Excel NPER Function
NPER calculates the number of periods needed to repay a loan or reach a savings goal, assuming a fixed interest rate and equal periodic payments.
Use it to find how long a repayment plan will take or how many regular deposits you need to reach a target balance.
NPER syntax & arguments
Syntax
=NPER(rate, pmt, pv, [fv], [type])
-
1
rate
RequiredThe constant interest or discount rate per period. Use an annual rate for annual cash flows or a monthly rate for monthly cash flows. To convert an annual rate to a monthly rate, divide by 12 if it is a nominal annual rate compounded monthly. If it is an effective annual rate, use
(1+annual_rate)^(1/12)-1to find the equivalent monthly rate. -
2
pmt
RequiredThe fixed payment each period. Use a negative amount for money paid out, or 0 when growing only an initial lump sum.
-
3
pv
RequiredThe balance or lump sum at time zero. Money received is positive and money paid out is negative.
-
4
fv
OptionalThe balance or lump sum at the end of the final period. Money received is positive and money paid out is negative. Defaults to 0.
-
5
type
OptionalWhen periodic payments occur. This changes payment timing, not the dates of pv or fv.
0: Payments at the end of each period. This is the default when the argument is omitted.1: Payments at the beginning of each period.
Example
=NPER(10%,-1100,2100,0,1)
Return the number of years needed to repay a 2100 loan with 1100 paid at the beginning of each year, given a constant annual interest rate of 10% and a final balance of 0.
NPER caveats
-
What positive and negative amounts mean
The sign of
pvdescribes the starting amount: positive for money you receive, such as loan proceeds, and negative for money you pay out, such as an initial savings deposit. The sign ofpmtdescribes each periodic payment: positive for money received and negative for money paid out.The sign of
fvdescribes the ending amount: positive for money you’ll receive or have available, and negative for money you’ll owe or pay out. Anfvof0means no ending amount. -
Rate and payments must use matching periods
For monthly payments, use the monthly payment amount for
pmtand a monthly interest rate forrate. If the annual rate is nominal 6% compounded monthly, use6%/12. If it is an effective annual rate of 6%, use(1+6%)^(1/12)-1instead. Entering6%directly would apply 6% every month. -
Payment timing changes the period count
Omitted
typeor0means end-of-period payments;1means beginning-of-period payments. Paying earlier can reduce the number of periods needed at a positive rate. Thepvamount stays at time zero andfvstays at the end of the final period. -
The result can include a fraction of a period
NPER does not round to a whole number of payments. For example,
=NPER(0,-300,1000)returns about3.33periods. With payments on fixed dates, this loan needs a fourth payment with its amount adjusted to repay the remaining balance. -
Payments and rate stay constant
NPER assumes equally spaced, equal payments and one rate for all periods. A changing rate, irregular payment dates, or extra repayments need to be modeled separately.
-
Zero interest makes the calculation simpler
At
rate=0, NPER returns-(pv+fv)/pmt. For example,=NPER(0,-500,2000)returns4periods, regardless of payment timing. Ifpmtis also0, the calculation divides by zero and returns#DIV/0!. -
The payment must be enough to repay the loan
For a loan repaid in full with end-of-period payments and a positive rate, the payment must exceed the interest charged each period so the balance can fall. For example,
=NPER(10%,-100,2000)returns#NUM!: each payment is less than the first period’s interest of200, so the loan balance grows. -
A lump sum needs a zero payment
To find how long an initial deposit takes to grow to a target balance without further deposits, enter
0forpmtand opposite signs forpvandfv. For example,=NPER(10%,0,-1000,1210)returns2periods. Putting the initial deposit inpmtwould repeat it every period.
Comparing repayment plans? Use PMT to find the fixed payment for a chosen period count, or FV to check the balance after a set number of periods.
Intro NPER practice problem
Solve the intro problem directly here, or open it on its own page.
Advanced NPER practice problems
Use NPER alongside other Excel functions in realistic, less-prescriptive challenges.
No advanced NPER problems are currently available.