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 are bonds that pay interest prior to maturity and those interest payments are paid on a regular schedule which can occur either one, two or four times a year. After you take possession of the bond, you can calculate the calendar date of your first coupon payment by using the COUP and the CD function, and that function name is short for coupon next coupon date. So, to calculate the date the next coupon payment is due, you can use the following four arguments and the first value you need to know is the settlement date and that's just the date that you buy into the investment.
The next argument is the maturity date and that is the date that the bond investment ends. So, that would probably be your last coupon payment and any other money due to you. Then we have the coupon frequency and that is the number of coupon payments for year. And again, for this function that can be either one, two, or four and then you have the basis. And basis is how you calculate the number of days in a month and a year. The default in North America, which is option zero, is for a 30 day month and a 360 day year.
In this case, we are using basis 1, which is actual so that means you count the number of days in January, the number of days in February, 28 in a regular year, 29 in a leap year and so on. With that information in place, we can create our formula to calculate the date the next coupon is due. So type =coupncd( and then the settlement is in c3, the maturity date is in c4, then type a comma, the coupon frequency is in c5, then a comma, and the basis is in c6.
And you can see the other bases that are available to you. As I said 0 is the standard used in North America for a 30 day month and a 360 day year and if you leave the basis argument blank then that's the one that Excel uses. So, to close out the formula, I will type a right parenthesis. Everything looks good, press Enter, and we see that the date of the next coupon is due is December 31 of 2011. And I will just change the coupon frequency to show you how the dates change. So, let's say that we go to a semiannual coupon.
For that we change the frequency in cell C5 to 2 and the date the next coupon is due changes to June 30th of 2011. If we were to go quarterly by typing in a 4 then the date the next coupon is due would be at the end of the first quarter, which is March 31. When you are on a coupon bond, you'll receive a payment every time the coupon comes due. Calculating the date your first payment is due will let you 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.