Easy-to-follow video tutorials help you learn software, creative, and business skills.Become a member
Excel has many functions that let you evaluate your investments. If you buy a bond at a set price and receive a payment when the instrument matures, you are said to be fully invested. If the bonds or prospectus doesn't list the investment's annual interest rate, you can find it by using the INTRATE function. To use the INTRATE function, you need to know the following five things. The first is the settlement date and that is the date that you take control of the investment. Then next is the maturity date and that is the last date of the bond and that is when all interests and other payments are due to you.
Next is the investment and that's simply the amount that you pay for the bond. The redemption value is the amount that you get at the maturity date and the basis is how you calculate the number of days in a month and a year. The default value for North America, which is what we are using here, is 0 and that means that you work with 30 day months and a 360 day year. There are other values that are available depending upon the basis that you're using and I'll show those to you when we create the formula. Let's do that now.
I will click in cell C11, type equal, and we're going to use the INTRATE function, so it's intrate(. Now, we can fill in the cell references for our arguments. The first is the settlement date that's in cell c4, comma, the maturity date is in c5, comma, the investment, the amount we put it is in c6, redemption value, the amount we get at the end is in c7, type a comma, and then we have the basis. So, the basis we are going to use is 0.
That's in cell C8, but we could also use actual, which would be 1, actual with a 360 day year, actual with a 365 a year, or the European standard which is also 30 day months and a 360 day year, but durations are calculated a little bit differently. So, you will have to be told which one you're going to use by your financial advisor. When in doubt, use option 0 which is the default for North America. So, the basis is in cell c8. Type a right parenthesis to close up the function. It looks good and I'll press Tab so I don't scroll down and we see that the Interest rate for this bond is 6.87%.
Now, just to show you how this calculation will change, I'm going to change the redemption value from 126,500 to 150,000 and press Enter. When I do, you see that we have an annual interest rate of 10%. One of the interesting ways that you can use the INTRATE function is to find out what interest rate you would need for your investment to double in value given a settlement date and a maturity date. And in this case, with June 1st 2011 and ending on May 30th of 2021, you would need 10%, approximately, interest rate to double the value of your investment.
Get unlimited access to all courses for just $25/month.Become a member
82 Video lessons · 98826 Viewers
80 Video lessons · 141659 Viewers
59 Video lessons · 60012 Viewers
52 Video lessons · 73179 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.