Spreadsheets

Cumulative spend line vs the monthly budget line

Chart month-to-date spending against a straight budget line in Excel with SUMIFS, and see which day you will cross the limit at your current pace.

Editorial illustration for Cumulative spend line vs the monthly budget line

To see when you will run out of budget, chart two lines for the month: a running total of spending built with SUMIFS, and a straight line that climbs evenly to your budget by the last day. Where the spending line rises above the budget line you are ahead of pace, and dividing the budget by your daily average tells you the day you will cross it.

Lay out one row per day

Put the month's first date in F1 (say 1 November) and the monthly budget in F2 (say 30,000). In column A list every date of the month: A2 is =$F$1, and A3 is =A2+1, filled down to the last day. A 30-day month needs 30 rows.

  • B, daily spend: =SUMIFS(Data[Amount], Data[Date], A2)
  • C, cumulative spend: =IF(A2>TODAY(), NA(), SUMIFS(Data[Amount], Data[Date], ">="&$F$1, Data[Date], "<="&A2))
  • D, budget line: =$F$2*(A2-$F$1+1)/DAY(EOMONTH($F$1,0))

Returning #N/A for future dates in column C makes the spending line stop at today instead of dropping to zero. Column D adds one thirtieth of the budget each day, so it ends exactly at 30,000. If your dates carry a time, the comparison in column B can miss them; use the >= and < pair shown for column C instead.

Draw the chart

  1. Select columns A, C and D, headers included.
  2. Choose Insert, then Line, then Line (not stacked).
  3. Right-click the budget series, choose Format Data Series, and make it a dashed grey line.
  4. Leave the spending line solid and thick, with no markers.
  5. Set the vertical axis minimum to 0 so the gap looks honest.

On which day will I cross the limit?

Work out your pace so far and extend it. With the sheet above, F3 holds days elapsed: =MIN(TODAY()-$F$1+1, DAY(EOMONTH($F$1,0))). F4 holds spend so far: =SUMIFS(Data[Amount], Data[Date], ">="&$F$1, Data[Date], "<="&TODAY()). Then:

  • Projected month-end spend: =F4/F3*DAY(EOMONTH($F$1,0))
  • Day you reach the budget: =IF(F4=0, "", ROUNDUP($F$2/(F4/F3), 0))
ItemHypothetical valueHow it is worked out
Monthly budget₹30,000Typed in F2
Days elapsed12Today is the 12th
Spent so far₹14,400SUMIFS result
Daily pace₹1,20014,400 divided by 12
Projected month-end₹36,0001,200 times 30 days
Day budget is reachedDay 2530,000 divided by 1,200

In this made-up case the straight budget line allows ₹1,000 a day, so at ₹1,200 a day the spending line is already ₹2,400 above it on day 12 (14,400 against 12,000) and will touch the budget on the 25th.

Why does the line drop or spike?

  • Line drops to zero after today: future cells hold 0 rather than #N/A. Check the IF in column C.
  • Line is flat for days: dates in the Data Table are text, so SUMIFS finds nothing.
  • Cumulative total is too high: the Table includes credits or transfers between your own accounts.
  • Chart stops at the wrong day: your date list is shorter than the month.

To reuse the sheet next month, change only F1 to the new first date. The date list, daily totals and budget line all follow, and the DAY(EOMONTH()) part adjusts for months with 28, 30 or 31 days. Keep the budget in F2 as a figure for flexible spending, and update it when your plan changes.

What to do with the answer

A crossing date in the middle of the month is a prompt to look at the three or four biggest flexible items so far, not a verdict. Postponing a purchase or planning a cheaper week is your call. If keeping a daily sheet is the part you drop first, SpendixAI can show spending against budgets on its Pro plan from statements you upload and cash entries you add by hand.

Quick check before you rely on it: the last cumulative value should equal your month total in the pivot or statement, and the budget line should end exactly on the budget.

SPENDIXAI JOURNAL

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

Explore your reports