Spreadsheets

Cap how much budget rolls over with MIN: a category example

Use MIN to stop a leisure envelope rolling past a ceiling and send the excess to a savings line, with formulas and a before-and-after table.

Editorial illustration for Cap how much budget rolls over with MIN: a category example

To cap a rollover in a spreadsheet, wrap the carry-out in MIN: =MIN(balance, cap). Whatever is left under the cap rolls into next month, and the difference above it goes to a separate savings line. That stops a leisure envelope growing so large that one trip eats a year of discipline.

What is the setup?

Say your leisure envelope gets ₹3,000 a month and you do not want more than ₹6,000 sitting in it, which is two months' worth. Put the cap in its own cell, H1, so you can change it later. Then lay out these columns, starting in row 2 for the first month:

  • A: month
  • B: monthly budget, ₹3,000
  • C: carry-in, which is 0 for the first month and the previous row's F afterwards
  • D: spent on leisure that month
  • E: balance before cap = C2+B2-D2
  • F: carry-out = MIN(E2, $H$1)
  • G: moved to savings = MAX(E2-$H$1, 0)

How do the two key formulas work?

The carry-out is simply:

=MIN(E2, $H$1)

MIN returns the smaller of the two numbers, so a balance of ₹4,200 stays ₹4,200, while a balance of ₹6,600 is trimmed to the cap of ₹6,000. The overflow is the mirror image:

=MAX(E2-$H$1, 0)

MAX(...,0) stops the savings column from going negative in months when the balance is below the cap. Add a running total of the savings line with =SUM($G$2:G2) in column H, filled down, if you want to see the pile growing.

What does it look like before and after the cap?

Here are four months of made-up spending. The balance is the carry-in plus ₹3,000 minus what was spent.

MonthSpentCarry-inBalanceCarry-out (cap ₹6,000)To savings
Month 11,00002,0002,0000
Month 28002,0004,2004,2000
Month 36004,2006,6006,000600
Month 45,5006,0003,5003,5000

Without a cap, month 4 would have started with ₹6,600 and ended at ₹4,100 (6,600 + 3,000 - 5,500). With the cap it ends at ₹3,500. The missing ₹600 is not lost: it sits in the savings line from month 3.

Read month 3 from left to right to see the logic. The carry-in of ₹4,200 plus the new ₹3,000 gives ₹7,200, and ₹600 of spending leaves ₹6,600. That is ₹600 above the cap, so MIN keeps ₹6,000 in the envelope and MAX pushes the other ₹600 across. In month 4 the envelope starts from the capped ₹6,000, so a big ₹5,500 outing still fits without going negative.

What if the balance is negative?

MIN does not mind negatives: MIN(-800, 6000) returns -800, so an overspend still carries forward. If you would rather forgive overspending, use =MAX(MIN(E2,$H$1),0) for the carry-out. Pick one rule and write it in a note beside the table so you remember it next year.

Can you apply the cap to several categories?

Yes. Give each category its own cap cell, or put the caps in a small table and look them up with a formula such as =MIN(E2, VLOOKUP($A2, $K$2:$L$10, 2, FALSE)) where column K lists category names and column L lists caps. This works the same way in Excel and Google Sheets. A modest cap on leisure and a higher one on groceries is more realistic than one number for everything.

Think about how to choose the cap itself. A cap of two months' budget lets you save up for one outing a quarter. A cap of one month's budget makes the envelope a pure monthly limit with a little cushion. Ask what the largest single purchase you would reasonably make from this envelope is, and set the cap just above it.

A quick test before you trust it

  1. Type a balance that is exactly equal to the cap and check nothing goes to savings.
  2. Type a balance one rupee above the cap and check that ₹1 appears in the savings column.
  3. Confirm that carry-out plus savings equals the balance in every row.
SPENDIXAI JOURNAL

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

Explore your reports