Spreadsheets

Sinking fund formula: set aside monthly for yearly bills in Excel

Build a sinking fund sheet in Excel: list yearly bills, count the months left, divide to get the monthly amount and track how funded each bill is.

Editorial illustration for Sinking fund formula: set aside monthly for yearly bills in Excel

A sinking fund sheet gives each yearly bill one number: the amount still needed divided by the months left before it falls due. In Excel that is =MAX(0,(B2-E2)/D2) on each row, and adding that column tells you the total to move aside every month. Here is the full layout, including the formula for months left and a column for how funded each bill already is.

What goes in the sinking fund table?

List every bill that arrives once or twice a year instead of monthly: an insurance premium, school fees, a vehicle service, an annual subscription renewal, society charges billed yearly. Set up one row per bill with these columns.

  • A: Bill name
  • B: Amount due in rupees
  • C: Due date, typed as a real date such as 01-04-2027 and not as text
  • D: Months left
  • E: Saved so far
  • F: Monthly set-aside
  • G: Funded percentage

How do you count the months left?

In D2 use this formula and fill it down:

=MAX(1,(YEAR(C2)-YEAR(TODAY()))*12+MONTH(C2)-MONTH(TODAY()))

It multiplies the difference in years by 12, then adds the difference in months. MAX(1, ...) keeps the divisor at one or more, so a bill due this very month does not cause a divide-by-zero error. TODAY() refreshes whenever the file opens, so the monthly amount rises slightly if you fall behind, which is the point. If D shows #VALUE!, the due date in column C was probably typed as text.

What are the monthly and funded-to-date formulas?

  • F2, monthly set-aside: =MAX(0,(B2-E2)/D2). Subtracting what is already saved means a half-funded bill asks for less.
  • G2, funded percentage: =IF(B2=0,0,E2/B2), formatted as a percentage.
  • E2, saved so far: =SUMIFS(Log!$C$2:$C$500,Log!$A$2:$A$500,$A2). This reads a second sheet called Log where you note each transfer, with the bill name in column A, the date in B and the amount in C.
  • Total row: =SUM(F2:F10) gives the one monthly figure to set aside.

What does it look like with real arithmetic?

Say today is 1 August and the bills below are made up for illustration. Months left follow from the formula above.

BillAmount dueMonths leftSaved so farMonthly set-aside
Insurance premium, due 1 April₹24,0008₹0₹3,000
School fees, due 1 June₹36,00010₹6,000₹3,000
Vehicle service, due 1 December₹9,0004₹1,000₹2,000
Total₹69,000-₹7,000₹8,000

Check one row by hand: school fees need ₹36,000 minus ₹6,000, which is ₹30,000, spread over 10 months, so ₹3,000 a month. Funded percentage there is 6,000 divided by 36,000, about 16.7 percent. The service row is 8,000 divided by 4, so ₹2,000, with 11.1 percent funded.

Where does the money actually sit?

A sheet only counts; it moves nothing. Many people keep the set-aside in a separate savings account or a clearly named pot and set a standing instruction for salary day. Whatever you choose, update the Log sheet when the transfer happens, because Saved so far is only as honest as the entries. When a bill is paid, move its due date a year ahead and reset its saved amount to zero.

Does this scale past a dozen bills?

One more habit helps: review the sheet each April, when the financial year turns over. Amounts change, some bills disappear and new ones arrive, such as a renewal you only noticed last month. Ten minutes then keeps the monthly figure close to reality, so you are not surprised by a bill that was never on the list.

For ten or twelve bills a sheet is perfectly fine. When you want the actual spending pulled in from statements and checked against savings goals, SpendixAI's Pro plan has bills, budgets and savings goals, with expenses imported from bank or card statements after you review the rows.

SPENDIXAI JOURNAL

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

Explore your reports