AI disclosure: This article was written with AI assistance (Claude), and the generator script was written with AI assistance too. The snippets come from the real build script, and the numbers in the output section came from running the verification script below.
Most budget spreadsheets break the same way. Someone copies a formula down one row too few, or a new category doesn't get added to the totals, or "this month" is a hard-coded column that nobody moves. After a few weeks, people stop trusting the numbers and stop using it.
I wanted a budget workbook where:
- you only ever append rows (transactions) and edit one cell (the month),
- everything else (totals, budget vs actual, the 50/30/20 split, a year view) recalculates itself,
- it uses only standard functions that Excel and Google Sheets both support, with no macros or scripts,
- and the formulas are tested like code, not checked by squinting.
The approach: generate the .xlsx from a TypeScript script with ExcelJS, and verify the formulas with HyperFormula, a headless spreadsheet engine that runs in Node or Bun. Here is how it fits together.
Why generate a spreadsheet from code?
Building a 7-tab workbook by hand is fine once. Changing it is painful: add a category and you have to fix ranges on three sheets. When the workbook is the output of a script, the ranges are variables. Add a row to the categories array and every SUMIFS, every dropdown and every dashboard row follows.
bun add exceljs hyperformula The data model: three tables
Everything hangs off three ranges:
- Categories: name, type (
Income,Need,Want,Savings) and a monthly budget. - Transactions: date, description, category (dropdown), amount, plus two helper columns computed per row.
- Dashboard: a month selector and formulas that read the other two.
Categories are a plain array, and the script derives the range from its length:
const cats: [string, string, number][] = [ ["Salary", "Income", 0], ["Side income", "Income", 0], ["Other income", "Income", 0], ["Rent / mortgage", "Need", 0], ["Utilities", "Need", 0], ["Groceries", "Need", 0], ["Transport", "Need", 0], // ... ["Eating out", "Want", 0], ["Subscriptions", "Want", 0], ["Shopping", "Want", 0], ["Emergency fund", "Savings", 0], ["Savings goals", "Savings", 0], ["Extra debt payment", "Savings", 0], ["", "", 0], ["", "", 0], ["", "", 0], ["", "", 0], ["", "", 0], // spare rows for the user ]; const C0 = 5, CN = C0 + cats.length - 1; const CATRANGE = `Categories!$A$${C0}:$A$${CN}`; The five empty rows matter. Users will want to add a category, and if the formulas already cover those rows, adding one is just typing a name. No range to fix.
The type column gets a list validation so it can't drift into "need" vs "Needs" vs "NEED":
r.getCell(2).dataValidation = { type: "list", allowBlank: true, formulae: ['"Income,Need,Want,Savings"'] }; Helper columns do the heavy lifting
The trick that keeps the dashboard simple is putting two computed columns on every transaction row: the category's type and the month number.
const T0 = 5, TN = 1004; // 1,000 pre-built rows for (let r = T0; r <= TN; r++) { const row = st.getRow(r); row.getCell(3).dataValidation = { type: "list", allowBlank: true, formulae: [CATRANGE] }; row.getCell(5).value = { formula: `IF(C${r}="","",IFERROR(VLOOKUP(C${r},Categories!$A$${C0}:$B$${CN},2,0),"?"))` } as any; row.getCell(6).value = { formula: `IF(A${r}="","",MONTH(A${r}))` } as any; } Three details:
- The category dropdown points at the Categories range, so renaming "Eating out" to "Restaurants" on the Categories tab changes the dropdown too.
- The
IF(C="","",…)wrapper keeps empty rows truly empty. Without it, 1,000 rows of#N/Awould make the sheet look broken before anyone types anything. - An unknown category shows
?instead of an error, which is much easier for a non-technical user to understand.
Note the 0 as the last VLOOKUP argument. My first version used FALSE, which Excel and Sheets accept for an exact match. The test engine I used read the boolean differently, and the lookup silently did the wrong thing. 0 means "exact match" in every engine I tried, so that's what the script writes now. This is the kind of bug the tests below caught.
One cell drives the dashboard
The dashboard has a single input, the month number in B4, restricted to 1–12:
const mc = sd.getCell("B4"); mc.value = 1; mc.dataValidation = { type: "whole", operator: "between", formulae: [1, 12], allowBlank: false }; sd.getCell("C4").value = { formula: `CHOOSE(B4,"January","February","March","April","May","June","July","August","September","October","November","December")` } as any; (CHOOSE replaced an earlier INDEX over an inline array, again because one engine disagreed with the others. CHOOSE is boring and works everywhere.)
Because every transaction row already knows its type and month, the totals are one SUMIFS each. A small helper builds them:
const TX = (col: string) => `Transactions!$${col}$${T0}:$${col}$${TN}`; const sumType = (t: string, m = "$B$4") => `SUMIFS(${TX("D")},${TX("E")},"${t}",${TX("F")},${m})`; const kp: [string, string][] = [ ["Income", sumType("Income")], ["Needs", sumType("Need")], ["Wants", sumType("Want")], ["Savings", sumType("Savings")], ["Left to assign", `B6-B7-B8-B9`], ]; Next to each total, a percentage of income sits beside a 50 / 30 / 20 guide, guarded against dividing by zero in a month with no income yet:
r.getCell(3).value = { formula: `IF($B$6=0,"",B${6 + i}/$B$6)` } as any; Below that, one row per category shows budget, actual and what's left, using the same pattern with the category name instead of the type:
row.getCell(4).value = { formula: `IF(A${r}="","",SUMIFS(${TX("D")},${TX("C")},A${r},${TX("F")},$B$4))` } as any; row.getCell(5).value = { formula: `IF(OR(A${r}="",B${r}="Income"),"",C${r}-D${r})` } as any; Conditional formatting turns "Left" red when it goes negative, and "Left to assign" red when you've planned more than you earn. ExcelJS writes these as real conditional formats in the file, not as code.
The year view is the same formula, twelve times
The "year at a glance" table to the right reuses sumType, passing a literal month instead of $B$4:
MONTHS.forEach((m, i) => { const r = 6 + i, mi = String(i + 1); row.getCell(8).value = { formula: sumType("Income", mi) } as any; row.getCell(9).value = { formula: `${sumType("Need", mi)}+${sumType("Want", mi)}` } as any; row.getCell(10).value = { formula: sumType("Savings", mi) } as any; row.getCell(11).value = { formula: `H${r}-I${r}-J${r}` } as any; }); That's the "self-updating" part. There is no "close the month" step and nothing to copy forward. A transaction dated in March shows up in March because its helper column says 3.
A few extra tabs, same idea
Once the pattern is in place, extra tabs are cheap:
Savings goals. Progress is capped at 100%, and "per month to hit it" counts down from TODAY():
`IF(OR(D${r}="",F${r}=""),"",IF(D${r}<=TODAY(),F${r},F${r}/MAX(1,ROUNDUP((D${r}-TODAY())/30.4,0))))` Debt payoff. Rank by smallest balance (snowball order) with COUNTIFS, and estimate months to pay off with the standard amortisation formula. It shows a friendly message when the payment doesn't even cover the interest:
`IF(OR(B${r}="",E${r}="",E${r}=0),"",IF(C${r}=0,ROUNDUP(B${r}/E${r},0),IF(E${r}<=B${r}*C${r}/1200,"Payment too low",ROUNDUP(-LN(1-(C${r}/1200)*B${r}/E${r})/LN(1+C${r}/1200),0))))` A 48-hour "impulse pause" list. Write down a thing you want, and a "decide on" date appears two days later. A total at the bottom adds up everything you decided to skip.
Testing the formulas with HyperFormula
This is the part I'd recommend even if you build your spreadsheets by hand. HyperFormula can load a workbook as plain arrays of values and =formulas, and compute it without Excel.
The verification script reads the generated .xlsx back with ExcelJS, converts each cell (formulas get a leading =, dates become Excel serial numbers) and builds a HyperFormula instance:
import ExcelJS from "exceljs"; import { HyperFormula } from "hyperformula"; const wb = new ExcelJS.Workbook(); await wb.xlsx.readFile("2027_Simple_Budget_Planner.xlsx"); const sheets: Record<string, any[][]> = {}; wb.eachSheet(ws => { const rows: any[][] = []; ws.eachRow({ includeEmpty: true }, (r, i) => { const a: any[] = []; r.eachCell({ includeEmpty: true }, (c, j) => { const v: any = c.value; a[j - 1] = v && typeof v === "object" && "formula" in v ? "=" + v.formula : v instanceof Date ? (v.getTime() / 86400000 + 25569) : v; }); rows[i - 1] = a; }); sheets[ws.name] = rows.map(r => r ?? []); }); const hf = HyperFormula.buildFromSheets(sheets, { licenseKey: "gpl-v3" }); const g = (s: string, a: string) => hf.getCellValue(hf.simpleCellAddressFromString(a, hf.getSheetId(s)!)!); 25569 is the number of days between Excel's epoch (1899-12-30) and the Unix epoch, so dates line up with what MONTH() expects.
The workbook ships with four example transactions (a 2,500 paycheck, 900 rent, 85.40 groceries and a 6.50 coffee in January), so you can assert on known numbers:
console.log("Dash C4", g("Dashboard","C4"), "Income", g("Dashboard","B6"), "Needs", g("Dashboard","B7"), "Wants", g("Dashboard","B8"), "Left", g("Dashboard","B10")); Dash C4 January Income 2500 Needs 985.4 Wants 6.5 Left 1508.1 %needs 0.39416 16 Rent / mortgage Need 900 900 0 18 Groceries Need 350 85.4 264.6 Jan row 2500 991.9 1508.1 total 1508.1 Tx E5/F5 Income 1 blank "" You can also write test data and check derived cells. That's how the debt tab was checked:
const sb = hf.getSheetId("Debt Payoff")!; hf.setCellContents({ sheet: sb, row: 4, col: 0 }, [ ["Card", 2000, 20, 50, 100], ["Loan", 500, 0, 20, 50], ]); console.log("Debt", g("Debt Payoff","F5"), g("Debt Payoff","G5"), g("Debt Payoff","F6"), g("Debt Payoff","G6")); // Debt 2 25 1 10 -> the loan is paid first; the card takes 25 months at 100/month and 20% APR This caught three real bugs before anyone opened the file: the FALSE vs 0 lookup, the month label, and a conditional format whose text match didn't fit the actual dropdown value. Turning those console.logs into expect() calls in bun test gives you a regression suite for a spreadsheet.
One honest caveat: HyperFormula is not Excel. It covers the functions used here, but it says nothing about dropdowns, conditional formats or frozen header rows. Formula logic is what the tests cover; presentation still needs a manual check in Excel and Google Sheets before you rely on it.
Things that make it pleasant to use
Some small choices that aren't about formulas:
- Colour-code input cells. Every cell a user should type in has the same light fill. Everything else is computed. The first tab says so in one sentence.
- Freeze header rows on the long tabs (
ws.views = [{ state: "frozen", ySplit: 4 }]). - Ship example rows labelled "(delete me)", so the dashboard isn't a wall of zeros on first open, but it's obvious what to remove.
- Positive amounts only. The category decides whether money is coming in or going out. Asking people to type negative numbers for expenses is a reliable source of sign errors.
Wrap-up
The pattern generalises to any tracker: put the classification into helper columns on the raw rows, drive the summary from one selector cell with SUMIFS, generate the workbook from code so ranges are variables, and test the formulas with a headless engine.
If you'd rather start from something ready-made in Notion than build it, I made a free Notion weekly planner template that you can duplicate: https://agebreak.gumroad.com/l/ecltde

