Easy-to-follow video tutorials help you learn software, creative, and business skills.Become a member
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.
Get unlimited access to all courses for just $25/month.Become a member
82 Video lessons · 87298 Viewers
80 Video lessons · 136349 Viewers
59 Video lessons · 55001 Viewers
52 Video lessons · 68866 Viewers
Access exercise files from a button right under the course name.
Search within course videos and transcripts, and jump right to the results.
Remove icons showing you already watched videos if you want to start over.
Make the video wide, narrow, full-screen, or pop the player out of the page into its own window.
Click on text in the transcript to jump to that spot in the video. As the video plays, the relevant spot in the transcript will be highlighted.