Spreadsheets

Daily spending allowance for the rest of the month: formula

Work out how much you can spend per day for the rest of the month with a budget, spend so far, EOMONTH and TODAY, plus a guard for the last day.

Editorial illustration for Daily spending allowance for the rest of the month: formula

The daily spending allowance is what is left of the budget divided by the days left in the month. In a sheet that is =(budget - spent) / (EOMONTH(TODAY(),0) - TODAY() + 1). It updates by itself each day, and a small guard stops it showing a negative figure or a divide-by-zero on the last day.

The setup: three cells

Say you set a food budget in B1. Spent so far this month goes in B2, pulled from your expense log with SUMIFS (log: date in A, category in B, amount in C, rows 2 to 500):

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

The first date test starts at the first of this month; the second stops at today, so post-dated entries do not count. Put the number of days left in B3:

=EOMONTH(TODAY(),0)-TODAY()+1

EOMONTH(TODAY(),0) is the last day of this month. Subtracting today gives the days after today; adding 1 counts today itself. On the last day of the month the result is 1, never 0, so there is nothing to divide by zero.

What is the allowance formula?

In B4:

=MAX(0,B1-B2)/B3

MAX(0, ...) keeps the figure at zero once you have overspent, instead of showing a negative daily allowance that makes no sense. Show the overspend separately in a message cell:

=IF(B2>B1,"Over budget by ₹"&TEXT(B2-B1,"#,##0"),"₹"&TEXT(B4,"#,##0")&" a day for "&B3&" days")

Should today count as a day left?

It depends on whether today's spending is already in the log. If you update the sheet at night after the day's spending, today is already used up, so count only the days after today: =EOMONTH(TODAY(),0)-TODAY(), and then protect the last day with MAX(1, ...) around it. If you check in the morning before spending, count today, as in the formula above. Pick one habit and stick to it.

A worked example with hypothetical numbers

Say your food budget is ₹9,000 for a 30-day month and you check in the morning, so today counts.

Day of monthSpent so farDays left (including today)Daily allowance
10th₹3,00021₹286
18th₹5,40013₹277
30th₹8,7001₹300

Check the 18th: 9,000 minus 5,400 is 3,600; 3,600 divided by 13 is 276.9, shown as ₹277. On the 10th: 6,000 divided by 21 is 285.7, so ₹286. On the 30th: 300 left for the one remaining day. Notice the allowance is not fixed; it drifts up when you spend less than the pace and down when you spend more.

What if the budget is for the whole month and I use a card?

The formula only cares about the date you record against each expense. If you track by swipe date, it works as shown. If your card bills arrive later, decide whether spending counts when you swipe or when you pay the bill, and use that one date consistently in the log.

Can I show the figure per week instead?

Yes. Some people find a daily number too twitchy because one dinner out moves it. Divide the remaining budget by the weeks left instead: =MAX(0,B1-B2)/(B3/7). With ₹3,600 left and 13 days to go, that is 3,600 divided by 1.86 weeks, roughly ₹1,938 a week. Same maths, calmer rhythm. Keep the days-left cell in view, because the weekly figure is only as honest as the days it assumes.

Checklist before you rely on it

  • Dates in the log are real dates, not left-aligned text.
  • Budget and spend refer to the same category names, spelt identically.
  • The days-left cell never shows 0 or a negative number.
  • You decided whether today counts and stuck to it.

If maintaining the log is the weak link, SpendixAI has budgets on Pro and imports statements you upload, so the spent-so-far number is not typed by you; the formula above remains a fine back-of-envelope check.

SPENDIXAI JOURNAL

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

Explore your reports