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.
A coupon bond is a bond that pays interest before maturity. Once you own the bond, you can calculate the number of coupon payments you will receive over the life of the bond by using the COUPNUM function. To use that function, you need to know the following four things. The first is the settlement date and that is the date that you buy into the investment and take possession of the bond. Then next you have the maturity date and that is the date that the investment ends, that you receive your last coupon payment and any other money due to you.
Then next you have coupon frequency and in Excel that means it can either be annual which is one coupon per year, this is an annual frequency, then two, which is semiannual, or four which is quarterly. Then the next argument is the basis and this is the way that you count the number of days in a month and a year. In North America, the standard is for a 30 day month leading to a 360 day year. That is option 0. In this case, I am using option 1, which is actual and that means you count the actual number of days in the year instead of abstracting it to a 30 day month and a 360 day year.
So, in non-leap years, February will have 28 days and in a leap year it will have 29. So, now let's go ahead and click in cell C9 and create our formula. Now, I will type an equal sign and then our function is COUPNUM( and then start filling in the arguments. So, our settlement date is in C3, comma, maturity is in C4, comma, coupon frequency C5, comma, and then the basis is in cell C6.
Now, basis is an optional argument. If you don't leave it in then Excel uses the North American standard which is a 30 day month and a 360 day year. And I will type a right parentheses to close out the formula. Make sure all the references look good and press Enter. So, when I do, I see that the number of coupons to be paid between the time that I buy into the investment which is in February of 2011. I will receive four coupons over the next four years and just from looking at the information, I can see that those will be paid on December 31 of 2011, 2012, 2013 and 2014.
So, now let's change the coupon frequency. If I were to go to semiannual, and then let's change the value to 2, see that the number of coupons is 8. And if I go back to cell C5 and type a 4 and press Enter, see that the number of coupons to be paid is 16. And if I were to change the settlement date to something after the end of the first quarter, let's do 7/ 14/2011 and press Enter, see that the number of coupons to be paid has been reduced by two because I missed the first two quarterly payments in the year 2011.
When you own a coupon bond, you will receive a payment every time a coupon comes due.
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.