PFA income and expense tracker

What the sheet looks like

Setup, about 5 minutes

1. Copy the sheet with columns Date, Type, Category, Amount, and Note.
2. Type is income or expense. Amount is in lei, zero or more.
3. Paste the code and allow the spreadsheet.
4. Run PFA > Refresh 2026 estimate.
5. A Summary sheet appears with monthly totals and the annual estimate.

/**
 * 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);
}