Start learning with our library of video tutorials taught by experts. Get started
Viewed by members. in countries. members currently watching.
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.
In the previous movie, I showed you how to evaluate a bond by using the DURATION function. Another way to evaluate a bond is to use the Modified Duration or MDURATION function. The MDURATION function is more conservative than the straight DURATION calculation because it is less sensitive to changes in yield. To use the MDURATION function, you need to know the exact same information that you do for the DURATION function. So, you start with the settlement date and that is the date that you take control of the investment. Then you have the maturity date and that is the date that the investment comes to an end and you're paid all proceeds due to you.
Next is the coupon interest argument and that value represents the amount of interest you get per coupon. Then you have yield, which is the annual percentage yield for the investment, the amount that increases per year, then the frequency and this is the number of coupons that you receive per year. That value can either be 1 for annual coupons, 2 for semiannual, or 4 for quarterly. And then finally there is basis. Basis refers to how you calculate the number of days in a month and in year. Option 0, which is the default and is the North American standard, assumes a 30 day month for every month and then a 360 day year.
In this case, we are going to use and basis 1 and that is actual. So, in the leap year you'll have 366 days and in a regular year 365. So, now let's go ahead with that information and create our formula. So, I'll click in cell C10, type =mduration, then a left parentheses and I can start adding the cell references. The first is the settlement date. That's in cell c3, comma, maturity date is in c4, the comma, coupon is in c5, comma, the yield is c6, comma, frequency c7, comma, and the basis is in c8, and then a right parenthesis to close out the entry.
Check to make sure all my references are right. Everything seems to line up so I will press Enter. And see that the modified duration is 1.64. Now, just to see how the duration changes as the parameters of the investment change, I'm going to change the yield to 12%. So I am clicking cell C6, type 12 and press Enter. When we do, the modified duration goes down to 1.57, so it's a very small change. Now, I'm going to press Ctrl+Z to undo that change and I'm going to change the maturity date to 12/31/2041 so to make it a much longer investment.
So, I will edit the value in cell C4 so it reads 12/31/2041 and press Enter. And when I do, we see that the modified duration goes up quite a bit to 13.73. Now, if I were to change the Yield to 10% and press Enter, so I just change the value in C6 to 10%, press Enter, we see that the duration goes down to 10.65. When you evaluate coupon bonds, you should strongly consider using both the DURATION and MDURATION functions to measure the investment using multiple tools.
Be sure though that you're comparing results of the same function together and don't cross them, because they use two different methods.
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.