Easy-to-follow video tutorials help you learn software, creative, and business skills.Become a member
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.
Get unlimited access to all courses for just $25/month.Become a member
82 Video lessons · 69987 Viewers
80 Video lessons · 127400 Viewers
52 Video lessons · 62348 Viewers
59 Video lessons · 48051 Viewers
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.
Your file was successfully uploaded.