COUPDAYSNC Function in Microsoft Excel: Syntax, Examples & Troubleshooting
Count days from Excel settlement to the next coupon with COUPDAYSNC. Learn actual versus 30/360 results, a settlement sensitivity check and invalid-input errors.
Verification status: See the evidence and test scope described in this guide. How formula examples are checked
Quick answer: COUPDAYSNC returns the day count from settlement to the next coupon date. Its result is a number of days under the chosen convention, not the date of the next coupon and not a count of remaining coupon payments.
Syntax and input contract
=COUPDAYSNC(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: remaining days before payment
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.
- In D2 enter
=COUPDAYSNC(B2,B3,B4,B5). - Format the output as Number.
- Expected result: 89.
The next coupon date is May 15, 2026. Independent actual-date subtraction =DATE(2026,5,15)-DATE(2026,2,15) gives 89: 28 days to March 15, 31 to April 15 and 30 to May 15. The function does not include the entire following period to November maturity.
Second example: use a 30/360 basis deliberately
=COUPDAYSNC(B2,B3,B4,4) is expected to return 90, since this mid-month three-month remainder is measured as three 30-day months on European 30/360. Omitted basis also gives 90 for these dates under US 30/360.
The different answer from actual/actual is expected. The basis convention belongs to the instrument’s calculation rules; changing it solely to obtain 90 would conceal the modeling assumption. The two 30/360 variants should not be treated as equivalent for all end-of-month inputs.
Advanced application: check the effect of a later settlement
In B7 enter =DATE(2026,3,15). The formula =COUPDAYSNC(B7,B3,B4,1) is expected to return 61, the actual days from March 15 to May 15. Compared with the original 89, the remaining time has fallen by 28 days.
Cross-check the original actual/actual coupon period using 92 elapsed + 89 remaining = 181 full-period days. The later settlement gives 120 + 61 = 181. This check is intentionally restricted to this ordinary actual/actual example; do not assume raw elapsed-plus-remaining calendar dates reproduce every financial basis’s full-period denominator.
For a quarterly control, =COUPDAYSNC(DATE(2026,2,15),DATE(2026,10,15),4,1) is expected to return 59, ending April 15. The maturity/frequency pair changed the schedule, even though the settlement date stayed fixed.
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 COUPDAYSNC reference, retrieved October 17, 2026.