A Sinking Fund Tracker in Google Sheets: Spreading Irregular Expenses into Monthly Set-Asides

A sinking fund is just a bucket you fund gradually so a big, predictable expense doesn’t hit as one lump sum. Car registration, annual software licenses, holiday spending, insurance premiums paid yearly instead of monthly. The spreadsheet problem is mechanical: given a target amount and a due date, how much do you set aside each month, and how do you track progress without rebuilding the sheet every time a due date passes.

This tracker uses two tabs: a Funds tab listing each sinking fund with its target and due date, and a Contributions tab logging what’s actually been deposited. The Funds tab pulls contribution totals from the log and calculates the rest.

Setting up the Funds tab

Columns: Fund Name, Target Amount, Due Date, Start Date, Months Remaining, Monthly Set-Aside, Total Contributed, % Funded.

Months remaining between today and the due date, rounded up so a partial month still counts as a full month of saving:

=MAX(1, DATEDIF(TODAY(), C2, "m") + IF(DAY(TODAY()) > DAY(C2), 1, 0))

DATEDIF with the "m" unit gives completed months between two dates, but it truncates rather than rounds. The IF clause adds a month back when today’s day-of-month has already passed the due date’s day-of-month within the current month count, so a fund due in 3.2 months shows 4, not 3. Wrapping the whole thing in MAX(1, ...) keeps the formula from returning 0 or a negative number once the due date is close, which would break the division in the next formula.

Monthly set-aside, recalculated automatically as time passes and the remaining balance shrinks:

=(B2-G2)/E2

Where B2 is the target amount, G2 is total contributed so far, and E2 is months remaining. This is the useful part: it’s not a static “target ÷ 12” number set once at the start. Every month you contribute, the remaining balance drops and the months-remaining count drops too, so the required monthly amount adjusts on its own. If you fall behind one month, the formula raises the next month’s suggested set-aside instead of quietly letting the shortfall sit there.

Pulling contributions from the log

The Contributions tab has Date, Fund Name, Amount. Total Contributed on the Funds tab sums by name:

=SUMIF(Contributions!B:B, A2, Contributions!C:C)

Percent funded, formatted as a percentage:

=G2/B2

Set the cell format to percentage rather than multiplying by 100 in the formula. That way the underlying value stays a clean decimal, which matters if you later feed % Funded into a conditional format or a chart.

Flagging funds that are behind schedule

A useful conditional format rule: highlight any row where the actual contribution pace is lagging the expected pace. Expected pace, as of today, is what fraction of the total time-to-due-date has elapsed:

=(TODAY()-D2)/(C2-D2)

D2 is the start date, C2 is the due date. Compare this to % Funded (column H) with a custom conditional formatting rule:

=H2 < (TODAY()-$D2)/($C2-$D2)

If the fraction of time elapsed is greater than the fraction funded, the fund is behind pace and the row turns red (or whatever color you pick). This catches a fund that’s technically not overdue yet but is quietly falling behind, which a simple “funded vs. not funded” checkbox would miss.

Handling a fund that gets fully paid out and restarts

Annual expenses like registration or insurance premiums recur. Rather than deleting and re-adding the row each year, add a Reset After Payout checkbox column. When checked, a script isn’t necessary. Just wrap the due date formula so it rolls forward a year once the fund reaches 100%:

=IF(H2>=1, EDATE(C2,12), C2)

EDATE adds exactly 12 months to the due date, keeping the same day of month (with the usual caveat that months with fewer days get clamped, e.g. adding 12 months to Jan 31 in a year that lands on Feb 28/29). Pair this with a companion formula that zeroes Start Date to today whenever Due Date rolls forward, so the months-remaining and pace calculations reset cleanly instead of comparing against a stale start date from the prior cycle.

One thing worth double-checking before you rely on this sheet: the DATEDIF function isn’t documented in Excel’s formula picker in every version, though it works in both Excel and Google Sheets. If you’re building this in Excel and it doesn’t autocomplete, that’s expected. Type it manually and it’ll still calculate.