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.
Coupon bonds pay interest at regular intervals, either 1, 2 or 4 times a year. In Excel, you can use the COUPDAYSNC function to calculate the number of days from the settlement date to the next coupon date. To do that you need to know 4 items. The first item is the settlement date and that is the date that you buy into the investment. The next is the maturity date and the maturity date is the date that the bond ends, in other words that you stop making interest off of it.
Then we have the coupon frequency and that is the number of coupons per year. And as I mentioned earlier, that can either be one, which means annual payments, two, which means semi-annual payments, or four, which is quarterly payments. And then you have the basis. Basis is how you count the days in a month and a year. In North America the standard is for a 30-day month and with 12 months a 360 day a year. That's option 0. In this case I'm using a basis of 1, which is actual. That means that it counts the number of days that are actually in a month and a year.
So in a non-leap year you'd have 31 days in January, 28 in February and so on. In a leap year, you'd have 31 in January again, but then 29 in February. So in this case, I'm assuming that we're counting based on actual days. So now I click in cell C9 and create my formula. So I type an equal sign and then again the function we're using is coupdaysnc, then a left parenthesis, and we can start filling in the cells that have our arguments.
So the settlement date is in C3, then a comma. Maturity date is C4, comma. Then the coupon frequency is in C5, then a comma, and our basis is in C6. And you can see a list in the formula auto complete list of the other basis that you can use. If you leave this argument blank then it uses 0, which is the 30 day month and 360 day standard in North America. So I will type a right parenthesis to close out the formula and just a quick check. Make sure everything looks right. It does, then I press Enter.
And when I do, I see the based on a settlement date of 3/15/2011 the next coupon payment will occur in 291 days and because those coupons are coming annually that means that would be 12/31/2011. So now let's see what happens when I change the coupon frequency. Let's go to semi-annual. So I'll type in a 2, which means that the payment will occur on 1/30/2011, and we see that it's 107 days to that coupon. If we're to get quarterly, which means it would come at the end of March, so we changed it to 4, press Return and we see that we have 16 days to the next coupon.
Again that happens on March 31 and we can see that with the settlement date of March 15 that value is correct. When you own a coupon bond you'll receive a payment every time a coupon comes due. If you know how many days it will be before you get paid, you can manage your cash flow more effectively.
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.