Written and reviewed by FormulaCraft Team. Each formula on this page is run through our verification engine before publishing.
Last reviewed:
How to work out the monthly savings to reach a goal in Excel and Google Sheets
Topic:Finance basicsExcel & Google Sheets
=PMT(A2/12,B2*12,0,-C2)✓
Verified example
Computed by a real spreadsheet engine on the sample data below.
| Annual Rate | Years | Goal | Monthly Savings |
| 0.05 | 10 | 100000 | |
| 0.03 | 5 | 20000 | |
| 0.07 | 20 | 500000 | |
| 0.04 | 3 | 15000 |
=PMT(A2/12,B2*12,0,-C2)→643.9884857241
Try it with your data
Edit the grid or formula, then run it through a real spreadsheet engine — no signup.
Sample data — click any cell to edit
Runs server-side · free · no signup
Step by step
- 1Column A = Annual Interest Rate (e.g., 0.05 for 5%), Column B = Years to Goal, Column C = Goal Amount.
- 2In D2 enter =PMT(A2/12,B2*12,0,-C2).
- 3PMT's arguments: rate per period, number of periods, present value (0 = starting from scratch), future value (negative because it's money going out).
- 4The result is positive — the amount to deposit each month.
Tips
- If you already have some savings, put the current balance as the PV argument (negative): =PMT(A2/12,B2*12,-CurrentSavings,-GoalAmount).
Working on a sheet you inherited? Run the Auditor on the whole file first — it flags every #REF!, #N/A, broken column pattern, and inconsistent formula in seconds, free, no signup.
Frequently asked
Why is the result negative and how do I fix it?
PMT returns a negative number to represent an outflow. Negate the result by prefixing with a minus sign: =-PMT(A2/12,B2*12,0,-C2).
What if I want to contribute at the START of each period (annuity due)?
Add 1 as the final argument: =PMT(A2/12,B2*12,0,-C2,1). This gives a slightly lower required payment because deposits earn interest one period longer.
More on Finance basics
See all →How-to
How to calculate compound interest
Calculate a compounded closing balance from principal, annual nominal rate, compounding frequency and years, then separate the interest earned.
How-toHow to calculate the effective annual interest rate
Convert a positive nominal annual rate and a whole-number compounding frequency into an effective annual rate with EFFECT.
How-toHow to calculate the break-even point in units
Divide fixed costs by unit contribution margin and round up to whole units, with an explicit message when the margin is nonpositive.
How-toHow to calculate tiered commission
Apply different commission rates by sales tier using IFS. Works in Excel 2019/365 and Google Sheets.
How-toHow to calculate net pay
Calculate take-home (net) pay by subtracting every deduction from gross salary, with worked examples for fixed amounts, percentage tax, and slab-based tax. Excel and Google Sheets.
How-toHow to calculate gross margin
Compute gross margin percentage as (price − cost) / price. Identical in Excel and Google Sheets.