Spreadsheets

Roll unspent budget into next month: the formula

Roll unspent budget into next month with a simple formula: opening balance, budget added, spend subtracted, plus a three-month groceries worked example.

Editorial illustration for Roll unspent budget into next month: the formula

To roll unspent budget into next month, give each envelope an opening balance, add the month's budget, subtract what you spent, and carry the closing balance forward as next month's opening balance. In a sheet that is one line: Closing = Opening + Budget - Spent. The only other decision is what to do when you overspend.

How do I lay out the columns?

  • A: first day of the month, for example 1-Apr-2026.
  • B: opening balance. The first month is typed by hand, usually 0; every later month is =E2 from the row above.
  • C: budget added for the month, such as 10,000 for groceries.
  • D: spent in the month, pulled from your transactions.
  • E: closing balance, =B2+C2-D2.

For spent, with your transactions on a sheet called Data, dates in A, categories in B and amounts in C, use:

  • =SUMIFS(Data!$C$2:$C$800,Data!$B$2:$B$800,"Groceries",Data!$A$2:$A$800,">="&$A2,Data!$A$2:$A$800,"<"&EDATE($A2,1))

Row 3's opening balance is =E2, and so on down the page.

What does a three-month example look like?

Say groceries get ₹10,000 a month, starting in April with nothing carried in:

MonthOpening (₹)Budget (₹)Spent (₹)Closing (₹)
Apr 2026010,0008,5001,500
May 20261,50010,00011,200300
Jun 202630010,0009,0001,300

Check May: 1,500 plus 10,000 is 11,500, minus 11,200 leaves 300. In May you spent ₹1,200 more than the month's budget and still finished positive, because April's leftover absorbed it. By the end of June you have ₹1,300 of room that did not exist in the plain monthly view.

What happens if I overspend?

If you spend more than opening plus budget, the closing balance goes negative. You choose how to treat that:

  • Let it carry. The deficit is subtracted from next month's room, which keeps the books honest. The formula is the one above.
  • Reset to zero. =MAX(0,B2+C2-D2) forgives an overspend, which is gentler and also hides it.
  • Cap the carry. =MIN(C2,B2+C2-D2) stops the balance growing beyond one month's budget, so a long quiet stretch does not build a huge pile.

Should every category roll over?

No. Rollover suits categories that are lumpy but predictable, like gifts, clothes, medical, travel or home repairs. Groceries and fuel often settle to about the same each month, so a rolling balance can invite you to spend the surplus. Many people roll over only a few categories, and let others reset every month.

How do I build several envelopes?

  1. Copy the block for each category and change the word in the SUMIFS.
  2. Or put the category in column F of each row and refer to it, replacing "Groceries" with $F2, so one formula fits every block.
  3. Add a summary at the top that adds up the closing balances.

What can break it?

  • A month skipped in column A. Keep one row per month, in order.
  • Opening balance typed over a formula by mistake. Colour that column so it is obvious.
  • Budgets changed halfway without a note. Change the budget in the next row only.
  • Spending categories renamed. A new name returns zero until the SUMIFS text matches.

If you work to the April to March year, decide what happens each April. Either reset the opening balance of the April row to zero, or carry the March closing balance across on purpose and note that you did. Carrying a large balance for many months without looking at it often means the monthly budget was set too high.

The hardest part is getting each transaction into the right category on time. SpendixAI can categorise imported statements with your own rules first, so your envelope formulas have clean categories to read.

SPENDIXAI JOURNAL

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

Explore your reports