Savings Goals in Excel: PMT, NPER, RATE, and the Sign Convention
Four functions solve the same equation for four different unknowns, and all four fail the same way if the signs are inconsistent. Here is the full set worked on one $60,000 goal, plus the inflation adjustment that raises the required contribution by 21%.
One Equation, Four Unknowns
PMT, NPER, RATE, FV, and PV are the same time-value-of-money equation rearranged five ways. Give any four of rate, periods, payment, present value, and future value, and the fifth follows.
What trips people is not the algebra. It is the sign convention, which Excel enforces strictly and explains badly.
Try the savings calculatorProject a balance from a starting amount, a monthly contribution, and a rate.The sign convention
Swipe sideways to compare columns.
| Argument | Sign | Why |
|---|---|---|
| pv, money you already have saved | Negative | It is money you have put in |
| pmt, monthly contribution | Negative | Money leaving you each period |
| fv, target balance | Positive | Money you will receive |
| The PMT result | Negative | Negate the whole function if you want it positive |
The goal
Swipe sideways to compare columns.
| Name | Value |
|---|---|
| Target | $60,000 |
| Years | 5 |
| AnnualRate | 5.0% |
| MonthlyRate | =AnnualRate/12 |
| Periods | =Years*12 |
| AlreadySaved | $12,000 |
How much a month
That last point is worth noticing. Twelve thousand dollars divided over sixty months is $200. The actual reduction is $226.45, because the existing balance earns as well as the new contributions.
How long it takes
What return it would take
What a plan produces
The type argument defaults to 0, end of period, in all five functions. Most people contribute at the start of the month, so 1 is usually the more accurate model. The gap grows with the rate and the term.
The adjustment most plans skip
A $60,000 target five years out is not $60,000 of today's buying power. At 3% inflation it is worth $51,756 in current terms.
Swipe sideways to compare columns.
| Basis | Target | Monthly from $12,000 |
|---|---|---|
| Nominal $60,000 | $60,000 | $655.82 |
| $60,000 of today's buying power | $69,556 | $796.34 |
The five errors
- Annual rate with monthly periods. =PMT(5%, 60, 0, 60000) returns $169.69 and is nonsense. Divide the rate and multiply the periods together.
- All arguments the same sign, which returns #NUM!. At least one cash flow must point the other way.
- Forgetting the pv argument entirely, which silently assumes you are starting from zero and overstates the required contribution.
- Using the wrong positional argument. These functions take positional arguments and skipping one with a comma matters. =PMT(rate, nper, , fv) with the empty third slot is a present value of zero, which is usually what you meant.
- Ignoring the type argument on contributions made at the start of the period. It is a small error, and it is free to fix.
What the formulas assume
- A constant rate of return, every period, with no variability. Real returns are volatile, and a plan that works at a steady 5% can fail at an average 5% depending on the order the returns arrive.
- A constant contribution. Most people can increase contributions over time, and modelling a flat amount understates what is achievable.
- No tax. Interest in a taxable account is taxed as it is earned, so the effective rate is lower than the quoted one.
- No fees. A 0.75% fund fee on a 5% return takes 15% of the growth, and neither PMT nor FV knows about it. Subtract fees from the rate before using it.
- That the money stays invested. A withdrawal mid-plan is not modelled and changes the answer more than any rate assumption.
- That the goal is fixed. Most savings goals are for something whose price is also moving, which is what the inflation section addresses and most plans ignore.
Why does my PMT formula return #NUM!?
Almost always because every cash flow has the same sign. Excel needs at least one negative and one positive. Make the present value and the payment negative, the future value positive, and the error disappears.
Why is my PMT result negative?
Because it is money leaving you, which is the convention. Put a minus sign in front of the whole function, as =-PMT(...), to display it as a positive contribution. Do not fix it by flipping an input, since that breaks the calculation.
What does the type argument do?
It says whether cash flows happen at the end of each period, which is 0 and the default, or at the beginning, which is 1. Contributions made on payday are beginning-of-period, and each one earns an extra period of return.
How do I account for inflation in a savings goal?
Either inflate the target, with =Target*(1+inflation)^years, or use a real rate of return throughout, computed as (1+nominal)/(1+inflation)−1. Do one or the other, never both, and say which you did.
Can I model a contribution that increases each year?
Not with PMT, which assumes a constant payment. Build a row-per-period schedule instead, with the contribution as a formula that steps up annually. It is more work and it is the only way to model an escalating plan honestly.
What rate should I use?
Whatever the money will actually be in, net of fees, and net of tax if it is a taxable account. For a five-year goal that is a savings account or short-term bonds rather than equities, since a market fall in year four leaves no time to recover.
Written by
Do The Calculation Team
Do The Calculation
Do The Calculation is built by a small team of data analysts and spreadsheet developers. Where a guide depends on a published formula, standard, or government rule, the calculator it links to names that source directly so you can check the number yourself.
About the team