The low point, not the total: what my expenses spreadsheet kept getting wrong

Every month for years I opened the same spreadsheet to answer one question: how much stock do I have to sell to cover the bills? Sell around the 27th, wait for settlement, pay the pile that clears between the 1st and the 3rd, repeat. The sheet did the arithmetic — everything due, minus the cash, minus the pay coming in — and gave me a number.

The number was wrong, in a specific and interesting way. Not the arithmetic. The timing.

A paycheck on the 31st does not pay a mortgage on the 3rd

Totalling a month treats it as a bucket: pour in the income, pour out the bills, look at what's left. But money is not a bucket, it is a sequence. If the big bills clear on the 1st and the paycheck lands on the 5th, the totals can come out fine while the account still goes negative for four days — and an account that goes negative is not a rounding error, it is a bounced mortgage payment.

So the tool I replaced the spreadsheet with does not total anything. It walks the window day by day, applying every bill and every paycheck in date order, and watches the running balance. The answer is the deepest point that balance reaches, plus a small buffer. Not the ending balance. The worst moment.

On my own numbers the two answers differ by enough to matter. The totals-only figure is always the optimistic one, and it is optimistic exactly when the timing is worst.

Sources propose, the model decides

The other half of the problem is that a spreadsheet is only true on the day you retype it. Bills change, statements arrive, a card gets paid off. So this thing keeps itself current from two places I already have: my budget app and my email.

The rule I set, and would set again, is that neither source ever changes a number on its own. Bills, income and cash are the model. YNAB and Gmail are sources: they read, they propose into a review queue, and I click Update, Add or Dismiss. Everything the projection reads is something I approved. There is exactly one write in the whole system — applying a statement amount back onto the matching scheduled transaction in the budget — and it happens after a click too.

That separation is what makes the tool trustworthy rather than merely automatic. An integration that silently edits your numbers gives you a dashboard you have to audit; one that only ever suggests gives you a dashboard you can read.

Three details that were only obvious afterwards

A credit card's current balance is the wrong number. It is what has been charged so far, not what the statement will ask for — counting it overstates the sale by everything spent since the statement closed. The bill has to come from the statement, or from the payment recorded against it. This one silently inflated my answer for a month before I caught it.

A stale due date means "roll me", not "pay me again". Everything here is on autopay, so a due date in the past is almost never an unpaid bill — it is a bill that cleared and whose date nobody stepped forward. Treating past-due as still-owed quietly double-counts it into the sale. Now a cleared payment offers to roll the date to the next occurrence instead.

Settlement is part of the deadline. The tool does not answer "how much" without also answering "by when": it backs the first shortfall up by the settlement days, so the question becomes sell this much, by this date, which is the form you can actually act on.

The engine is boring on purpose

The forecast module is a pure function — no clock, no network, no I/O — so every number the page shows is covered by ordinary unit tests, including a parity case asserting it reproduces the old spreadsheet's answer over the spreadsheet's own window. Being able to prove the new thing agrees with the old thing, before trusting it, was worth more than any feature.

This one stays private, unlike most of what I write about here: it holds real balances and a real pay schedule, so it lives behind an admin login and there is no public link. But the shape of it generalises to any question of the form "will this account survive the next N days" — and the answer is always the low point, never the total.