Spreadsheets

Zero-based budget in Excel: formulas that check you reach zero

Set up a zero-based budget in Excel with an income cell, category allocations and a Left to allocate formula that turns green at zero and red otherwise.

Editorial illustration for Zero-based budget in Excel: formulas that check you reach zero

One cell tells you whether a zero-based budget is finished: Left to allocate, =B1-SUM(B4:B12). Put income in B1 and list allocations in a column; conditional formatting turns the cell green at exactly zero and red otherwise. Every rupee of income has a job when income minus allocations equals zero.

What does the sheet look like?

Use column A for names and column B for amounts:

  • B1: Income for the month, your take-home pay and any other money you expect, typed as a number.
  • B2: Left to allocate, the formula.
  • A4 to A12: one line per job for money, with the planned amount in column B.

In B2 write:

=ROUND(B1-SUM(B4:B12),2)

The ROUND to two decimals is not decoration. Spreadsheets store numbers in binary, so an amount that should be exactly 0 can come out as a tiny leftover such as 0.0000000001, which would show red even though your plan balances. Rounding removes that.

How do I make it green at zero and red otherwise?

  1. Select B2.
  2. Go to Home, then Conditional Formatting, then New Rule, then Use a formula.
  3. Enter =$B$2=0 and choose a green fill; press OK.
  4. Add a second rule with =$B$2<>0 and choose a red fill.

Add a plain-language message in C2 so the colour is not the only signal:

=IF(B2=0,"Every rupee has a job",IF(B2>0,"₹"&TEXT(B2,"#,##0")&" still to allocate","Over-allocated by ₹"&TEXT(-B2,"#,##0")))

A worked example with hypothetical numbers

LinePlanned amount
Income₹60,000
Rent₹18,000
Groceries₹9,000
Bills and utilities₹4,500
Transport₹3,500
Loan EMI₹6,000
Savings transfer₹8,000
Family₹3,000
Fun₹4,000
Left to allocate₹4,000

Add the allocations: 18,000 + 9,000 = 27,000; plus 4,500 = 31,500; plus 3,500 = 35,000; plus 6,000 = 41,000; plus 8,000 = 49,000; plus 3,000 = 52,000; plus 4,000 = 56,000. Income of 60,000 minus 56,000 leaves ₹4,000 in red. Add a line called Buffer for 4,000 and the cell turns green and says every rupee has a job. Add a ₹500 coffee habit instead and it will say ₹3,500 still to allocate.

Add a column C of actuals with SUMIFS from your expense log, using the month in a cell, and a column D of =B4-C4. A positive D means money left in that line; a negative means you spent more than planned. Total each column. Then ask the question zero-based budgeting really cares about: did the total actual equal income, or did some money go unrecorded? If actuals add up to less than income, the gap is either unspent cash or spending you have not logged.

What about irregular income?

Budget from the amount you are confident will arrive, and add extra money to the plan only when it lands. People with variable income often budget a month in arrears, from last month's money, so this month's plan starts with cash already in hand. That is a habit, not a formula; the sheet does not change.

How do I use it each month?

Start the month by copying last month's sheet, clearing the actual column and updating income. Move money between lines until Left to allocate is zero. During the month, glance at the actual column; if one line is running ahead, take money from a line that has room, not from nowhere, and keep the total allocated equal to income. At month end, note one thing to change next month. The sheet is a conversation with your own money, so a small change each month is plenty.

Before you rely on it

  • Allocations are numbers, not text.
  • The SUM range covers every line, including ones you add later.
  • Left to allocate is rounded.
  • You update the plan when income changes.

SpendixAI is not a zero-based budgeting system; it has budgets, bills and savings goals on Pro, and imports statements so your Actual column needs less typing. The sheet above remains the place to plan every rupee.

SPENDIXAI JOURNAL

Useful ideas start with a clearer picture of your own spending.

Explore your reports