Sinking Funds in a Spreadsheet: Formulas to Stop "Surprise" Expenses Wrecking Your Budget

Car repairs. Annual insurance. Holiday gifts. A new phone. Vet bills. School supplies. Most of the expenses that wreck a budget aren't really surprises - we know they're coming, we just don't know exactly when, or we forget until the bill arrives.

Sinking funds fix this. They're one of the simplest budgeting habits, and with a few formulas a spreadsheet can do all the maths for you. This guide explains what they are, how to calculate them, and how to build a small tracker in Google Sheets or Excel.

What is a sinking fund?

A sinking fund is money you set aside a little at a time for a specific future expense. Instead of paying a big bill all at once (or putting it on a credit card), you save toward it in smaller monthly amounts.

Example: if your car insurance renews once a year for 600, you set aside 50 a month. When the renewal arrives, the money is already there.

Sinking fund vs. emergency fund

They're often confused, but they do different jobs:

• An emergency fund is for truly unexpected events: job loss, a medical emergency, an urgent major repair.
• A sinking fund is for expected expenses: things you know will happen, even if the exact date or amount is a little uncertain.

Keeping them separate protects your emergency fund. If you use your emergency savings every time an annual bill arrives, it won't be there when a real emergency happens.

Which sinking funds should you start with?

Look back through the last 12 months of spending and find the costs that didn't happen monthly. Common sinking funds include:

• Car maintenance, repairs and registration
• Insurance premiums paid annually or quarterly
• Holidays and travel
• Birthdays, gifts and festive seasons
• Medical and dental costs not covered by insurance
• Home maintenance and repairs
• Pet care and vet visits
• Annual subscriptions and memberships
• Clothing and school costs
• Replacing electronics such as phones and laptops

You don't need all of these at once. Start with the two or three that have caused you the most stress.

The core formula

Monthly amount = (target cost - already saved) / months until you need it

If a holiday will cost 1,800 in nine months and you've saved nothing yet, you set aside 200 per month.

For expenses with an uncertain amount (like car repairs), use what you spent in the past year or two as your estimate. For expenses with no fixed date, choose a yearly amount and divide by twelve.

Building the tracker: the Goals sheet

Create a sheet called Goals with these columns, one fund per row starting at row 2:

A: Fund	B: Target	C: Target date	D: Saved	E: Left	F: Months left	G: Per month	H: Progress
Car	1200	2027-09-30	150
Insurance	850	2027-07-15	0
Gifts	900	2026-11-25	300

Then add the formulas in row 2 and fill down.

Amount left (never below zero):

E2:  =MAX(0, B2 - D2)

Whole months until the target date (at least 1, so you never divide by zero):

F2:  =MAX(1, DATEDIF(TODAY(), C2, "M"))

How much to save each month:

G2:  =IF(E2 = 0, 0, ROUNDUP(E2 / F2, 0))

Progress as a percentage (format the column as %):

H2:  =IF(B2 = 0, 0, MIN(1, D2 / B2))

At the bottom of the table, total what you need to set aside every month:

G15: =SUM(G2:G13)

If you get paid weekly or every two weeks, convert the monthly total:

Per week:      =G15 * 12 / 52
Per paycheck:  =G15 * 12 / 26     (biweekly)

Logging deposits and withdrawals

Instead of typing "Saved" by hand, keep a second sheet called Log with three columns: A: Date, B: Fund, C: Amount (positive for deposits, negative when you spend from a fund). Then replace the Saved column in Goals with:

D2:  =SUMIF(Log!B:B, A2, Log!C:C)

Now every transfer you record updates the balance, the amount left, the monthly figure and the progress automatically. When you spend from the car fund, log -320 against "Car" and the monthly amount recalculates for you.

A progress bar without charts

In Google Sheets you can draw an inline progress bar in each row:

I2:  =SPARKLINE(H2, {"charttype","bar"; "max",1; "color1","#2e7d32"})

In Excel, select the Progress column and use Conditional Formatting > Data Bars for the same effect.

Flag funds that are falling behind

A simple status column helps you spot trouble early. This marks a fund as "Behind" when the monthly amount it needs is more than a sixth of its whole target - a sign the date is close and the gap is still big:

J2:  =IF(D2 >= B2, "Done",
       IF(G2 > B2 / 6, "Behind", "On track"))

Tune the threshold to taste - the point is to make a fund that needs an unusually large monthly amount stand out.

What if the total is too big?

Many people add up their sinking funds and get a shock: the monthly total looks impossible. That's normal, and it's also useful information - these costs were always there; you were just paying for them in painful lumps.

Options:

• Prioritise. Fund the essential ones (insurance, car, medical) first, and the nice-to-haves (travel, upgrades) later.
• Adjust the target. Maybe the holiday can be simpler, or the phone upgrade can wait a year.
• Extend the timeline. A goal 18 months away needs less per month than one in 6 months.
• Use windfalls. Tax refunds, bonuses or extra paychecks can top up several funds at once.

Because the sheet recalculates, you can try each option by editing one cell and watching the total in G15 change.

Where to keep sinking fund money

• One separate savings account for all sinking funds, tracked in the spreadsheet. Simple and common.
• Multiple named savings "pots" or "buckets". Many banks let you create named sub-accounts. Seeing "Car" and "Gifts" separately is very motivating.
• Cash envelopes, for those who prefer physical money for spending categories.

Wherever you keep it, keep it separate from your everyday spending account, so it doesn't quietly get spent.

Common sinking fund mistakes

• Starting too many funds at once. Start with a few and add more over time.
• Not adjusting after spending. With the SUMIF log above, this happens automatically.
• Borrowing between funds without tracking. Moving money from "Holiday" to "Car" is fine - just log a negative entry on one and a positive on the other.
• Forgetting to automate. Set up automatic transfers on pay day. The guide to budgeting by paycheck shows how to fit this in.

A simple example

Imagine a household with four sinking funds: car maintenance (1,200 per year), annual insurance (850 in nine months), gifts (900 by late November) and a summer holiday (3,000 by July). They already have a little saved toward some of them.

The sheet divides each remaining amount by the months left and totals the monthly figure. It feels like a lot at first, so they reduce the holiday target slightly and push a laptop replacement to next year. Now the total fits their budget - and next time the car needs new tyres, it's simply a negative line in the Log sheet instead of a crisis.

The bottom line

Sinking funds turn big, stressful bills into small, boring monthly transfers. List your non-monthly expenses, let a few formulas divide each by the months left, automate the transfers and log every deposit and withdrawal. Within a year, "surprise" expenses become just another line in your budget.

This article is general information, not financial advice.

---

If you'd rather not build it from scratch, the free sinking funds calculator works out the monthly, weekly and per-paycheck amount for several funds at once - no signup needed.