NPER - Computing Number of Periods

This is a guest post from Joe Helstrom, CPA and is an excerpt from his Ebook Excel Time Values of Money Function for the CPA


Computing Number of Periods


One of the biggest questions asked in retirement planning relates to how long funds will last. Excel can compute that, as seen in the following example:
Your client has saved $1 million and wants to retire. He intends to withdraw $70,000 per year at the end of each year. He expects his investments to earn 6% per year. How long will his retirement assets last?

We know that the client has $1 million today (PV). He intends to withdraw $70,000 (PMT) per year and his Rate is 6%. As the PMT is on an annual basis, there is no need to adjust the Rate.
Our inputs follow:

The sign convention is at work again. The PV is negative as this represents funds invested (cash outflow). The PMT is positive as this represents a cash inflow.

Click on cell B5. Click the Fx button on the top left of the Formula tab.
Choose the “Financial” category and scroll down the function list until you see NPER.

Complete the inputs with Rate as cell B3, PMT as cell B2 and PV as cell B1.

Click OK.

Your client’s investments will last 33 years in this scenario.

==============================================================

This computation can also be monthly.

Your client has saved $1 million and wants to retire. He intends to withdraw $5,500 per month . He expects his investments to earn 6% per year. How long will his retirement assets last?


Note that our compounding is now monthly. Therefore, we must adjust the interest rate to a monthly rate by dividing by 12. We must also expect our answer to be denominated in months now, rather than years.
Our inputs follow:

Click in cell B5.  Type =Nper(B3,B2,B1),  The answer is below.

Your client’s retirement assets will last 480 months.  Dividing the 480 by 12, you get approximately 40 years.