FAQs

Watch the Online Video Course Excel 2007: Financial Analysis

2h 18m Intermediate Aug 25, 2009

Viewers: in countries Watching now:

Applying nominal versus effective interest rates (APR versus APY)

Financial institutions like to advertise the interest rates their products return to investors. Most ads list an annual percentage rate, which you can use to calculate the yearly return using the compound interest formula you learned earlier. Some ads, however, list an annual percentage yield, which is the net return and not the actual interest rate applied to the investment. When comparing two or more investment opportunities, you must make sure that all of the investments’ terms are expressed using the same type of interest rate. Annual percentage rate versus annual percentage yield.

There are two built-in Excel functions you can use to convert annual percentage yields to annual percentage rates and vice versa. The first is the Effect or Effective function, which you can use to determine the annual interest earned in a year expressed as a percentage of the principal. You can use the Nominal function to determine an annual percentage rate when presented with an annual percentage yield. So let’s take a look at the example I have here in the worksheet. We have two investments, one of which has an annual percentage yield; the other which has an annual percentage rate.

Now, the annual percentage rate of 5% with a principal of $20 million and a term of 10 years, I already have enough information to calculate the future value of the investment. In other words, because I know the APR, the principal and the term, I can create a formula, which you see here on the Formula bar, the compound interest formula, to determine the total value of the investment. However, with the annual percentage yield, I do not have enough information to calculate the future value of the investment.

Instead, I need to determine the annual percentage rate. So to calculate the annual percentage rate, given an annual percentage yield, you use the Nominal formula and include the Effective rate, which is the APY in cell B5 and then the number of periods per year. In other words, if the interest is compounded within the year, how many times is it compounded? In this case, we are assuming that the loan is compounded monthly. So we use the value from cell B6 and Return.

So with an annual percentage yield of only 5%, the annual percentage rate is 4.89%. That leads to a future value of the investment of $32,234,973. Compare that to the future value of the loan, which has the 5% APR. The future value here is about $300,000 higher, and when you are talking in terms of a $20 million investment, $300,000 is real money. So you need to play close attention to it.

If you want to express the annual percentage rate of 5% as an APY, to compare it more directly to the APY of 5% from the first investment, you can use the Effect or Effective function. So type =EFFECT, then you input the nominal rate, which is in cell E5. Number of periods per year, which is in E6, Return and there you have an APY of 5.12%.

Every fraction of a percentage point of interest makes a difference in the value of a loan or an investment, especially when the amounts are in [00:03:14.00s] the millions of dollars. Take the time to ensure the interest rates of the investments you are considering are expressed using consistent terms.

There are currently no FAQs about Excel 2007: Financial Analysis.