To have a set amount by a future date — replace equipment, fund a project — a sinking fund sets aside a regular deposit. PMT solves for that deposit given a target and a return.
The example
Reach $50,000 in 5 years at 4%.
| A | B | |
|---|---|---|
| 1 | Item | Value |
| 2 | Goal | $50,000 |
| 3 | Monthly deposit | $754 |
The formula
The formula:
How it works
How it works:
- A sinking fund accumulates a target through regular deposits plus growth.
PMT(rate/12, years*12, 0, -goal)solves for the deposit: PV is 0, the goal is the (negative) future value.- Compounding helps — the deposit is less than goal÷months because the balance earns return.
- It’s the mirror of an amortizing loan: instead of paying a balance down, you build one up.
Already have a head start? Put your current balance in the PV argument (negative): =PMT(rate/12, months, -current, -goal) lowers the required deposit because the existing balance grows too.
Try it: interactive demo
Goal, years, return.
Variations
With a starting balance
Lowers the deposit:
Lump sum needed now
One deposit:
FV of a set deposit
Reverse:
Pitfalls & errors
Sign convention. Goal as negative FV so PMT returns a positive deposit.
Rate/period match. Monthly deposits use rate/12 and years×12.
Return isn’t guaranteed. Treat the figure as a plan; real returns vary.
Practice workbook
Frequently asked questions
How do I calculate a sinking fund deposit in Excel?
How do I account for money already saved?
What single lump sum would reach the goal?
Stop fighting formulas. Learn them in a day.
This recipe is one of hundreds of real-world formulas we teach. Our Excel Formulas & Functions class covers lookups, logic, text, and dynamic arrays hands-on — live in Dallas–Fort Worth, Houston, Austin, Oklahoma City, Denver, or online.
See the Formulas & Functions Class