Spreadsheets

Project month-end spending from what you have spent so far

Project your month-end spending in Excel from what you have spent so far, compare it with the budget and flag a likely overspend, plus where the method fails.

Editorial illustration for Project month-end spending from what you have spent so far

To project month-end spending, divide what you have spent so far by the day of the month and multiply by the days in the month. In Excel that is =B2/DAY(TODAY())*DAY(EOMONTH(TODAY(),0)). Set it next to your budget and an IF formula can warn you while there is still time to slow down. It is a pace estimate, not a prediction, and it misleads when big bills land late.

The pace formula, step by step

Put the spend so far this month in B2. If your expense log has dates in column A and amounts in column C, rows 2 to 500, this gives it:

=SUMIFS($C$2:$C$500,$A$2:$A$500,">="&(EOMONTH(TODAY(),-1)+1),$A$2:$A$500,"<="&TODAY())

Now the projection, in B3:

  • DAY(TODAY()) is how many days of the month have passed, including today.
  • DAY(EOMONTH(TODAY(),0)) is the number of days in this month, because the last day's number equals the month's length.
  • Projection: =B2/DAY(TODAY())*DAY(EOMONTH(TODAY(),0))

Put your total monthly budget in B1 and a warning in B4:

=IF(B3>B1,"Likely over by ₹"&TEXT(B3-B1,"#,##0"),"On pace, ₹"&TEXT(B1-B3,"#,##0")&" to spare")

What does it look like with real arithmetic?

Hypothetical numbers: a 30-day month, a total budget of ₹32,000, and ₹14,400 spent by the 12th.

StepWorkingResult
Average per day so far14,400 / 12₹1,200
Projected month1,200 x 30₹36,000
Against budget36,000 - 32,000₹4,000 over

The sheet would say Likely over by ₹4,000. Before you panic, look at what the 14,400 contains.

Why can a straight-line projection be wrong?

Because spending is lumpy. Rent, society maintenance, school fees and EMIs usually leave the account on a particular date, often early in the month. A straight-line pace treats them as daily habits and multiplies them by 30. Equally, a bill due on the 28th is invisible on the 12th, so the pace can look comfortable and then jump.

The fix is to split the month into two buckets. Fixed bills have a known amount and date; variable spending (groceries, fuel, eating out) is what pace is good for. Say the 14,400 includes ₹9,000 of rent paid on the 1st, and you know an electricity bill of about ₹2,000 comes later.

PieceWorkingAmount
Variable so far14,400 - 9,000₹5,400
Variable pace for the month5,400 / 12 x 30₹13,500
Fixed bills9,000 rent + 2,000 electricity₹11,000
Better projection13,500 + 11,000₹24,500

Same month, same data: ₹24,500 instead of ₹36,000. The earlier alarm was false because the rent was multiplied up. To build this, tag each log row as Fixed or Variable in a fourth column D, then use SUMIFS with a "Variable" criterion for the pace and add the known fixed amounts separately.

Should I use the projection to decide anything?

Use it as a nudge, not a verdict. If it says you may overspend, look at which category is driving it. A better version of the warning shows the projection per category by repeating the formulas with a category criterion, so you see Dining out heading for double its budget rather than a vague overall alarm.

Can I show this as a simple traffic light?

Yes. Compare the projection with the budget as a ratio in a spare cell, =B3/B1, and apply a conditional format: green below 90 percent, amber between 90 and 100, red above 100. In the earlier example the naive projection is 36,000 divided by 32,000, which is 112.5 percent and shows red, while the split method gives 24,500 divided by 32,000, about 77 percent, and shows green. The colour is only as good as the projection behind it.

Quick checks

  1. Confirm the log's dates are real dates, or SUMIFS returns less than you spent.
  2. Make sure the spend-so-far formula stops at TODAY(), not at the end of the month.
  3. Separate rent and other fixed bills before applying the pace.
  4. Revisit the number after every large payment.

If keeping the log current is what stops this working, SpendixAI can import your statements, so recent card and bank spending is already categorised for review before you project anything.

SPENDIXAI JOURNAL

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

Explore your reports