Spreadsheets

Sinking fund tracker in Excel: monthly set-aside per goal

Make a sinking fund spreadsheet in Excel that shows the monthly amount to set aside for insurance, festivals, school fees and other yearly costs.

Editorial illustration for Sinking fund tracker in Excel: monthly set-aside per goal

A sinking fund spreadsheet takes each big irregular expense, such as an insurance premium or school admission, and divides what is still needed by the months left until it is due. The answer is a monthly set-aside amount for each goal. Add them up and you have one line to put in your household budget.

What columns does a sinking fund tracker need?

  • A: Item - yearly insurance, vehicle service, festival, school admission, gifts
  • B: Target amount in rupees
  • C: Due month, entered as a date, say 1-Mar-2027
  • D: Saved so far
  • E: Months left, calculated
  • F: Still needed, calculated
  • G: Monthly set-aside, calculated

Enter every item on its own row, starting at row 2, with headers in row 1. Begin with the expenses you already know about. Think of the last twelve months of lumpy bills: the ones that made a month feel tight.

Which formulas do I use?

In E2, months left, counting this month as a month you can contribute:

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

In F2, what is still needed, never below zero:

=MAX(0, B2-D2)

In G2, the monthly set-aside, rounded up to the next rupee so you do not fall short:

=ROUNDUP(F2/E2, 0)

At the bottom, =SUM(G2:G20) gives the total monthly contribution. The MAX(1, ...) in column E avoids a division by zero when a due date is this month or has already passed.

A worked example with made-up goals

Say today is in November 2026. Five goals, all hypothetical:

ItemTargetDueSavedMonthsMonthly
Yearly insurance₹24,000Mar 2027₹04₹6,000
Vehicle service₹6,000Jan 2027₹1,0002₹2,500
Festival fund₹30,000Oct 2027₹5,00011₹2,273
School admission₹40,000Jun 2027₹12,0007₹4,000
Gifts₹12,000Dec 2027₹013₹924

Check the festival row: 30,000 - 5,000 = 25,000, and 25,000 / 11 = 2,272.7, which rounds up to 2,273. The monthly column adds to 6,000 + 2,500 + 2,273 + 4,000 + 924 = ₹15,697. That is the number to set aside every month, and it shows why a quiet month can still be an expensive one.

How do I record what I have saved?

Add a tab called Log with three columns: date, item name and amount put aside. Then in D2 of the main sheet use =SUMIFS(Log!$C$2:$C$500, Log!$B$2:$B$500, A2). Each time you move money, add one line to the Log, and the saved figure and the monthly requirement update by themselves.

Treat the Months column as a promise to yourself. When the number looks too big, resist the urge to shrink the target. Either move the due month, if the real deadline allows, or decide which smaller version of the expense you will accept, such as a basic service in place of a full one.

What happens when a goal is paid or the target changes?

  • When you pay the expense, record it as spending, then set the row's target to the next cycle and clear saved so far.
  • If a price rises, change the target in column B; the monthly amount adjusts.
  • If you cannot afford the total, extend the due month only if the real deadline allows it.

Feeding the monthly total into your budget

Treat the total in column G as a fixed line in your monthly plan, next to rent and EMIs. In SpendixAI Pro, budgets and savings goals can carry the same numbers, so you can compare what you meant to set aside with what actually moved. The sheet is still a good place to work out the amounts.

Review once a quarter

  1. Reconcile each Saved so far figure with the real balance in your savings account.
  2. Add any new yearly expense that surprised you.
  3. Remove goals that are done and reset those that repeat.
SPENDIXAI JOURNAL

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

Explore your reports