NPER
Returns the number of periods for an investment or loan based on periodic, constant payments and a constant interest rate.
NPER(rate, pmt, pv, [fv], [type])Arguments
rateInterest rate per periodpmtPayment made each periodpvPresent value (loan amount or current savings)fvFuture value (default 0)(optional)type0 = end of period, 1 = beginning(optional)
=NPER(5%/12, -500, 25000)About 56 months to pay off $25K at 5% with $500/month payments
=NPER(6%/12, -1000, 0, 100000)About 81 months of saving $1K/month to reach $100K at 6%
- •Divide annual rate by 12 for monthly periods
- •PMT should be negative (outgoing money)
- •Helps answer: how long until the loan is paid off?
- •Using an annual interest rate while expecting a result in months, or vice versa - the rate, pmt, and pv/fv must all be expressed in the same period to get a meaningful number of periods back
- •Mixing up the sign convention on pmt and pv - one should typically be negative (money paid out) and the other positive (money received), and using two positive or two negative values produces a #NUM! error or a nonsensical result
- •Expecting a whole number back - NPER often returns a fractional number of periods, meaning a loan might actually take, say, 47.3 months to pay off rather than a clean 47
PMTCalculates the periodic payment for a fixed number of periods, the inverse calculation of what NPER solves for.RATESolves for the interest rate rather than the number of periods, when the payment and term are already known.FVCalculates a future value over a fixed number of periods rather than solving for how many periods are needed.Why does NPER return a #NUM! error?
This usually means the sign convention on pmt and pv is wrong - one of them needs to be negative and the other positive to represent an outflow and inflow, since NPER can't resolve a scenario where both point the same direction.
Why is my NPER result a decimal instead of a whole number?
That's normal - NPER calculates the exact number of periods needed, which often isn't a round number. A payoff might genuinely take 47.3 months rather than exactly 47 or 48.
Do I need to convert the rate to match my payment frequency?
Yes, if payments are monthly, divide an annual rate by 12 - all the arguments need to describe the same time period for NPER to return an accurate answer.
Need to translate a formula using NPER?
Use our translator to convert your complete formula