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: 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.
Dave Bruns
Hi - I’m Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.
