Exceljet

Quick, clean, and to the point

Calculate periods for annuity

Excel formula: Calculate periods for annuity
Generic formula 
=NPER(rate,pmt,pv,fv,type)
Summary 

To calculate the number of periods needed for an annuity to reach a given future value, you can use the NPER function. In the example shown C9 contains this formula:

=NPER(C6,-C7,-C4,C5,0)
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.

Author 
Dave Bruns

Excel Formula Training

Formulas are the key to getting things done in Excel. In this accelerated training, you'll learn how to use formulas to manipulate text, work with dates and times, lookup values with VLOOKUP and INDEX & MATCH, count and sum with criteria, dynamically rank values, and create dynamic ranges. You'll also learn how to troubleshoot, trace errors, and fix problems. Instant access. See details here.

Download 200+ Excel Shortcuts

Get over 200 Excel shortcuts for Windows and Mac in one handy PDF.