COUPDAYS Function in Microsoft Excel: Syntax, Examples & Troubleshooting
Find the length of the Excel coupon period containing settlement with COUPDAYS. Work through actual/actual and 30/360 periods, frequency changes and errors.
Verification status: See the evidence and test scope described in this guide. How formula examples are checked
Quick answer: COUPDAYS returns the length of the coupon period containing settlement under the selected day-count convention. It returns the entire period’s length, not just the days elapsed or the days still remaining.
Syntax and input contract
=COUPDAYS(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: the complete semiannual coupon 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
=COUPDAYS(B2,B3,B4,B5)in D2. - Use Number format for the result.
- Expected result: 181.
The period runs from November 15, 2025 through May 15, 2026. Independent calendar subtraction, =DATE(2026,5,15)-DATE(2025,11,15), gives 181 days. Settlement on February 15 identifies this period; it does not shorten the period to the 89 days left before May 15.
Second example: contractual versus actual period length
Use =COUPDAYS(B2,B3,B4,4) in D3. Expected result: 180, the European 30/360 length for a semiannual period. Omitting basis also gives 180 in this regular US 30/360 example.
That tenet does not mean every six-month calendar period contains 180 days. The actual/actual result was 181. Conversely, a basis labeled Actual/360 is not a guarantee that the returned complete-period denominator equals raw calendar subtraction. Do not replace a documented coupon-period function with end-minus-start for an arbitrary basis.
Advanced application: distinguish changing settlement from changing frequency
First replace the settlement input with =DATE(2026,3,15), leaving maturity and frequency unchanged. =COUPDAYS(B2,B3,2,1) remains 181, because settlement is still inside the November-to-May period.
For a separate quarterly example use:
=COUPDAYS(DATE(2026,2,15),DATE(2026,10,15),4,1)
Expected result: 90, covering January 15 through April 15, 2026. The independent month spans are 31 + 28 + 31 days. With basis 4 instead, this same quarter is also 90 convention days, an agreement specific to these dates.
When auditing a pricing workbook, retain the full-period count separately from elapsed and remaining counts. For the original actual/actual case, 92 elapsed + 89 remaining = 181 total. Dividing elapsed days by 181 represents a fraction of one semiannual coupon period; it is not automatically a fraction of a calendar year.
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 COUPDAYS reference, retrieved October 17, 2026.