Determining Median and Mode

Determining the Median and Mode

Sometimes determining the average is not sufficient.
For example, if  you are analyzing departmental salaries you might want to know the median or mode of the salary levels.

=Median(data range)  internally sorts the data and  then displays the middle value. If there is an even number of values, then the two values in the middle are averaged. =Median(B2:B10)

=Mode(data range) will display the value that appears most often. =MODE(B2.B10)

This from an excerpt from my Excel CPE Course, Must Know Excel Tools and Tips for CPAs.  

RRI Time Value of Money Function

Below is an excerpt from Excel Time Value of Money Functions for CPAs, a CPE course offered by CPASelfstudy.com

New Single Sum functions in Excel 2013 

There are two new time value of money functions in Excel 2013, the RRI and PDURATION functions.  Both of these functions will only work if you have Excel 2013 or greater.  You will not be able to replicate the examples using a lower version of Excel.

The RRI function returns the equivalent interest rate for the growth of an investment.  The inputs required are the number of periods, the present value and the future value.

In this blog entry, we are only going to discuss RRI and save PDURATION for another time.

RRI Example:

As an example, let’s say you invested $100,000 for 8 years compounded annually and the investment grows to a value of $150,000.  What is the equivalent rate of return?  Click here to read the rest of the entry.

PMT function and Amortization Schedules

In this example, we are going to walk through how to do a PMT function and then create an amortization schedule to make sure that the we did the PMT function correctly. The PMT function is used by everyone - personally and professionally.  For example. if you want to figure out what your car payment or your mortgage payment is going to be - you use PMT.

Click here to continue 

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.