COUPNCD Function in Microsoft Excel: Syntax, Examples & Troubleshooting
Return the next Excel coupon date with COUPNCD. Set a regular payment schedule, format the date serial, compare annual and quarterly frequencies and troubleshoot errors.
Verification status: See the evidence and test scope described in this guide. How formula examples are checked
Quick answer: COUPNCD returns the next coupon date after settlement as an Excel date serial. Apply Date formatting to see the calendar date. The regular schedule is determined from maturity and the number of coupon payments per year.
Syntax and input contract
=COUPNCD(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: identify the next payment date
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
=COUPNCD(B2,B3,B4,B5)in D2. - Apply a date format such as
yyyy-mm-ddto D2. - Expected displayed date: 2026-05-15.
In Excel’s 1900 date system, the expected numeric serial for that date is 46157. A General-formatted output showing that number has not become a day count. In a workbook using a different date system the underlying serial differs, so prefer comparing the date to DATE(2026,5,15).
A worksheet control =D2=DATE(2026,5,15) is expected to return TRUE. This checks the date value without requiring a particular display style or date separator.
Second example: a quarterly schedule
=COUPNCD(DATE(2026,2,15),DATE(2026,10,15),4,1)
Expected result, formatted as a date: April 15, 2026. Starting from an October 15 maturity, quarterly dates fall on January 15, April 15, July 15 and October 15. The first of those after February settlement is April 15.
Changing just the frequency can therefore change the next payment date; it is not enough to take settlement and add six months. The maturity date anchors the regular schedule.
Advanced application: use the returned date in a planning calculation
For the original semiannual case, enter =D2-B2 in D3 and format it as Number. Expected result: 89 actual calendar days to the next payment. This subtraction is a calendar lead time, useful for a reminder or reconciliation. It is not a claim that the contractual 30/360 day count is also 89.
Move settlement to March 15, 2026 and recompute using the same maturity and frequency. The next date remains May 15, 2026, while the actual lead time becomes 61 days. The stable date and decreasing lead time are a useful pair of checks.
For an annual control, =COUPNCD(DATE(2026,2,15),DATE(2026,11,15),1,1) gives November 15, 2026. The current year has only the annual November payment in that regular schedule. This function does not fetch an issuer’s holiday-adjusted payment calendar or independently prove the real instrument follows the entered schedule.
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 COUPNCD reference, retrieved October 17, 2026.