# 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%.

---

- **Canonical URL:** https://dothecalculation.com/blog/templates/excel-savings-goal-pmt-nper-rate
- **Category:** Templates
- **Author:** Do The Calculation Team
- **Published:** 2026-08-03
- **Reading time:** 10 min read
- **Publisher:** Do The Calculation (https://dothecalculation.com)
- **Methodology:** https://dothecalculation.com/methodology

---

## 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.

Tool: [Try the savings calculator](https://dothecalculation.com/calculators/savings-calculator) — Project a balance from a starting amount, a monthly contribution, and a rate.

## The sign convention

> **Money leaving you is negative** — A contribution you make is negative. A balance you will receive is positive. Excel needs at least one of each in the same formula, and if every value has the same sign it returns #NUM!. That single rule explains most of the errors people hit with these functions.

**Signs for a savings goal**
| 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

**Inputs, in named cells**
| Name | Value |
| --- | --- |
| Target | $60,000 |
| Years | 5 |
| AnnualRate | 5.0% |
| MonthlyRate | =AnnualRate/12 |
| Periods | =Years*12 |
| AlreadySaved | $12,000 |

## How much a month

**PMT, with and without a starting balance**

```
=-PMT(MonthlyRate, Periods, 0, Target)    ·    =-PMT(MonthlyRate, Periods, -AlreadySaved, Target)
```
- From zero: $882.27 a month.
- With $12,000 already saved: $655.82 a month.
- The $12,000 grows to $15,400 over the five years, which is why the contribution falls by more than $12,000 ÷ 60.

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

**NPER**

```
=NPER(MonthlyRate, -600, -AlreadySaved, Target)
```
- At $600 a month from a $12,000 base: 64.5 months, or 5 years and 5 months.
- The result is in periods, so divide by 12 for years: =NPER(...)/12.
- A fractional result is real. 64.5 means the goal is reached partway through month 65.

## What return it would take

**RATE**

```
=RATE(Periods, -700, -AlreadySaved, Target)*12
```
- Contributing $700 a month from $12,000 reaches $60,000 at about 3.44% a year.
- RATE returns a rate per period, so multiply by 12 for an annual figure.
- It solves iteratively and returns #NUM! when it cannot converge; supplying a guess as the sixth argument usually fixes it.

> **Read the RATE answer as a feasibility test** — If it comes back at 3.44%, the plan works in a savings account. If it comes back at 14%, the plan is not a savings plan; it is a hope. RATE is the most useful of the five functions precisely because it converts a wish into a required return you can judge.

## What a plan produces

**FV, and the type argument**

```
=FV(MonthlyRate, Periods, -700, -AlreadySaved)    ·    =FV(MonthlyRate, Periods, -700, -AlreadySaved, 1)
```
- Without the type argument, contributions are assumed at the end of each period.
- Passing 1 makes them at the beginning, which is how a standing order on payday actually works.
- On this plan the difference is $198 over five years, small but free.

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.

**The same goal, stated two ways**
| Basis | Target | Monthly from $12,000 |
| --- | --- | --- |
| Nominal $60,000 | $60,000 | $655.82 |
| $60,000 of today's buying power | $69,556 | $796.34 |

**Inflating the target**

```
=Target*(1+InflationRate)^Years
```
- 60,000 × 1.03⁵ = $69,556
- The required contribution rises by 21%, from $655.82 to $796.34.
- Alternatively use a real rate throughout: =(1+nominal)/(1+inflation)−1, and keep the target nominal.

Tool: [Try the inflation calculator](https://dothecalculation.com/calculators/salary-inflation-calculator) — Convert an amount between today and a future year at a given inflation rate.

## 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.

---

_Source: [Do The Calculation](https://dothecalculation.com/blog/templates/excel-savings-goal-pmt-nper-rate). Quote freely with attribution and a link to this page._