Start learning with our library of video tutorials taught by experts. Get started
Viewers: in countries Watching now:
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.
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.
Find answers to the most frequently asked questions about Excel 2010: Financial Functions in Depth.
Here are the FAQs that matched your search "":
Sorry, there are no matches for your search ""—to search again, type in another word or phrase and click search.
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.