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

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](https://openpyxl.readthedocs.io/) 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.

## Where this toolkit fits (compared with the popular options)

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:

```python
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:

```text
Baseline salary  = SUM(Setup!B9:B24)
```

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

```text
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:

```text
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:

```text
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:

```text
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`:

```text
=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:

- **Free:** [50/30/20 budget sheet (Google Sheets + Excel)](https://tanlabs.gumroad.com/l/free-budget-sheet?utm_source=hashnode&utm_medium=blog&utm_campaign=hn10_budget_sheets) and a [5-page large-print coloring sampler](https://tanlabs.gumroad.com/l/eypunu?utm_source=hashnode&utm_medium=blog&utm_campaign=hn10_budget_sheets) (yes, unrelated, but people keep asking for it for their parents).
- **Single templates:** [Freelancer irregular-income budget](https://tanlabs.gumroad.com/l/gvfjft?utm_source=hashnode&utm_medium=blog&utm_campaign=hn10_budget_sheets) ($12), [Debt snowball vs avalanche planner](https://tanlabs.gumroad.com/l/kubsdr?utm_source=hashnode&utm_medium=blog&utm_campaign=hn10_budget_sheets) ($9), [Bookkeeping with automatic P&L](https://tanlabs.gumroad.com/l/qrfhah?utm_source=hashnode&utm_medium=blog&utm_campaign=hn10_budget_sheets) ($15), [Undated budget binder PDF](https://tanlabs.gumroad.com/l/pgigm?utm_source=hashnode&utm_medium=blog&utm_campaign=hn10_budget_sheets) ($7).
- **All four:** [Budget & Bookkeeping Toolkit](https://tanlabs.gumroad.com/l/zlynlp?utm_source=hashnode&utm_medium=blog&utm_campaign=hn10_budget_sheets) for $29 instead of $43 bought separately.

If you'd rather not download anything, the same maths runs in the browser on [calc.funmagi.com](https://calc.funmagi.com/?utm_source=hashnode&utm_medium=blog&utm_campaign=hn10_budget_sheets): [debt snowball](https://calc.funmagi.com/debt-snowball-calculator?utm_source=hashnode&utm_medium=blog&utm_campaign=hn10_budget_sheets), [50/30/20 budget](https://calc.funmagi.com/50-30-20-budget-calculator?utm_source=hashnode&utm_medium=blog&utm_campaign=hn10_budget_sheets) and [freelance rate](https://calc.funmagi.com/freelance-rate-calculator?utm_source=hashnode&utm_medium=blog&utm_campaign=hn10_budget_sheets) 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.