Calculates the number of days between the last coupon payment date and the settlement date for a fixed-income security with regular interest payments.
Syntax
COUPDAYBS(Settlement; Maturity; Frequency; Basis)
Arguments
| Argument | Required | Description | Valid Values |
| Settlement | Yes | Date of ownership transfer | Valid date format |
| Maturity | Yes | Bond repayment date | Must be after Settlement |
| Frequency | Yes | Interest payments per year | 1 (annual), 2 (semi-annual), 4 (quarterly) |
| Basis | No | Day-count convention (see Table 1) | 0-4 (default=0) |
Day-Count Basis Methods (Table 1)
| Basis | Method | Description |
| 0 | 30/360 (NASD) | 30-day months, 360-day year |
| 1 | Actual/Actual | Exact calendar days |
| 2 | Actual/360 | Actual days/360-day year |
| 3 | Actual/365 | Actual days/365-day year |
| 4 | 30/360 (European) | European 30-day convention |
Requirements & Error Handling
- Dates must be valid (no time component)
- Frequency and Basis are truncated to integers
- Returns #VALUE! for invalid dates
- Returns #NUM! for:
- Invalid Frequency (≠1,2,4)
- Invalid Basis (≠0-4)
- Settlement date > Maturity date
Background
- Used to calculate accrued interest owed when bonds trade between coupon dates
- Different day-count methods yield slightly different results
- Essential for accurate bond pricing and yield calculations
Example Applications
- Accrued Interest Calculation:
Accrued Interest = (Annual Coupon Rate × Face Value) × (COUPDAYBS()/Days in Coupon Period)
- Yield Analysis:
- Used with YIELD() and PRICE() functions
- Helps determine exact holding period returns
Key Notes
- For US corporate bonds: Typically Basis=0 (30/360)
- For government bonds: Typically Basis=1 (Actual/Actual)
- Always verify Settlement < Maturity
- Combine with COUPNCD() and COUPPCD() for complete coupon date analysis