Venituri și cheltuieli PFA

Cum arată foaia

Instalare, cam 5 minute

1. Copiază foaia cu coloanele Date, Type, Category, Amount, Note.
2. Type este income sau expense. Amount este în lei, zero sau mai mult.
3. Lipește codul și autorizează foaia.
4. Rulează PFA > Refresh 2026 estimate.
5. Apare o foaie Summary cu totalurile lunare și estimarea anuală.

/**
 * Monthly totals plus a 2026 real-system PFA estimate.
 * Same caps as src/lib/tax/romania2026.ts: minimum wage on 1 Jan 2026 is 4050.
 * This does not apply the "also an employee" CASS exception.
 * Columns: Date, Type (income or expense), Category, Amount, Note.
 */
function onOpen() {
  SpreadsheetApp.getUi()
    .createMenu('PFA')
    .addItem('Refresh 2026 estimate', 'refreshPfaEstimate')
    .addToUi();
}

function roundMoney_(value) {
  if (!isFinite(value)) return 0;
  var negative = value < 0;
  var parts = Math.abs(value).toFixed(8).split('.');
  var digits = (parts[1] + '000').slice(0, 3);
  var cents = 100 * Number(parts[0]) + Number(digits.slice(0, 2));
  if (Number(digits[2]) >= 5) cents += 1;
  var rounded = cents / 100;
  return negative ? -rounded : rounded;
}

function refreshPfaEstimate() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var values = sheet.getDataRange().getValues();
  if (values.length < 2) throw new Error('Add at least one data row.');
  var headers = values[0].map(function (h) { return String(h).trim(); });
  var dateCol = headers.indexOf('Date');
  var typeCol = headers.indexOf('Type');
  var amountCol = headers.indexOf('Amount');
  if (dateCol < 0 || typeCol < 0 || amountCol < 0) throw new Error('Need columns Date, Type, and Amount.');
  // Published as the 2026 estimate. Caps use the 4,050 lei minimum from 1 January 2026.
  var year = 2026;
  var income = 0;
  var expense = 0;
  var months = {};
  for (var r = 1; r < values.length; r++) {
    var kind = String(values[r][typeCol] || '').trim().toLowerCase();
    var amount = Number(values[r][amountCol]);
    if ((kind !== 'income' && kind !== 'expense') || !isFinite(amount) || amount < 0) continue;
    var ymd = asYmd_(values[r][dateCol]);
    if (ymd.slice(0, 4) !== String(year)) continue;
    var month = ymd.slice(0, 7);
    if (!months[month]) months[month] = { income: 0, expense: 0 };
    months[month][kind] = roundMoney_(months[month][kind] + amount);
    if (kind === 'income') income = roundMoney_(income + amount);
    else expense = roundMoney_(expense + amount);
  }
  var estimate = estimateRealPfa_(income, expense);
  writeSummary_(SpreadsheetApp.getActive(), year, months, income, expense, estimate);
}

function estimateRealPfa_(income, expenses) {
  var net = roundMoney_(Math.max(0, income - expenses));
  var lower = 4050 * 12;
  var upper = 4050 * 24;
  var cas = 0;
  if (net >= lower) cas = roundMoney_((net < upper ? lower : upper) * 0.25);
  var floor = 4050 * 6;
  var cap = 4050 * 72;
  var cassBase = net <= 0 ? 0 : roundMoney_(Math.min(cap, Math.max(floor, net)));
  var cass = roundMoney_(cassBase * 0.1);
  var taxable = roundMoney_(Math.max(0, net - cas - cass));
  var tax = roundMoney_(taxable * 0.1);
  return { net: net, cas: cas, cass: cass, tax: tax, left: roundMoney_(net - cas - cass - tax) };
}

function writeSummary_(ss, year, months, income, expense, estimate) {
  var name = 'Summary ' + year;
  var summary = ss.getSheetByName(name) || ss.insertSheet(name);
  summary.clear();
  var rows = [['Month', 'Income', 'Expense']];
  Object.keys(months).sort().forEach(function (month) {
    rows.push([month, months[month].income, months[month].expense]);
  });
  rows.push(['Year income', income, '']);
  rows.push(['Year expenses', expense, '']);
  rows.push(['Net', estimate.net, '']);
  rows.push(['CAS', estimate.cas, '']);
  rows.push(['CASS', estimate.cass, '']);
  rows.push(['Income tax', estimate.tax, '']);
  rows.push(['Left after tax', estimate.left, '']);
  summary.getRange(1, 1, rows.length, 3).setValues(rows);
}

function asYmd_(value) {
  var tz = Session.getScriptTimeZone();
  if (Object.prototype.toString.call(value) === '[object Date]' && !isNaN(value.getTime())) {
    return Utilities.formatDate(value, tz, 'yyyy-MM-dd');
  }
  return String(value || '').trim().slice(0, 10);
}