Sinking Funds Spreadsheet: Monthly and Per-Paycheck Savings for Irregular Bills (Excel & Google Sheets)
Short on time? The 2027 Budget Planner ($12.99) is the finished, tested version of what this tutorial builds.
A sinking fund is money you set aside a little at a time for a bill you know is coming: the six-month car insurance premium, holiday gifts, a summer trip, the next set of tires. The math looks like "cost ÷ 12", but that's only right if the bill is due exactly twelve saving dates from now. Most aren't. And if you're paid every two weeks, "monthly amount ÷ 2" quietly leaves some funds short.
This tutorial builds a two-tab sheet, Settings and Funds, that counts the saving dates and paydays actually left before each due date and divides by those. It works the same in Excel, Google Sheets and LibreOffice, using only DATE, YEAR, MONTH, DAY, INT, IF, MAX, ROUND and SUM. To test it, we built a workbook with these exact formulas, recalculated it in LibreOffice, and compared every cell to the worked example below and to an independent Python model that lists every saving date and payday one by one, on the example plus 40 randomized sets of funds.
1. The Settings tab
B1 (As of): 2027-01-01
B2 (Saving day): 1
B3 (Next payday): 2027-01-08
B4 (Days between pays): 14
B5 (First saving date): =IF(DAY(B1)<=B2,DATE(YEAR(B1),MONTH(B1),B2),DATE(YEAR(B1),MONTH(B1)+1,B2))
As of is the date you're planning from (use =TODAY() if you want the sheet to move on its own). Saving day is the day of the month you move money into your sinking funds; keep it between 1 and 28 so every month has one. Next payday is your first payday on or after the as-of date, and Days between pays is 14 for every two weeks or 7 for weekly.
First saving date is the next saving day on or after the as-of date. If this month's saving day has already passed, it moves to next month. DATE(YEAR(B1),MONTH(B1)+1,B2) is safe in December: month 13 rolls over to January of the next year in Excel (Microsoft's DATE help) and in Google Sheets (Google's DATE help).
2. The Funds tab
Row 1 holds the headers: Fund, Target, Due date, Saved so far, Saving / month, Still needed, Months left, Needed / month, Paychecks left, Needed / paycheck, Projected, Shortfall, Status. You type columns A–E. Columns F–M are formulas; fill them down as far as you need.
F2 (Still needed): =MAX(0,B2-D2)
G2 (Months left): =IF(C2<Settings!$B$5,0,(YEAR(C2)-YEAR(Settings!$B$5))*12+MONTH(C2)-MONTH(Settings!$B$5)+IF(DAY(C2)>=Settings!$B$2,1,0))
H2 (Needed / month): =IF(G2>0,F2/G2,F2)
I2 (Paychecks left): =IF(C2<Settings!$B$3,0,INT((C2-Settings!$B$3)/Settings!$B$4)+1)
J2 (Needed / paycheck): =IF(I2>0,F2/I2,F2)
K2 (Projected): =D2+E2*G2
L2 (Shortfall): =MAX(0,B2-K2)
M2 (Status): =IF(F2=0,"Funded",IF(ROUND(L2,2)=0,"On track","Behind"))
Months left counts the saving dates from the first saving date up to and including the due date. The (YEAR…)*12+MONTH… part counts whole months between them, and the IF(DAY(C2)>=…) adds one more if the due date falls on or after that month's saving day. A bill due July 1 with saving on the 1st, planned from January 1, gets 7 saving dates (January through July). Planned from January 2, it gets 6.
Paychecks left counts paydays from the next payday to the due date, inclusive: the days between them divided by 14, rounded down, plus the next payday itself. If a fund has no saving dates or paychecks left, the Needed columns show the whole amount still needed, because it has to come from this month's money.
Projected is what the fund will hold on its due date if you keep saving what you typed in column E. Status says Funded once the target is already saved, On track if the projection reaches the target, and Behind otherwise. ROUND keeps a one-cent floating-point difference from showing a fund as Behind.
3. Totals for your budget
Back on Settings:
B7 (Total needed / month): =SUM(Funds!H:H)
B8 (Total needed / paycheck): =SUM(Funds!J:J)
B9 (You're saving / month): =SUM(Funds!E:E)
B10 (Monthly gap): =B7-B9
B7 is the line to put in your monthly budget (or B8 if you budget by paycheck). A positive gap is how much more per month you'd need to set aside to keep every fund on track.
Worked example
Settings: as of Jan 1, 2027, saving day 1, next payday Jan 8, 2027, paid every 14 days.
| Fund | Target | Due date | Saved so far | Saving / month | Still needed | Months left | Needed / month | Paychecks left | Needed / paycheck | Projected | Shortfall | Status |
|---|---|---|---|---|---|---|---|---|---|---|---|---|
| Car insurance (6-mo) | 660.00 | 2027-07-01 | 120.00 | 90.00 | 540.00 | 7 | 77.14 | 13 | 41.54 | 750.00 | 0.00 | On track |
| Holidays 2027 | 1200.00 | 2027-12-01 | 0.00 | 100.00 | 1200.00 | 12 | 100.00 | 24 | 50.00 | 1200.00 | 0.00 | On track |
| Summer vacation | 2000.00 | 2027-08-01 | 400.00 | 60.00 | 1600.00 | 8 | 200.00 | 15 | 106.67 | 880.00 | 1120.00 | Behind |
| Car repairs | 800.00 | 2027-12-31 | 250.00 | 0.00 | 550.00 | 12 | 45.83 | 26 | 21.15 | 250.00 | 550.00 | Behind |
Totals: needed $422.98 a month or $219.36 a paycheck. You're saving $250.00 a month, so the monthly gap is $172.98.
Three things this example shows:
- "Cost ÷ 12" undersaves anything due before December. The vacation still needs $1,600, and $1,600 ÷ 12 is $133.33 a month. But August 1 is only 8 saving dates away, so it really needs $200.00 a month.
- "Monthly ÷ 2" doesn't work on a biweekly paycheck. January 8 to July 1 has 13 paydays, not 14. Saving $38.57 a paycheck (half of $77.14) for the car insurance leaves you $38.59 short in July. The vacation, at $100 a paycheck over 15 paydays, ends $100.00 short. Divide by the real count instead.
- Three-paycheck months help funds due late in the year. Car repairs gets 26 paydays before December 31, so $21.15 a paycheck is less than half of the $45.83 monthly amount.
Common mistakes
- Counting the current month after its saving day has passed. If you plan on January 20 with saving on the 1st, January is gone. The First saving date cell handles this; a plain "months between" formula doesn't.
- Forgetting the money already saved. Divide what's still needed, not the full target, or every fund looks more expensive than it is.
- Leaving a fund at $0 needed after the bill is paid. When the bill goes out, set Saved so far back to 0 and move the due date to the next one. The fund starts filling again.
- Typing percentages or text in the dates. A due date typed as text ("July 1") makes the comparisons fail. Format column C as a date.
Going further
This sheet covers sinking funds on their own. The paid 2027 Budget Planner puts them inside a whole-year budget: a month-by-month contribution grid for each fund, paycheck and bill planning for weekly, every-two-weeks and twice-a-month pay, variable income, debt payoff (snowball or avalanche) with a 120-month schedule, savings goals and an annual dashboard, plus dark mode. But the two tabs above are a complete, working sinking-fund planner on their own. This is a budgeting method, not financial advice.
Written with AI assistance. Every formula, command and number on this page was tested before publishing (see each section). Product links go to our store.