Start learning with our library of video tutorials taught by experts. Get started
In this course, author Curt Frye shows how to perform a wide range of financial calculations quickly and easily using the many financial functions found in Excel 2010. The course details dozens of functions for evaluating cash flows; calculating depreciation; determining rates of return, bond coupon dates, and security durations; and more.
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.
Find answers to the most frequently asked questions about Excel 2010: Financial Functions in Depth.
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.