NPER
Work out the number of periods needed to pay off a loan, or to reach a savings target, at a fixed rate and payment.
Syntax
NPER(rate, pmt, pv, [fv], [type])| Argument | Required | Description |
|---|---|---|
rate | Required | The interest rate per period. |
pmt | Required | The payment each period, negative for money paid out. |
pv | Required | The amount now. |
fv | Optional | The balance to reach. Defaults to 0. |
type | Optional | 0 (the default) for payments at the end of each period, 1 for the start. |
Examples
Every example below was executed against VisiGrid engine 0.54.0 — not
transcribed from another vendor's documentation. Reproduce any of them with vgrid calc.
| Formula | Result | Notes |
|---|---|---|
=NPER(0.12/12,-100,-1000,10000,1) | 59.67 | Microsoft's example — about 60 months of 100 at 1% a month to reach 10,000. |
=NPER(0.05/12,-1500,200000) | 195.03 | Paying 1,500 a month clears a 200,000 loan at 5% in about 195 months — just over 16 years. |
=NPER(0,-100,1000) | 10 | With no interest it is the amount divided by the payment. |
=NPER(0.05/12,PMT(0.05/12,360,200000),200000) | 360.00 | NPER undoes PMT. The .00 is floating-point residue. |
How long?
NPER answers “how long will this take”: to pay off a card at a fixed monthly
amount, to clear a mortgage with overpayments, to save up a deposit. The result
is usually fractional — round it up with ROUNDUP for
whole payments.
When there is no answer
If the payment does not even cover the interest, the balance never falls and
NPER returns #NUM!. That is worth knowing on its own: it means the payment
is too small to ever finish.
Excel compatibility
Matches Excel, including #NUM! when the payment can never reach the target.