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.

PV of Annuities using Excel

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

Present Value of Annuities


An annuity is a periodic payment of a fixed amount. The most prevalent examples are car loans and mortgages. They add the payment (Pmt) variable. The present value of an annuity deals with the value today of a future stream of fixed payments at a specific earnings rate.

Example.
Your client has just won the lottery. He must choose between $1 million paid to him immediately or $150,000 paid to him at the end of the next 10 years. He can earn 5% annually on his investments. Which option should he choose?

We have two scenarios to evaluate. The first involves $1 million today (the PV). The $1 million involves no computation. It’s already a present value. The second involves a series of periodic payments of $150,000 (Pmt) over the next 10 years (Nper) to be evaluated at an annual rate of 5%. We want to know the present value (Pv) of the annuity in the second scenario to compare to the first scenario. First, we prepare our input spreadsheet.

Click on the Fx button on the left side of the Formulas tab.  It will ask you to select a category.  Select “Financial”.  On the bottom, scroll down and choose PV.  Click OK.  The function arguments wizard will appear.  Complete the function arguments wizard as shown below.

Click OK. 

Once again, ignoring the sign convention, the present value of $150,000 per year over the next 10 years at a rate of 5% is $1,158,260.  Which is better – $1 million today or $1,158,260?  In this case, the better deal is to take the $150,000 per year periodic payments.

If you want to make the answer a positive number, place a minus sign in front of the payment (Pmt) input.  The answer is negative as Excel assumes that you would need to pay (cash outflow) this amount to obtain (cash inflow) $150,000 per year payments at a 5% rate.

AGGREGATE()

Excel 2010 introduced a couple of new functions. If you currently use the Subtotal() feature, you might be interested in a new function that was introduced in Excel 2010 - AGGREGATE().


The AGGREGATE() function is similar to the SUBTOTAL() function; however, it gives you greater control over what to ignore or what to include. It is particularly handy if you routinely have error messages in data that you are trying to manipulate. You can specify that you want the calculation to ignore hidden rows, subtotals, eror cells and or any combination of the the 3.

The syntax is =AGGREGATE(function_num,options, array, [k])

The function number, like Subtotal() 's , is a number that refers to the function you want to perform. Aggregate's function number has been expanded to 19.

Options allows you to specify what you want to ignore or exclude:

Array are the cells that you are trying to aggregate/subtotal.

Below is a simple example. For Java Joe's I have filtered all the data to just see customer "Cup of Joe"'s sales.

Now, I just want to add up Cup of Joe's Total Sales in Column F.

If I tried to use the SUBTOTAL() function I would get an error message since F13 has an error value.

 

Instead, I can use AGGREGATE() and tell it to ignore any error values as well as hidden rows. The answer of 1053.2 is returned.

New Keyboard Shortcut

I have been woefully behind on this blog due to family illness and upgrading CPASelfstudy.com so I apologize to people who have left me questions however I will get them all answered shortly.
CPAselfstudy.com will be down March 17and March 18 as the site is upgraded and shopping cart changed. I can't wait for this to be finished :)

Meanwhile - A quick tip.

Display Formulas with Keyboard Shortcut

This one was new to me and I have to thank Francis, the ExcelAddict, for it.
If you have a large spreadsheet with a lot of formulas, a really quick way to display them is to simply Press the Control key and the tilde button. The tilde button is the upper left of the keyboard and looks like this: ~

Press Ctrl and ~ together and your formulas will display. It works as a toggle.

If you have long formulas, you will need to widen the columns to see the formulas and then manually return them to their previous width.

Have a great St. Patrick's Day!

Excel Hero Academy - Starts Feb 1

I wanted to tell you about Daniel Ferry's Excel School. Daniel is a Microsoft Excel MVP and runs a really interesting Excel enthusiast blog. If you have not discovered it yet, definitely check it out. It’s called Excel Hero . What makes Daniel's blog interesting is that he does all sorts of things with Excel that no one has ever seen before. Some really fun things to be sure, but very important and useful business things as well.

Daniel started an Excel training course a few years back at the request of his readers It is called  Excel Hero Academy . The Excel Hero Academy has been extremely popular and is now in its third year. Registration for Excel Hero Academy (EHA3) opens on February 1.

Excel Hero  teaches you how Excel “thinks” and how to leverage that to be drastically more productive at work. This makes you a valuable commodity in today’s competitive workforce. I sat in for a week or two and was impressed with the caliber of the teaching as well as of the students.  I plan to sit in this session too now that I have some "free time".

The Excel Hero Academy is an extremely in depth, 16-week, video training program. If you can dedicate three to four hours a week for the next four months, the course will revolutionize the way you solve business problems. You will be transformed, literally, into an Excel Hero.  There is homework and I want to be clear -you need to have the time for this course - you just can't squeeze it in and think you can do it in ten minutes.   I will also state that I personally would only recommend this for high intermediate to advanced Excel users and those having an interest in learning more about VBA.


The entire course can now be accessed from iPhone and Android devices in addition to the normal web based academy access. EHA Mobile is distributed as a free app (for paid students of the academy) in both the iTunes App Store and the Android Market. And the academy forums now support Tapatalk as well.


Daniel is committed to continually improving the Excel Hero Academy and the results speak for themselves. Just take a moment to peruse the many dozens of student reviews on his LinkedIn Profile.

If you enroll in the class,  you will be glad that you did. And here is the icing on the cake. I have worked with Daniel and have secured a special $50 discount for you. To learn more and enroll in the course click here
Your $50 discount code: CPASELFSTUDY.

If you sign up let me know. I always appreciate feedback particularly when I am recommending something. Daniel and I are talking about ways to set up this course for CPE credit down the road so all comments etc are appreciated. I also want to let you know that I do receive a small commission for anyone who signs up.
Have a good week.