COUPNUM Function

Counts the coupons left between settlement and maturity.

Category

Financial

Available in

All versions, Microsoft 365

Returns

A whole number

Want to practice COUPNUM in a real spreadsheet? Open a blank sheet in our Practice Sandbox and try it with live data, with the AI Excel tutor beside you. No Excel needed — just a free account.Try it free in the Sandbox

Overview

COUPNUM counts the coupon payments still to come, rounded up to whole coupons.

The coupon functions (COUPPCD, COUPNCD, COUPNUM, COUPDAYS, COUPDAYBS, COUPDAYSNC) describe the coupon period a settlement date falls in — the building blocks of accrued interest and bond pricing.

Syntax

=COUPNUM(settlement, maturity, frequency, [basis])

Arguments

ArgumentRequired?What it does
settlementYesThe date the buyer takes ownership (after the issue date).
maturityYesThe date the security is repaid.
frequencyYesCoupon payments per year: 1 = annual, 2 = semiannual, 4 = quarterly.
basisNoDay count: 0 or omitted = US 30/360, 1 = actual/actual, 2 = actual/360, 3 = actual/365, 4 = European 30/360.

Examples

Example 1 — Northstar's corporate bond

Northstar's treasury buys a corporate bond: 5% coupon paid twice a year, maturing 11/15/2033, settling 3/15/2026.

B5fx=COUPNUM(B1,B2,B3,B4)
AB
1Settlement3/15/2026
2Maturity11/15/2033
3Frequency2
4Basis0
5Coupons remaining16
6Coupons if quarterly31

From 5/15/2026 to 11/15/2033 that's 16 semiannual coupons; a quarterly bond would have 31.

Common mistakes

  • #VALUE! or #NUM!. A date isn't a real date, settlement is on or after maturity, or frequency isn't 1, 2 or 4.
  • A date result shows as a number. Format the cell as Short Date.

Notes

Kyle Stott

Author

Kyle Stott

Certified Microsoft Excel Expert

Kyle has worked professionally with Microsoft Excel for over a decade and has consulted on and taught Excel best practices to over 400 companies across 30+ countries.