A coupon bond is paid on a regular schedule, either one, two, or four times a year. After you take possession of the bond, you can calculate the number of coupon payments you’ll receive from your settlement date to the bond’s maturity date. You can find that value by using the COUPNUM function. Calculating the number of coupon payment you’ll receive lets you manage your cash flow more effectively.
- [Narrator] A coupon bond pays interest or coupons on a regular schedule. Those payments can happen either one, two or four times a year. After you take possession of the bond, you can calculate the number of coupon payments you will receive between the time you take possession of the investment that is your settlement date and the time the investment ends on its maturity date. You can calculate the number of coupon payments due to you using the coupnum function. I'll demonstrate how to do that and the number of coupons 04 05 workbook which you can find in the chapter four folder of your exercise files collection.
I need to know four pieces of information. The first is a settlement date. That is the date that you actually take possession of the security. Next is the maturity date. That's the date that the investment ends. And you receive you principle in return plus any accumulated interest that wasn't covered by a coupon. Next is the coupon frequency and that is the number of times that coupon interest payments occur per year. That can be either one for annual, two for semi annual or four for quarterly.
Finally basis is how excel looks at the calendar. I'll get into more detail on that in a moment. Let's calculate the number of coupon payments that are still remaining in this investment. I'll type an equal sign and then the function coupnum left parenthesis and I can enter in my arguments. C3 has a settlement date comma, the maturity date is in C4 comma, coupon frequency which again can be one two or four is in C5. I currently have that set to one, comma.
Finally, how excel looks at the calendar. When you evaluate a bond, you should usually use the actual number of days in the month and the actual number of days in the year rather than a simplifying assumption that assigns values based on a 30 day month and a 360 day year. The reason for that is that money loses value over time due to inflation or gains value due to interest and the longer that you hold an investment, the more it will be worth in the long term. So, we'll go ahead and go with actual.
We have C6, type a right parentheses and press enter and we see the number of coupons to be paid is three. If we take a look at our settings or the arguments, then we can see why that's the case. When you're counting coupon payments, it usually makes sense to count backwards. We have our coupon frequency of one. That means that there is one coupon payment per year and that occurs on the last day of the period. If we look at the maturity date we see that it is December 31, 2018.
That means that there will be a coupon payment on that date, December 31, 2018. Also a year before that, December 31 of 2017 and one before that December 31 of 2016. The settlement date is April 17, 2016 so that's all. We see that the payments in 2018 and 2017 and 2016 correspond to the number of coupon payments here which was three. Now, let's change our coupon frequency to see how that effects the calculation.
We have a three year investment and right now we have the number of coupons to be paid at three. That's based on an annual frequency. If I change that to two, then we should see the number double to six and it does. See if you can finagle it so we're already here in C3 with that selected. The reason that's the case is that the settlement date of April 17, 2016 is before the coupon payment date for the first half of 2016 which would be June 30.
Note that it is after the first quarterly payment. So, if I were to change coupon frequency to four. Then, we should see the number of coupons paid double to twelve minus one because the first coupon date has already passed. When we press enter, we see that's the case. When you own a coupon bond, you'll receive a payment every time a coupon comes due. Calculating the number of coupon payments you'll receive lets you evaluate and manage your cash flow more effectively.
- Analyzing loans, payments, and interest
- Calculating depreciation
- Determining values and rates of return
- Calculating bond coupon dates and security durations
- Calculating security prices and yields
- Calculating prices and yields of securities with odd periods
- Analyzing simulation results