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.
Comparing coupon bonds with different investment periods can be difficult. However, there is a method that you can use to calculate a common value that let's you evaluate a bond based on its sensitivity to changes in its yield. In Excel, you implement that method using the DURATION function. To calculate duration, you need to know the following six things. The first of those is the settlement date and that is the date that you purchase and gain control of the investment. Second is the maturity date and that is the date that the bond investment ends and you are paid any proceeds due to you.
Then next is the coupon interest. A coupon bond pays interest prior to the bond's maturity date. So, you can receive interest payments per coupon either annually, which is once a year, semiannually, which is twice a year, or quarterly, which is four times a year. Next is the yield and the yield is the interest on the bond itself. So, as distinct from the coupon interest, the yield is the interest that is used to calculate the final value of the bond at its maturity.
Then next you have frequency and frequency is the number of coupons per year, again 1, 2 or 4. And then finally we have basis and basis refers to the way that you calculate the days in a month and days in the year. If we were to set this value to 0 or leave it blank, the default is to use 30 day months which lead to a 360 day year. In this case, we are going to use option 1 and that is actual days. So, in a non-leap year, February will have 28 days while in a leap year it would have 29 and the year length would be 365 and 366 respectively.
With that information in hand, we can now calculate the duration and that would be equal and I am typing in cell C11, so that would be equal =duration( and then we can start filling in the cell references for our arguments. So, I have a settlement in C3. That's the settlement date, then a comma, maturity date is C4, comma, comma, oupon interest is in C5, comma, the yield is C6, comma, frequency is again the number of coupons per year, that's in C7, then a comma, and the basis is in C8.
Type a right parentheses and make sure all of our references look good. They do. So, I'll press Enter and we see that the bond has a duration of 1.74. Now that value doesn't mean much unless we have something to compare it to. So, remembering the value 1.74, I'm going to change the maturity date from 12/31/2012 to 21/31/2041. So, I will make it a 30 year investment. So, for that, 12/31/2041 and press Enter.
When I do, you see that the duration changes to 14.3. Now, let's change the yield with the same settlement date and maturity date to 8%. So, remembering the duration is 14.3, if we change the yield in cell C6 to 8% and press Enter, you see that the duration actually goes down to 13.2. When you make a long-term investment in a coupon bond, you will find at the yield in essence determines the value of the bond. You can use the DURATION function to discover how sensitive your investment is to changes in its yield.
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.