Quick Navigation
COUPDAYBS Function
Summary
The COUPDAYBS function calculates the number of days from the start of the coupon period to the settlement date for bonds or securities, essential for accurate interest calculations in fixed-income investments.
Syntax
COUPDAYBS(settlement, maturity, frequency, [basis])Parameters
| Parameter | Type | Required | Description |
|---|---|---|---|
| settlement | Date |
Yes | Security's settlement date (date after issue when traded to buyer) |
| maturity | Date |
Yes | Security's maturity date (when security expires) |
| frequency | Number |
Yes | Coupon payments per year (1=annual, 2=semiannual, 4=quarterly) |
| basis | Number |
No | Day count basis (0=30/360 US, 1=Actual/actual, 2=Actual/360, 3=Actual/365, 4=30/360 European) |
Using the COUPDAYBS Function
COUPDAYBS is crucial for bond traders and financial analysts calculating accrued interest. It determines how many days in the current coupon period occurred before settlement, which affects the interest payment calculation between buyer and seller.
Common COUPDAYBS Examples
Semiannual Bond Settlement Example
=COUPDAYBS(DATE(2011,1,25),DATE(2011,11,15),2,1)For a bond settling Jan 25, 2011 with Nov 15 maturity and semiannual coupons using actual/actual basis, returns 71 days from coupon period start.
Annual Coupon Calculation
=COUPDAYBS("1/15/2024","12/31/2025",1,0)Calculates days from period start to Jan 15, 2024 settlement for annual coupon bond using 30/360 US basis.
Frequently Asked Questions
Common Errors and Solutions
#VALUE!
Cause: Settlement or maturity not valid dates
Solution: Use DATE(yyyy,mm,dd) function or valid date references
#NUM!
Cause: Invalid frequency (not 1,2,4) or basis (not 0-4)
Solution: Use valid frequency (1,2,4) and basis (0-4) values
#NUM!
Cause: Settlement ≥ maturity date
Solution: Ensure settlement date is before maturity
Notes
- Excel stores dates as serial numbers (Jan 1, 1900 = 1)
- All arguments truncated to integers
- Perfect for use with COUPDAYS, COUPPCD, and other coupon functions
- Settlement is always after issue date when security trades to buyer
Compatibility
Available in: Excel 2007, Excel 2010, Excel 2013, Excel 2016, Excel 2019, Excel 2021, Microsoft 365
Not available in: Excel 2003 and earlier
Content last reviewed: December 9, 2025
Update frequency: As needed
Excel versions tested: Excel 2007+