Mobile Apps for Finance

If you are into stocks and investing I thought you might be interested in this link.
It discusses the top 17 Must Have Mobile Apps for Finance - some of the apps are free and some are pricey but a lot of interesting ones.

http://www.businessinsider.com/17-must-have-mobile-apps-for-investors-2012-8

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!