COUPNUM Function in Microsoft Excel: Syntax, Examples & Troubleshooting
Count remaining regular Excel coupon payments with COUPNUM. Understand rounding to whole coupons, frequency changes, final-period counts and documented date errors.
Verification status: See the evidence and test scope described in this guide. How formula examples are checked
Quick answer: COUPNUM counts coupons payable between settlement and maturity, rounding up to a whole coupon. It is a count of remaining scheduled payments, not a number of calendar years or a fraction of an annual coupon.
Syntax and input contract
=COUPNUM(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: two remaining semiannual payments
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
=COUPNUM(B2,B3,B4,B5)in D2. - Format the result as Number with no decimal places.
- Expected result: 2.
The remaining regular payment dates are May 15 and November 15, 2026. Settlement is already partway through the period ending in May, but the next payment still counts as one whole coupon. The function does not prorate that count to reflect 89 days remaining in the first period.
Second example: less than one period is still one coupon
=COUPNUM(DATE(2026,10,15),DATE(2026,11,15),2,1)
Expected result: 1, because the November maturity coupon remains. Dividing the remaining month by six to obtain one-sixth would answer a different question. Settlement exactly equal to maturity is a documented error, so do not extend this observation to a zero-length interval.
Advanced application: reconcile a cash-flow schedule
Use =COUPNUM(DATE(2026,2,15),DATE(2027,11,15),2,1). Expected result: 4. List the regular remaining dates as May 15 and November 15 in 2026, then May 15 and November 15 in 2027. The schedule contains four dates, independently checking the result.
If a hypothetical fixed coupon amount is 30 per payment, multiplying this count by 30 gives 120 of undiscounted coupon cash flows. That arithmetic deliberately excludes redemption, accrued interest and present-value discounting. It is a schedule completeness check, not the bond’s price.
For a different quarterly schedule, =COUPNUM(DATE(2026,2,15),DATE(2027,10,15),4,1) is expected to give 7: April, July and October 2026, followed by January, April, July and October 2027. A simple year difference times frequency would miss the partial first year unless it applied the same schedule and rounding rule.
Keep the actual list of cash-flow dates alongside a model when irregular terms or adjusted payment dates matter. A count alone cannot show which dates are included, and this function has no argument for a custom payment-date list.
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 COUPNUM reference, retrieved October 17, 2026.