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 calendar date your first coupon payment is due by using the COUPNCD function. When you own a coupon bond, you’ll receive a payment every time a coupon comes due. Calculating the date your first coupon comes due will let you manage your cash flow effectively.
- [Voiceover] A Coupon Bond houses interest paid on a regular schedule with payments occurring either one, two, or four times a year. After you take possession of the bond you can calculate the calendar date of your next coupon payment by using the Coupon CD Function. I'll show you how to make those calculations in this movie and my sample file is the Date Next Coupon Is Due 0404 file and you can find that in the Chapter Four folder of the Exercise Files Collection. To calculate the date of the next coupon payment you need to know four things.
The first is the Settlement Date and that is the date that you took possession of the security. Next is the Maturity Date, that's when the investment ends. Then Coupon Frequency, that's the number of coupons per year. In this case it can be one, two, or four. And then Basis in cell C6, which is how you look at the calendar and I'll describe those options in more detail in a moment. Let's go ahead and create the calculation. I'll click in cell C9. Type an equal sign. And my function is COUP NCD, which returns the next Coupon Date after the Settlement Date.
Type a left parenthesis and I can just enter in my arguments. In cell C3 I have the Settlement Date, which is first, followed by a comma. Next is the Maturity Date in C4 then a comma. And next is Coupon Frequency and again that's the number of payments per year one, two, or four. If you put in a three, or a zero, or a five, or any other number then you'll get an error. But in this case, we're assuming one and that's in cell C5, then a comma. And next is how we track the calendar.
Because Excel is doing the hard work of calculating interest rates for us I usually like to go ahead and click one, which is to use the actual number of days in the month, including all the exceptions for February and leap years. And an actual calendar of either 365 or 366 days. If I were doing this calculation by hand then I would definitely make simplifying assumptions, such as 30 day months and 360 day years. But, if Excel's doing the work we don't have to worry about that. So I'll click cell C6, which contains one for our actual calendar days.
Type a right parenthesis and enter. And I see that the Date The Next Coupon Is Due is on December 31st of 2016, which makes sense. If I am receiving one coupon payment per year, in which I see here in cell C5 with the number one. Then it makes sense that the coupons will be paid on the final day of that calendar year. So I have 3/15/2016, which means that my next payment will be at the end of that calendar year on December 31st.
If I change the Coupon Frequency to two, so that payments are every six months. So I'll type a two in cell C5 and press enter. The we can see that the next coupon is due at the end of the second quarter or the first half of the year. Or at least the first six months and that is June 30th. If I were to change the Coupon Frequency to four. And enter. The we see that the date changes to March 31st. So when you own a Coupon Bond, you'll receive a payment every time a coupon comes due.
Calculating the date that your next cash flow will come allows you to manage your finances 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