Table of Contents
Summary
The Excel NPER function calculates the number of periods, or amount of time, needed to payoff a loan or reach an investment objective. The function assumes constant payments (or cashflow if an investment) will be made. The NPER function is an Excel financial function.
Function
=NPER(rate, pmt, pv, [fv], [type])
Arguments (inputs)
rate = the interest rate
pmt = the payment per period
pv = present value, the loan or principle amount
fv = [optional] the future value of the loan. If left blank it will default to 0.
type = [optional] 1 = payment at the beginning of the period, 0 = payment at the end of the period, if omitted it will default to end of period
Note: the pmt and rate inputs should be aligned, meaning that if the rate is entered as an annual interest rate, then the pmt should be an annual payment.
Return value
The NPER function returns the number of periods, or amount of time, to payoff a loan or reach an investment objective with constant payments or cashflow and a fixed interest rate. The period of time (annual, monthly, etc.) is defined by the rate and pmt arguments.
For example, if the rate and pmt are annual figures, the NPER function will return a value with in the units of years.
Example
In the example above the NPER function calculates the number of periods (in years) for a loan with the terms listed in cells C5, C7, and C9. In this example, the loan amount is $1,000, at an interest rate of 6%, with an annual payment of $350.
=NPER(C5, C7, -C9, , )
Note: A minus sign “-” is entered before the pv argument (C9) because the present value is negative (a loan). If a minus sign is not entered the function will return an incorrect value.
The result returned is 3.2 years, which is the number of periods or amount of time it will take to payoff this loan.
