COUPDAYBS Function

Returns the days from the start of the coupon period to settlement.

Category

Financial

Available in

All versions, Microsoft 365

Returns

A number of days

Want to practice COUPDAYBS in a real spreadsheet? Open a blank sheet in our Practice Sandbox and try it with live data, with the AI Excel tutor beside you. No Excel needed — just a free account.Try it free in the Sandbox

Overview

COUPDAYBS counts days from the previous coupon to settlement — the days of interest the buyer owes the seller.

The coupon functions (COUPPCD, COUPNCD, COUPNUM, COUPDAYS, COUPDAYBS, COUPDAYSNC) describe the coupon period a settlement date falls in — the building blocks of accrued interest and bond pricing.

Syntax

=COUPDAYBS(settlement, maturity, frequency, [basis])

Arguments

ArgumentRequired?What it does
settlementYesThe date the buyer takes ownership (after the issue date).
maturityYesThe date the security is repaid.
frequencyYesCoupon payments per year: 1 = annual, 2 = semiannual, 4 = quarterly.
basisNoDay count: 0 or omitted = US 30/360, 1 = actual/actual, 2 = actual/360, 3 = actual/365, 4 = European 30/360.

Examples

Example 1 — Northstar's corporate bond

Northstar's treasury buys a corporate bond: 5% coupon paid twice a year, maturing 11/15/2033, settling 3/15/2026.

B5fx=COUPDAYBS(B1,B2,B3,B4)
AB
1Settlement3/15/2026
2Maturity11/15/2033
3Frequency2
4Basis0
5Days since last coupon120

11/15/2025 to 3/15/2026 is 120 days on 30/360 (and also 120 actual days).

Common mistakes

  • #VALUE! or #NUM!. A date isn't a real date, settlement is on or after maturity, or frequency isn't 1, 2 or 4.
  • A date result shows as a number. Format the cell as Short Date.

Notes

Kyle Stott

Author

Kyle Stott

Certified Microsoft Excel Expert

Kyle has worked professionally with Microsoft Excel for over a decade and has consulted on and taught Excel best practices to over 400 companies across 30+ countries.