Ready to watch this entire course?
Become a member and get unlimited access to the entire skills library of over 4,987 courses, including more Business and personalized recommendations.
Start Your Free Trial Now Overview
 Transcript
 View Offline



Financial institutions have to advertise the interest rates their products return to investors. Most ads list an annual percentage rate which you can use to calculate the year of the return by using the compound interest formula. Some ads, however, list an annual percentage yield which is the net return and not the actual interest rate applied to the investment. When you compare two or more investment opportunities, you have to make sure that all of your investments terms are expressed using the same type of interest rate. That is, an APR or an APY.
Excel has two functions that let you convert annual percentage rates which are also called nominal rates to annual percentage yields which are called effective rates. So let's say that you want to convert an effective rate or APY to a nominal rate or APR. To do that you need to have two arguments, the first is the effective rate or APY and the number of periods per year. If you look at the numbers on the left side of my worksheet here, you'll see that I have a space for APR and then the Principal and Term and all of those arguments are used here in cell B13 which calculates using the compound interest formula, the future value of a $20 million investment over the term of 10 years, with an APR that we will calculate here.
Now let's say that the APR is 5% and I'll type that into cell B8. When I type it in, we see that the future value of the investment is about $32.5 million. But now let's calculate the APR by converting this annual percentage yield to an annual percentage rate. To do that, type =NOMINAL( and we type in the cell addresses of our two arguments. We have B5 for the effective rate, type a comma, and then the number of periods per year is in cell B6, type a right parenthesis, and remembering that the future value in cell B13 is 32.5 million, press Enter and we see that the future value drops by a substantial amount because we're dealing with fairly large numbers.
Now let's do the same thing by converting an annual percentage rate to an annual percentage yield and I have my same formula set up over here and I have a future value of $32.5 million, as we did on the other side. The point of my calculation on the right side of the worksheet isn't to calculate the future value. We already know that because we have the annual percentage rate that we use in our compound interest formula. Instead what I want to do is calculate the annual percentage yield based on an APR of 5%, so that we can compare how much switching between an APY and an APR affects the numbers that are used.
So to convert we type equal and then the function is EFFECT, left parentheses, and the nominal rate or the APR is in cell E5, comma, number of periods per year is in cell E6. Everything looks right, type a right parenthesis, press Return, and we see then annual percentage rate equates to an annual percentage yield of 5.12%. Every fraction of a percentage point of interest makes a big difference in the value of a loan or an investment, especially when the numbers are in the millions of dollars.
You should use the NOMINAL and EFFECT functions to ensure the interest rates of the investments you're considering are expressed using consistent terms.






Released
6/28/2011 Analyzing loans, payments, and interest
 Discovering the interest rate of an annuity
 Determining depreciation using the straight line, declining balance, doubledeclining balance, and other methods
 Calculating the future value of an investment with variable returns
 Finding the discount rate of a security
 Converting between fractional prices and decimal prices
 Determining the yield of securities that pay interest periodically
Share this video
Embed this video
Video: EFFECT and NOMINAL: Finding nominal and effective interest rates