Returns the next coupon payment date after the settlement date for a fixed-income security with regular interest payments.
Syntax
COUPNCD(Settlement; Maturity; Frequency; Basis)
Arguments
| Argument | Required | Description | Valid Values |
| Settlement | Yes | Date of ownership transfer | Valid Excel date |
| Maturity | Yes | Bond repayment date | Must be after Settlement |
| Frequency | Yes | Interest payments per year | 1 (annual), 2 (semi-annual), 4 (quarterly) |
| Basis | No | Day-count convention | 0-4 (default=0) |
Day-Count Basis Methods (Table 1)
| Basis | Method | Common Usage |
| 0 | 30/360 (NASD) | Corporate bonds |
| 1 | Actual/Actual | Government securities |
| 2 | Actual/360 | Money market instruments |
| 3 | Actual/365 | UK gilts |
| 4 | 30/360 (European) | Eurobonds |
Requirements & Error Handling
- Dates must be valid (time components are ignored)
- Frequency and Basis are converted to integers
- Returns #VALUE! for invalid date formats
- Returns #NUM! for:
- Invalid Frequency (not 1, 2, or 4)
- Invalid Basis (not 0-4)
- Settlement date ≥ Maturity date
Background
- Critical for determining future cash flows
- Used in conjunction with:
- COUPDAYSNC() (days to next coupon)
- COUPPCD() (previous coupon date)
- Different day-count methods may yield different coupon date calculations
Example Applications
Key Notes
- For US corporate bonds: Typically Basis=0
- For Treasury securities: Typically Basis=1
- Always verify Settlement precedes Maturity
- Combine with COUPNUM() for complete coupon analysis
- Critical for bond portfolio management between payment dates
Common Errors
- Using text strings instead of date serials
- Incorrect Frequency for bond type
- Mismatching Basis with market convention
Note: Refer to RATE() and YIELD() function examples for implementation scenarios.