Explanation
The NPER function returns the number of periods for loan or investment. You can NPER to get the number of payment periods for a loan, given the amount, the interest rate, and periodic payment amount. An annuity is a series of equal cash flows, spaced equally in time.
The goal in this example is to calculate the years required to save 100,000 by making annual payments of $5,000 where the interest rate is 5% and the starting amount is zero. Payments are made annually, at the end of each year.
To solve for periods, the NPER function is configured like this:
=NPER(C6,-C7,-C4,C5,0)
where:
- rate - from cell C6, 5%.
- pmt - from C7, $5,000 (entered as negative value)
- pv - from cell C4, 0.
- fv - from cell C5, 100000.
- type - 0, payment at end of period (regular annuity).
With this information, the NPER function returns 14.20669908 years. Note both payment and present value are entered as negative values, since these are cash outflows.
Related formulas
Future value of annuity
The FV function is a financial function that returns the future value of an investment. You can use the FV function to get the future value of an investment assuming periodic, constant payments with a constant interest rate. An annuity is a series of equal cash flows, spaced equally in time. In...
Future value vs. Present value
The FV function is a financial function that returns the future value of an investment, given periodic, constant payments with a constant interest rate. The PV function returns the present value of an investment. You can use the PV function to get the value in today's dollars of a series of future...
Present value of annuity
The PV function is a financial function that returns the present value of an investment. You can use the PV function to get the value in today's dollars of a series of future payments, assuming periodic, constant payments and a constant interest rate. An annuity is a series of equal cash flows,...
Payment for annuity
The PMT function is a financial function that returns the periodic payment for a loan. You can use the PMT function to figure out payments for a loan, given the loan amount, number of periods, and interest rate. An annuity is a series of equal cash flows, spaced equally in time. The goal in this...
Related functions
PMT Function
The Excel PMT function is a financial function that returns the periodic payment for a loan. You can use the PMT function to figure out payments for a loan, given the loan amount, number of periods, and interest rate.
PV Function
The Excel PV function is a financial function that returns the present value of an investment. You can use the PV function to get the value in today's dollars of a series of future payments, assuming periodic, constant payments and a constant interest rate.
FV Function
The Excel FV function is a financial function that returns the future value of an investment. You can use the FV function to get the future value of an investment assuming periodic, constant payments with a constant interest rate.
