Spreadsheets

Reset rollover balances at the start of the financial year

Restart rollover balances each April with an IF on the month number, keep earlier years as history, and get the April-to-March month index right.

Editorial illustration for Reset rollover balances at the start of the financial year

To reset rollover balances every April, test the month number in your carry-in formula and return 0 when the month is April. The useful trick is a financial-year month index, =MOD(MONTH(A2)-4,12)+1, which gives April 1 and March 12. Keep every month in one long table so earlier years stay on the sheet as history.

How do you number months April to March?

Excel's MONTH function returns 1 for January, so you need to shift it. With a real date in A2, use:

=MOD(MONTH(A2)-4,12)+1

Test it by hand: April gives MOD(0,12)+1 = 1, February gives MOD(-2,12)+1 = 11, and March gives MOD(-1,12)+1 = 12. MOD with a negative number returns a positive remainder in both Excel and Google Sheets, which is why the formula works across the year boundary.

What is the reset formula?

Suppose column A holds months as dates, B the budget, C the carry-in, D available, E spent and F left, and column G holds the month index above. In C2, and filled down:

=IF(MOD(MONTH(A2)-4,12)=0, 0, N(F1))

In an April row the carry-in is forced to 0. In every other row it takes the previous row's left figure. N(F1) turns the header text in the very first row into 0, so you can use the same formula from row 2 without an error.

Worked example around March and April

Say groceries have a ₹5,000 budget. These figures are made up, and the sheet restarts in April.

MonthIndexCarry-inAvailableSpentLeft
February114005,4004,700700
March127005,7004,5001,200
April105,0005,300-300
May2-3004,7004,100600

March ends with ₹1,200 unspent, but April starts at ₹5,000 rather than ₹6,200, because the reset removed the old carry. April ends ₹300 over, and May carries that forward normally.

Where does last year's ₹1,200 go?

Nowhere: it stays in the March row, because you never delete rows. To see the year's story, add a label column H with:

=YEAR(A2)-(MONTH(A2)<4)&"-"&RIGHT(YEAR(A2)-(MONTH(A2)<4)+1,2)

A February 2027 date returns 2026-27 and an April 2026 date returns 2026-27 too. Then build a small history table, one row per label in column J:

  • Budget for the year: =SUMIFS($B$2:$B$200, $H$2:$H$200, J2)
  • Spent for the year: =SUMIFS($E$2:$E$200, $H$2:$H$200, J2)
  • Closing balance: =SUMIFS($F$2:$F$200, $H$2:$H$200, J2, $G$2:$G$200, 12)

The last one picks the left figure from the month with index 12, which is March. Put a simple chart of spent against budget beside this table and the pattern of each year becomes visible at a glance, including the months that always overshoot.

What if the budget changes in the middle of the year?

Type the new amount in column B from the month it applies, and leave the earlier rows alone. Because every month keeps its own budget cell, the history table still adds up correctly, and the carry-in logic does not care what B held last month. Avoid pointing every row at a single budget cell, since changing it would quietly rewrite the past.

Should every category reset?

Not necessarily. An annual-premium sinking line or a travel fund may be meant to survive April. Add a Reset column on your category list with Yes or No and change the test to =IF(AND(MOD(MONTH(A2)-4,12)=0, $K2="Yes"), 0, N(F1)), where K holds that category's setting. Quite a few households find it enough to reset only the everyday categories.

Year-start checklist

  1. Add the new April row and confirm its index is 1.
  2. Confirm its carry-in shows 0, or the intended opening balance.
  3. Update base budgets if your costs changed.
  4. Check that the history table now shows the old year as a separate line.
SPENDIXAI JOURNAL

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

Explore your reports