Financial & Accounting
Bond Coupon Schedule & Actual/Actual Day Counts (EDATE & DATEDIF)
Derive previous coupon date (COUPPCD), next coupon date (COUPNCD), accrued days (COUPDAYBS), remaining days (COUPDAYSNC), period length (COUPDAYS), and remaining coupons (COUPNUM).
Anchor coupon schedules backward from maturity using EDATE
Fixed-income coupon dates step backward from Maturity in exact multiples of stepMo = 12 / Frequency months (6 months for semiannual, 3 for quarterly, 12 for annual). Stepping backward from Maturity by stepMo * INT(DATEDIF(Settlement, Maturity, "m") / stepMo) lands either on the coupon date on/before Settlement or one period ahead; if it exceeds Settlement, stepping back one additional -stepMo gives the exact previous coupon date COUPPCD!
Once COUPPCD and COUPNCD = EDATE(COUPPCD, stepMo) are anchored, Actual/Actual day counts follow by exact serial subtraction: COUPDAYBS = Settlement - COUPPCD, COUPDAYSNC = COUPNCD - Settlement, and COUPDAYS = COUPNCD - COUPPCD (where COUPDAYBS + COUPDAYSNC = COUPDAYS).
| Bond Schedule Metric | 2026-05-20 Settlement (2028-09-15 Mat, Freq=2) | Formula Identity |
|---|---|---|
| Previous Coupon (COUPPCD) | 2026-03-15 | Backward EDATE from Maturity |
| Next Coupon (COUPNCD) | 2026-09-15 | EDATE(COUPPCD, 12 / Freq) |
| Days Accrued (COUPDAYBS) | 66 days | Settlement - COUPPCD |
| Days to Next (COUPDAYSNC) | 118 days | COUPNCD - Settlement |
| Coupon Period (COUPDAYS) | 184 days | COUPNCD - COUPPCD (66 + 118) |
| Remaining Coupons (COUPNUM) | 5 coupons | Months(COUPPCD..Maturity) / stepMo |
Accrued Days Since Previous Coupon (Actual/Actual COUPDAYBS)
Validates Settlement B2 < Maturity B3 and Frequency B4 in {1, 2, 4}, then returns exact elapsed days since COUPPCD.
=IF(OR(B2 >= B3, AND(B4 <> 1, B4 <> 2, B4 <> 4)), "Invalid Bond Dates or Freq", LET(stepMo, 12 / B4, wholeMo, DATEDIF(B2, B3, "m"), candPrev, EDATE(B3, -stepMo * INT(wholeMo / stepMo)), prevCpn, IF(candPrev > B2, EDATE(candPrev, -stepMo), candPrev), B2 - prevCpn))Total Actual/Actual Days in Current Coupon Period (COUPDAYS) & Remaining Coupons
Computes nextCpn - prevCpn in actual calendar days.
=LET(stepMo, 12 / B4, wholeMo, DATEDIF(B2, B3, "m"), candPrev, EDATE(B3, -stepMo * INT(wholeMo / stepMo)), prevCpn, IF(candPrev > B2, EDATE(candPrev, -stepMo), candPrev), EDATE(prevCpn, stepMo) - prevCpn)Try the example
Use the original values or change them to test your understanding.