Skip to main content
Hashnode
TanLabs

I Built 4 Budget Spreadsheets With Python (openpyxl): The Formula Logic Behind Each One

Updated 8 min readMarkdown
T
TanLabs

Most budget templates are hand-built in Excel, so when you find a bug you fix it in one file and forget the other three. I generated mine from a single Python script with openpyxl instead. Every formula is a Python f-string, so the logic lives in one place, sits in version control and gets rebuilt in a second.

This post covers the formula logic of the four sheets: an irregular-income budget for freelancers, a snowball-vs-avalanche debt planner, a bookkeeping sheet whose P&L fills itself in, and a printable binder. You can copy the formulas into your own sheets. If you'd rather not, the finished files are linked at the end.

Status, so nobody is misled: the templates are on Gumroad. I have no sales numbers worth sharing yet, so this post is about the build, not revenue. AI tools helped me draft and double-check some formulas. I tested every sheet by hand in Excel and Google Sheets.

Before choosing a template, decide whether you need bank sync or a repeatable workbook you own. Tiller is the strongest spreadsheet-first option when automatic daily bank feeds matter; YNAB is the stronger app when zero-based planning and one-tap assigning are the priority; Vertex42 is the simplest free, manual template for a general household budget. This toolkit sits between those three: no bank connection, no subscription, and more specialised sheets for irregular freelance income, debt payoff, bookkeeping P&L and print.

Option Best at Trade-off Why this toolkit can be a better fit
Tiller Daily bank feeds into Sheets/Excel Subscription and account connection Manual, local workbook with no bank credentials; one-time purchase
YNAB Zero-based planning and guided workflow App-first workflow and recurring subscription Keeps Excel/Google Sheets formulas visible and editable
Vertex42 Free general-purpose templates Less specialised for freelancers and debt comparisons Includes irregular-income buffer, snowball/avalanche and P&L in one bundle

That positioning also sets the right expectation: this is a transparent workbook for people who want to inspect and change formulas, not an account-syncing app.

Five-minute smoke test

  1. Open the free 50/30/20 sheet and replace the three sample income/expense values.
  2. Open the debt planner and change one balance and APR; confirm the priority and payoff month change.
  3. Open the browser calculators first if you want to test the method without downloading anything.
  4. Only then decide whether the four-sheet bundle saves enough setup time for your workflow.

Why generate spreadsheets with code?

  • One source of truth. Column letters, ranges and number formats come from helper functions, so a renamed sheet can't silently break a formula three tabs away.
  • Big pre-built ranges. The income log has 500 formula rows and the debt schedule has 360 months. Nobody wants to drag-fill those by hand, and nobody wants to check that someone did.
  • Formulas, not values. openpyxl writes formulas as strings and Excel or Google Sheets calculates them on open. Buyers get a live sheet, not a static report.

A cell is just this:

ws.cell(row=r, column=4, value=f'=IF(C{r}="","",C{r}*Setup!$B$5)').number_format = MONEY

1. Irregular income: pay yourself a fixed salary out of a buffer

Freelance income arrives in lumps. The fix is to stop treating each payment as spending money:

  1. Every payment goes into a buffer (a separate account).
  2. A fixed share of each payment is set aside for tax (25% by default, editable).
  3. Once a month you pay yourself a baseline salary equal to your must-pay costs.
  4. The buffer carries you through the lean months. Runway = buffer ÷ salary.

On the Setup tab you list your must-pay costs (rent, utilities, insurance, debt minimums, savings goal…). The salary is simply their sum:

Baseline salary  = SUM(Setup!B9:B24)

Each row in the Income Log splits a payment into tax and net:

Tax set-aside = IF(C4="", "", C4 * Setup!$B$5)
Net to buffer = IF(C4="", "", C4 - D4)

The Dashboard rolls the logs up by month with SUMIFS on a date window and carries the buffer forward:

Gross income (month) = SUMIFS('Income Log'!$C$4:$C$503,
                        'Income Log'!$A$4:$A$503, ">="&A5,
                        'Income Log'!$A$4:$A$503, "<"&EOMONTH(A5,0)+1)
Buffer balance       = previous buffer + net income - salary
Runway (months)      = IF(salary=0, "", buffer / salary)

Two conditional formats do most of the work: the buffer cell turns red when it drops below zero, and runway turns yellow under 3 months. The >= month-start and < next-month-start window also handles timestamps safely, so you never need a "month" helper column in the log. Just type the date.

2. Snowball vs avalanche in plain formulas (no macros)

The difference between the two methods is only the order in which debts get the extra money:

  • Snowball: smallest balance first (quick wins).
  • Avalanche: highest APR first (least interest).

Ranking without SORT(). SORT doesn't exist in older Excel, so the priority column uses COUNTIFS. A debt's rank is the number of debts that beat it, plus a tie-breaker:

Snowball  rank = COUNTIFS($B$10:$B$17,"<"&B10, $B$10:$B$17,">0") + COUNTIF($B$10:B10, B10)
Avalanche rank = COUNTIFS($C$10:$C$17,">"&C10, $B$10:$B$17,">0") + COUNTIFS($C$10:C10, C10, $B$10:B10, ">0")
Priority       = IF(N(B10)<=0, 99, IF($B$5="Avalanche", <avalanche>, <snowball>))

The running COUNTIF($B$10:B10, B10) makes two equal balances get different ranks, so no money is ever split twice.

The monthly schedule with the rollover. Each month and each debt:

owed      = last balance * (1 + APR/12)
min part  = MIN(owed, minimum payment)
left      = total monthly budget - SUM(all min parts)
ahead     = SUMPRODUCT((priority < my priority) * MAX(0, owed - min part))   ← what higher-priority debts still need
pay       = min part + MIN(owed - min part, MAX(0, left - ahead))
balance   = ROUND(MAX(0, owed - pay), 2)

The total budget stays fixed at extra + all original minimums. So when a debt hits zero, its minimum doesn't disappear. It becomes part of left and flows to the next debt in line. That is the snowball rollover, and it needs no VBA.

The Summary tab counts months with a balance above 1 cent to get the payoff date. It also flags any debt whose minimum is at or below its monthly interest (min <= balance * APR / 12), because that debt does not decline and people rarely notice. Flip the method dropdown to compare total interest for both orders on your own numbers.

3. A P&L that builds itself with SUMIFS

For the bookkeeping sheet, the rule was "one row per transaction, nothing else". Each row is a date, a description, a type (Income/Expense) and a category from a dropdown (a DataValidation list fed by the Setup tab), plus an amount.

The P&L is a grid of categories × 12 months, and each cell is a single four-condition SUMIFS:

=IF($A6="", "", SUMIFS(Transactions!$E$4:$E$2003,
      Transactions!$C$4:$C$2003, "Income",          ← type
      Transactions!$D$4:$D$2003, $A6,               ← category
      Transactions!$A$4:$A$2003, ">="&B$4,          ← month start
      Transactions!$A$4:$A$2003, "<"&EOMONTH(B$4,0)+1))

Then come totals, net profit and margin (IF(income=0,"",profit/income)), and a simple tax set-aside tab: MAX(0, monthly profit) * rate, with a running total so you can see what you should have put aside so far. It's labelled as an estimate, not tax advice.

The details that took longest:

  • $A6 / B$4 mixed references, so one formula string fills the whole grid.
  • The category list on the P&L is itself a formula (=IF(Setup!A9="","",Setup!A9)). Rename a category once and it updates everywhere.

4. Printables: HTML + CSS → PDF

The undated budget binder (12 pages, A4 and US Letter) isn't a spreadsheet at all. A small Python script builds an HTML document: a cover, a monthly budget, a bill tracker, savings and debt trackers, all as CSS-grid tables. Then it prints that to PDF once per paper size. CSS is much nicer than a PDF library for "a grid of boxes with rounded corners", and changing the paper size is a single argument.

Lessons I'd keep

  1. Test in Google Sheets as well as Excel. Both handle SUMIFS, EOMONTH and COUNTIFS the same way. Newer functions like SORT/FILTER are where they differ, which is why I avoided them.
  2. Put example data in every log. An empty dashboard full of zeros looks broken. A realistic year of sample payments shows how it works in five seconds.
  3. Shade the input cells. The buyer should know exactly which cells are theirs to type in, and that everything else is formulas.
  4. Write the edge-case warnings into the sheet ("minimum below interest", "runway under 3 months") instead of the README nobody reads.

Get the files

If you just want to use them:

If you'd rather not download anything, the same maths runs in the browser on calc.funmagi.com: debt snowball, 50/30/20 budget and freelance rate calculators, free and with no sign-up.

Questions about any of the formulas are welcome in the comments. Not financial advice: these are templates, and you enter your own numbers.

Comments

Join the discussion

No comments yet. Be the first to comment.

More from this blog