COUPDAYBS Function in Microsoft Excel: Syntax, Examples & Troubleshooting
Measure days accrued in the current Excel coupon period with COUPDAYBS. Compare actual and 30/360 examples, settlement dates, frequency and documented errors.
Verification status: See the evidence and test scope described in this guide. How formula examples are checked
Quick answer: COUPDAYBS returns the day count from the beginning of the coupon period to settlement. It measures the elapsed portion of the current period under the chosen basis, not all days since the bond was originally issued.
Syntax and input contract
=COUPDAYBS(settlement, maturity, frequency, [basis])
settlement is the buyer’s settlement date; maturity is the security’s final maturity date. Both are required. They are not interchangeable with the original issue date. frequency is required and accepts 1 for annual coupons, 2 for semiannual coupons or 4 for quarterly coupons. A monthly frequency of 12 is not supported by this function.
basis is optional and controls the day-count convention:
| Basis | Convention |
|---|---|
| 0 or omitted | US (NASD) 30/360 |
| 1 | Actual/actual |
| 2 | Actual/360 |
| 3 | Actual/365 |
| 4 | European 30/360 |
Create dates with DATE or reference real Excel date values. Text such as 02/03/2026 can depend on regional interpretation. Microsoft documents truncation of all arguments to integers, including date serials; a time component is not an intraday coupon calculation.
Worked example: elapsed portion of a semiannual period
Enter this input block in A1:B5:
| Cell | Meaning | Entry |
|---|---|---|
| B2 | Settlement | =DATE(2026,2,15) |
| B3 | Maturity | =DATE(2026,11,15) |
| B4 | Coupons per year | 2 |
| B5 | Basis | 1 |
For this regular semiannual schedule, the surrounding coupon dates are November 15, 2025 and May 15, 2026; maturity follows on November 15, 2026. The settlement lies between the first pair.
- Enter
=COUPDAYBS(B2,B3,B4,B5)in D2. - Format D2 as Number rather than Date.
- The independently calculated expected result is 92.
The ordinary date difference =DATE(2026,2,15)-DATE(2025,11,15) also gives 92: 30 days to December 15, then 31 to January 15 and 31 to February 15. This arithmetic check works because the example explicitly uses actual/actual and ordinary mid-month dates. It does not establish that raw date subtraction matches every basis.
Second example: compare a 30/360 convention
Enter =COUPDAYBS(B2,B3,B4,4) in D3. Expected result: 90. Under European 30/360, this mid-month three-month interval contributes three 30-day months. With basis omitted, the US 30/360 result is also expected to be 90 for these particular dates.
The agreement of the two 30/360 results here does not make those conventions identical at month ends. Keep the selected basis visible in a model and test the security’s actual dates before relying on a boundary rule.
Advanced application: isolate a settlement-date change
Leave maturity and frequency fixed, but put =DATE(2026,3,15) in B7. =COUPDAYBS(B7,B3,B4,1) is expected to return 120. Moving settlement from February 15 to March 15 adds 28 actual days in 2026, while remaining inside the same coupon period.
A useful reconciliation uses the known full actual period of 181 days. At February 15, the elapsed and remaining pieces are 92 + 89 = 181. At March 15, they are 120 + 61 = 181. These checks make a swapped start or end date visible. The elapsed count is not a coupon payment and has no currency units until a separately justified interest calculation uses it.
Troubleshooting and boundaries
- #VALUE!: Microsoft documents this for an invalid settlement or maturity date. Inspect imported text before changing number formats.
- #NUM!: documented causes include a frequency other than 1, 2 or 4, a basis outside 0 through 4, or settlement greater than or equal to maturity.
- Wrong coupon schedule: check both maturity and frequency. These determine the regular schedule; entering the issue date as maturity can produce an error or an unrelated schedule.
- Different date systems: use DATE formulas rather than copying unexplained serial numbers between workbooks. The examples’ calendar dates are the intended inputs.
- Odd first or last periods: this guide’s examples use an ordinary regular coupon schedule. Do not infer a contractual irregular schedule from this function’s four arguments.
The basis is a contract input, not a preference chosen to make a result match. These are formula-mechanics examples, not a recommendation to buy a security or substitute for its terms.
Compatibility and verification
Microsoft’s reference checked on October 17, 2026 lists: Excel for Microsoft 365 Excel for Microsoft 365 for Mac Excel 2024 Excel 2024 for Mac Excel 2021 Excel 2021 for Mac Excel 2019 Excel 2016. This current support list does not establish the first release. The introduction version and last functioning version have not been established from the reviewed documentation. No retirement is asserted.
Example verification: Expected numerical results are independently calculated; text and shape expectations follow the documented contract. These examples have not been tested in Microsoft Excel. No screenshot or native execution is claimed.
Related guides and source
DATE in Excel | DAYS in Excel | EDATE in Excel
- Microsoft COUPDAYBS reference, retrieved October 17, 2026.