Guides › Sinking Funds Spreadsheet: How to Set One Up (Google Sheets & Excel)

Sinking Funds Spreadsheet: How to Set One Up (Google Sheets & Excel)

Sinking funds turn the expenses that "come out of nowhere" (insurance renewals, car repairs, holidays, annual subscriptions) into small, planned monthly amounts. A spreadsheet is the ideal home for them: it can calculate how much each fund needs, show how far along you are, and keep a history of what you spent. This guide builds a complete tracker from a blank sheet in about 30 minutes, with every formula explained.

Step 1: Find your irregular expenses

Before opening a spreadsheet, gather twelve months of bank and credit card statements. Go through them and note every payment that did not happen every month. You are looking for three kinds of cost:

• Fixed and dated: an annual insurance premium, a vehicle registration, a software renewal. You know the amount and the date.
• Fixed amount, flexible date: holiday gifts, a planned trip, a new phone. You control the timing.
• Unpredictable amount, predictable likelihood: car maintenance, home repairs, vet bills, replacing worn-out clothes. You know something will happen in a year; you just don't know exactly what.

Most households find ten to twenty items. Group small similar ones ("annual subscriptions") so the sheet stays manageable. If you are new to this, start with the five biggest and add the rest later; a small system you keep using beats a perfect one you abandon.

Step 2: Create the funds table

Open a new sheet and create these column headers in row 1:

Fill in A to C for each fund. For the "unpredictable amount" funds, use your yearly average as the target and set the due date twelve months out; when the date arrives, roll it forward a year.

Step 3: Add the formulas

In row 2, enter these and copy them down:

• Months left (E2): =MAX(1, DATEDIF(TODAY(), C2, "M")). The MAX stops a division by zero in the month the bill is due.
• Monthly amount (F2): =MAX(0, (B2-D2)/E2). What you still need, spread over the remaining months.
• % funded (G2): =IF(B2=0, 0, D2/B2), formatted as a percentage.

Under the table add a total: =SUM(F2:F20). This single number is the most important output of the whole sheet: the monthly amount to transfer into your sinking fund account. Divide it by your paychecks per month to get a per-paycheck figure, or use our sinking fund calculator to check your numbers.

Add conditional formatting to column G: a color scale from red (0%) to green (100%) turns the column into a quick progress bar. In Google Sheets, Format › Conditional formatting › Color scale; in Excel, Home › Conditional Formatting › Color Scales.

Step 4: Log contributions and spending

Create a second tab called "Log" with columns: Date, Fund, Amount, Note. Contributions are positive, spending is negative. Use data validation on the Fund column so it is a dropdown of your fund names (Data › Data validation › List from a range, pointing at the fund names on the first tab). This prevents typos that would break the totals.

Back on the first tab, the balance in D2 becomes: =SUMIF(Log!B:B, A2, Log!C:C). Now every contribution and every withdrawal updates the right fund automatically, and you have a full history to look at when setting next year's targets.

Step 5: Connect it to your budget

Your monthly budget should contain a single line, "Sinking funds", equal to the total from step 3. Treat it like rent: it is paid on payday before discretionary spending. If you use the 50/30/20 method, essential funds (insurance, car maintenance) belong in needs, while gifts and travel belong in wants. Splitting the total that way gives a truer picture of your budget than putting everything in savings.

Set up an automatic transfer from checking to a separate savings account for the monthly total. One account with the spreadsheet tracking the split is perfectly fine; you do not need a separate bank account per fund.

Step 6: Spending from a fund

When the insurance bill arrives, pay it from checking as usual, then move the same amount from the sinking fund account back to checking and log it as a negative amount for that fund. The fund drops to (near) zero, and you update the due date to next year. If the bill was higher than the target, the fund is short. Cover the gap from the general buffer or another flexible fund and raise next year's target.

Monthly review: ten minutes

Once a month, ideally right after payday:

1. Confirm the automatic transfer happened and log it, split across funds according to column F.
2. Log any spending from funds.
3. Scan column G for funds that are behind with a near due date.
4. Roll forward the due dates of any funds that were used.

That is all. Because the monthly amount recalculates, a fund that fell behind automatically asks for more in the following months.

Common mistakes

• Too many funds. Twenty-five funds of $5 each is busywork. Merge small ones.
• Using last year's prices. Premiums and subscriptions tend to rise. When a renewal notice arrives, update the target immediately.
• Borrowing between funds without logging it. It's fine to move money between funds; just record both sides so balances stay honest.
• Mixing with the emergency fund. The emergency fund covers the unexpected and should not be used for a planned holiday.

Do it yourself or use a template?

Everything above is free to build, and building it yourself teaches you how each formula works. If you would rather start from a finished file, our Monthly Budget Tracker includes category budgets and automatic spent/remaining totals that pair well with a sinking funds tab, and the Paycheck Budget template plans sinking-fund transfers payday by payday.

Example funds to start with

If you are unsure where to begin, these five cover the most common budget-breakers: annual or semi-annual insurance, car maintenance and registration, gifts and holidays, annual subscriptions and memberships, and home or appliance replacement. Fill in your own targets from your statements; the list matters more than the exact numbers in the first month.

FAQ

What formula calculates a sinking fund contribution?

Monthly amount = (target − current balance) ÷ months until the due date. In Sheets or Excel: =MAX(0,(B2-D2)/MAX(1,DATEDIF(TODAY(),C2,"M"))).

Do I need a separate bank account for each sinking fund?

No. One separate savings account is enough; the spreadsheet tracks how much of it belongs to each fund.

Where do sinking funds go in a budget?

Add one line equal to the total monthly contribution and pay it on payday, before discretionary spending.

What happens if an expense is higher than the fund?

Cover the gap from a flexible fund or your buffer, log it, and raise the target for next time.

Does this work in Excel?

Yes. All formulas used (SUMIF, DATEDIF, MAX, IF) work in both Excel and Google Sheets.

For general education and planning only. Not financial, tax or legal advice. Check important decisions with a qualified professional.