Calculation of Bond Payment Dates
How to calculate bond payment dates in Excel by accounting for weekends and US Federal holidays using WEEKDAY, EDATE, VLOOKUP, and ISERROR functions.
The inclusion of holidays is critical in calculating bond payment dates. For bonds that accrue interest on Actual/360 or Actual/Actual bases, if a payment date falls on a weekend or holiday, the next payment date shifts to the following business day — which also extends the accrual period. For example, December 25th, 2015 falls on Friday, so the payment date moves to December 28th (Monday). The interest accrual period for that month becomes 33 days instead of the usual 30.
Projecting this correctly for 15 or 30 years without automation would be extremely time-consuming.
All bonds issued in the US use the US Federal holidays schedule. The holidays are:
- New Year's Day
- Martin Luther King Day
- Presidents' Day
- Memorial Day
- Independence Day
- Labor Day
- Columbus Day
- Veterans Day
- Thanksgiving
- Christmas
There are no consecutive US Federal holidays, which simplifies the adjustment logic.
Steps to Calculate Bond Payment Dates
Step 1: Use a VBA module to generate a list of US Federal holidays for the full projection period (up to 30 years).
Step 2: Generate the "Stated" payment date using the EDATE function. For example, if the stated payment date is the 25th of each month, use EDATE to calculate dates on a monthly, quarterly, semi-annual, or annual basis.
Step 3: Check if the stated payment dates fall on weekends using the WEEKDAY function. If the date falls on Saturday, add 2 days. If it falls on Sunday, add 1 day.
Step 4: Check if the adjusted date is a holiday by combining VLOOKUP and ISERROR functions. VLOOKUP looks up the holiday list to see if the date is a holiday. If yes, add 1 day. If not, VLOOKUP returns an error and the ISERROR + IF combination leaves the date unchanged.
Step 5: Check again if the second-adjusted date falls on a weekend using WEEKDAY. Since US Federal holidays never fall consecutively, there is no need to check for holidays a second time. The result is the final "Actual Payment Date."