The inclusion of holidays is critical in calculating the “Bond Payment Dates”. There are bonds that accrue interest on the basis of Actual/360 or Actual/Actual. For example, if a bond has a payment date of 25th of each month, the accrual days for a period will be from 25th of this month to 24th of next month (The accrual end date is a day before the payment date). However, if the next payment date falls on a weekend or holiday, the next payment date will be on the day after such weekend or holiday. The number of accrual days will be extended as well to include the weekend or holiday. For example, December 25th of 2015 falls on Friday. So the payment date of a bond will be on December 28th (Monday) instead. So the interest accrual period of such bond will be from November 25th, 2015 to December 27th, 2015, which is 33 days. The next interest accrual period will be from December 28th, 2015 to January 24th, 2016, which is only 28 days. What makes it even more complicated is that we need to project such interest payments for 15 or 30 years for both waterfall and tax calculation purposes. If you don’t have a way to automate the holiday calculations, just imagine the amount of work you need to do so and if you are lucky enough to find such information that you can manually put in.
All bonds issued in the US uses the US Federal holidays schedule, at least I have not seen any bond deal uses different holiday schedule. Below is the list of the US Federal holidays. Please note that there is no US Federal holiday in a consecutive manner. Consecutive manner means two holidays adjoining each other, making them two holidays in a row.
- New Year
- Martin Luther King Day
- Presidents’ Day
- Memorial Day
- Independence Day
- Labor Day
- Columbus Day
- Veterans Day
- Thanksgiving
- Christmas
Please follows the below steps to calculate the Bond Payment Dates. The columns refers to the below illustration.
Step 1: Use the VB module that I created to generate a list of holidays. You may need to generate a holiday list with 30 years of projection. Please refer to this article (US Holidays Calculations) for more information.
Step 2: Generate the “Stated” Payment Date by using the EDATE function (Column). For example, the stated payment date will be on 25th of the month. By using the EDATE function, you can calculate the payment date on monthly, quarterly, semi-annual, or annual basis. Please refer to this article for more information (Date Functions).
Step 3: First check if the stated payment dates fall on weekends by using the WEEKDAY function (Column B). If the stated payment dates falls on Saturday, add 2 days to the stated payment dates. If the stated payment dates falls on Sunday, add 1 day to the stated payment dates. Please refer to this article for more information (Date Functions).
Step 3: Then check if the calculated date on column B is a holiday by using a combination of VLOOKUP and ISERROR functions. The VLOOKUP functions looks up the holiday list to see if the calculated date on column B is a holiday. If yes, add 1 day to the calculated dates on column B. If not, the VLOOKUP function will return an error and the ISERROR function (combined with the IF function) will not change the date on column B. Please refer to this article for more information (VLOOKUP and HLOOKUP functions).
Step 4: Check again if the calculated dates on column C fall on weekends by using the WEEKDAY function. If such dates fall on Saturday, add 2 days to the calculated dates on column C. If such dates fall on Sunday, add 1 day to the calculated dates on column C. Since the US Federal holiday will not be in a consecutive manner. There is no need to check the second time for holidays again. The calculated dates on column D will be the “Actual Payment Date. Please refer to this article for more information (Date Functions).
