How to Calculate Bond Payment Dates in Excel

When a bond payment date falls on a weekend or US Federal holiday, it shifts to the next business day. For bonds on an Actual/360 or Actual/Actual accrual basis, that shift matters: it extends the accrual period, changing the interest amount. Projecting this across 15 or 30 years of payments requires a formula approach, not manual adjustments.

This guide builds a full adjusted payment date calculator using EDATE, WEEKDAY, and IFNA — no VBA required.

What You Need Before You Start

Two things: a list of US Federal holidays covering your projection period, and the bond's first payment date.

The holiday table. Create a single-column range named holidays containing every US Federal holiday for the years you're projecting. The ten federal holidays are New Year's Day, Martin Luther King Day, Presidents' Day, Memorial Day, Independence Day, Labor Day, Columbus Day, Veterans Day, Thanksgiving, and Christmas. You can generate these dates manually or pull them from a reference source — the key is that they're stored as real Excel date values in a named range.

✓

The federal holiday schedule follows predictable rules (e.g., "fourth Thursday in November"). If you need to generate 30 years of dates, a lookup table built with DATE and WEEKDAY formulas is faster than typing them by hand. See the Common Use Cases section below.

The first stated payment date. This is the contractual date — before any business day adjustment. Everything else derives from it.

The Core Formula

For a bond with monthly payments, this three-step formula calculates the adjusted payment date for row n:

=LET(
  stated,    EDATE(first_payment, n - 1),
  wd_adj,    stated + IF(WEEKDAY(stated, 2) = 6, 2, IF(WEEKDAY(stated, 2) = 7, 1, 0)),
  hol_adj,   wd_adj + IF(IFNA(MATCH(wd_adj, holidays, 0), 0) > 0, 1, 0),
  final,     hol_adj + IF(WEEKDAY(hol_adj, 2) = 6, 2, IF(WEEKDAY(hol_adj, 2) = 7, 1, 0)),
  final
)

// first_payment  — the first stated payment date (unadjusted)
// n              — the payment number (1, 2, 3 ...)
// holidays       — named range of federal holiday dates

This returns the actual payment date after accounting for both weekends and holidays. The sections below explain each step.

How the Adjustment Works

Step 1: Generate the stated date with EDATE

=EDATE(first_payment, n - 1)

EDATE moves a date forward by a given number of months while preserving the day-of-month. If first_payment is January 25, 2025 and n is 3, this returns March 25, 2025. For quarterly payments, replace n - 1 with (n - 1) * 3.

Step 2: Adjust for weekends

=stated + IF(WEEKDAY(stated, 2) = 6, 2, IF(WEEKDAY(stated, 2) = 7, 1, 0))

WEEKDAY(date, 2) returns 6 for Saturday and 7 for Sunday. Saturday gets +2 days (to Monday), Sunday gets +1 day (also to Monday). Any other day is unchanged.

⚠

Always use WEEKDAY(date, 2) — the mode-2 argument makes Monday = 1 and Sunday = 7. Mode 1 (the default) makes Sunday = 1 and Saturday = 7, which requires different logic. Using the wrong mode produces silent errors.

Step 3: Check for holidays with MATCH and IFNA

=wd_adj + IF(IFNA(MATCH(wd_adj, holidays, 0), 0) > 0, 1, 0)

MATCH looks for the weekend-adjusted date in the holidays range. If it finds a match, it returns a position number (greater than 0) — meaning the date is a holiday — and adds 1 day. If MATCH returns #N/A (not a holiday), IFNA replaces that with 0 and the date stays unchanged.

Step 4: Check for weekends a second time

=hol_adj + IF(WEEKDAY(hol_adj, 2) = 6, 2, IF(WEEKDAY(hol_adj, 2) = 7, 1, 0))

Adding one day to a holiday could land on a weekend. This re-applies the same weekend check. Because no two US Federal holidays fall on consecutive days, a second holiday check isn't needed — one pass is enough.

Common Use Cases

Monthly bond payments

A bond issues on January 1, 2025 with monthly payments on the 25th. first_payment = January 25, 2025, n runs from 1 to 180 (15 years).

=LET(
  stated,  EDATE(B$1, ROW(A1) - 1),
  wd_adj,  stated + IF(WEEKDAY(stated, 2) = 6, 2, IF(WEEKDAY(stated, 2) = 7, 1, 0)),
  hol_adj, wd_adj + IF(IFNA(MATCH(wd_adj, holidays, 0), 0) > 0, 1, 0),
  final,   hol_adj + IF(WEEKDAY(hol_adj, 2) = 6, 2, IF(WEEKDAY(hol_adj, 2) = 7, 1, 0)),
  final
)

// B$1    — cell containing the first stated payment date
// ROW()  — increments n automatically as the formula fills down

Fill this formula down 180 rows for a 15-year monthly schedule.

Semi-annual bond payments

For semi-annual payments, change the EDATE offset to multiples of 6:

=LET(
  stated,  EDATE(B$1, (ROW(A1) - 1) * 6),
  wd_adj,  stated + IF(WEEKDAY(stated, 2) = 6, 2, IF(WEEKDAY(stated, 2) = 7, 1, 0)),
  hol_adj, wd_adj + IF(IFNA(MATCH(wd_adj, holidays, 0), 0) > 0, 1, 0),
  final,   hol_adj + IF(WEEKDAY(hol_adj, 2) = 6, 2, IF(WEEKDAY(hol_adj, 2) = 7, 1, 0)),
  final
)

Calculating the accrual period

The adjusted payment date also determines how many days of interest accrue. Once you have a column of adjusted dates, the accrual period for each payment is:

=adjusted_date_n - adjusted_date_(n-1)

For the first payment period, use the settlement date as the start. A 30-day month becomes 33 days when a payment shifts from Friday December 25 to Monday December 28 — and that extra interest adds up over a full bond schedule.

Generating the Holiday Table Without VBA

The holidays named range needs real date values. For fixed-date holidays (New Year's Day, Independence Day, Veterans Day, Christmas), use:

=DATE(year, 1, 1)    // New Year's Day
=DATE(year, 7, 4)    // Independence Day
=DATE(year, 11, 11)  // Veterans Day
=DATE(year, 12, 25)  // Christmas

For floating holidays (MLK Day, Presidents' Day, Memorial Day, Labor Day, Columbus Day, Thanksgiving), the pattern is "nth weekday of a month." For example, Labor Day is the first Monday in September:

=DATE(year, 9, 1) + MOD(9 - WEEKDAY(DATE(year, 9, 1)), 7)

// finds the first Monday in September of the given year

Build a table with one row per year and one column per holiday. Stack the values into a single column and name the range holidays. This approach covers any projection period without manual entry.

✓

Sort the holidays range in ascending order. MATCH with exact match (0) doesn't require sorting, but it's good practice and makes the table easier to audit.

Notes & Gotchas

Format the result as a date. LET returns a number — apply a date format (Ctrl+1) or the column will show a serial number like 46025 instead of 01/25/2025.
The holiday check only adds 1 day. The formula assumes no two consecutive business days are both holidays. This holds for US Federal holidays but would need adjustment for other holiday calendars.
EDATE preserves end-of-month dates. If first_payment is January 31, EDATE(date, 1) returns the last day of February — not February 31. For bonds with end-of-month conventions, this is correct. For others, verify your payment day stays consistent.
LET requires Excel 2021 or Microsoft 365. For older versions, nest the intermediate calculations directly or use helper columns.

⚠

If any date in the holidays range is stored as text instead of a real date value, MATCH will not find it. Dates entered as "12/25/2025" text strings look correct but won't match numeric date values. Use a formula like =ISNUMBER(A2) to verify the holiday table contains real dates.