Quick Navigation
COUPNUM Function
Summary
The COUPNUM function calculates the total number of coupon payments remaining between a bond's settlement date and maturity date, automatically rounding up to the next whole coupon period. Essential for bond traders and financial analysts tracking fixed-income securities.
Syntax
COUPNUM(settlement, maturity, frequency, [basis])Parameters
| Parameter | Type | Required | Description |
|---|---|---|---|
| settlement | Date |
Yes | Security settlement date (when bond is traded to buyer) |
| maturity | Date |
Yes | Security maturity date (when bond expires) |
| frequency | Number |
Yes | Coupon payments per year: 1=annual, 2=semi-annual, 4=quarterly |
| basis | Number |
No | Day count basis (0=30/360 US, 1=Actual/actual, 2=Actual/360, 3=Actual/365, 4=30/360 European) |
Using the COUPNUM Function
COUPNUM is vital for bond valuation and cash flow analysis. Use it to determine how many interest payments remain on a bond from purchase date through maturity, accounting for the specific payment schedule and day count method used by the security.
Common COUPNUM Examples
Semiannual Bond Coupon Count
=COUPNUM(DATE(2024,1,25),DATE(2025,11,15),2,1)Returns 4 - counts 4 semiannual coupons from Jan 2024 to Nov 2025 using actual/actual basis
Quarterly Corporate Bond
=COUPNUM(A2,B2,4,0)Calculates quarterly coupons using standard 30/360 US convention
Annual Government Bond
=COUPNUM("1/15/2024","12/31/2030",1,4)Annual payments using European 30/360 convention
Frequently Asked Questions
Common Errors and Solutions
#NUM! error
Cause: Invalid frequency (not 1,2,4) or basis (not 0-4)
Solution: Verify frequency=1,2,4 and basis=0-4
#VALUE! error
Cause: Settlement or maturity not recognized as valid dates
Solution: Use DATE(year,month,day) function for dates
#NUM! error
Cause: Settlement date >= maturity date
Solution: Ensure settlement is before maturity
Notes
- Always use DATE() function for dates to prevent parsing errors
- Excel stores dates as serial numbers (Jan 1, 1900 = 1)
- Settlement is purchase date, not issue date
- Function truncates all arguments to integers
Compatibility
Available in: Excel 2007, Excel 2010, Excel 2013, Excel 2016, Excel 2019, Excel 2021, Excel for Microsoft 365, Excel for Mac, Excel Online
Not available in: Excel 2003, Excel XP, Excel 2000, Excel 97
Content last reviewed: December 9, 2025
Update frequency: As needed
Excel versions tested: Excel 2007+