Practice free

COUPNUM function

COUPNUM returns how many coupons remain between settlement and maturity, rounded up to a whole number. It is useful for building a bond cash-flow schedule.

Example

Read it as a sentence: for a bond settled March 1, 2024 that matures January 1, 2025 and pays twice a year, count the coupons still to be paid, using basis 0.

Practice this in the app

Syntax

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

Arguments in [square brackets] are optional.

settlement (required)
Date the security is bought (after issue, before maturity).
maturity (required)
Date the security expires and is repaid.
frequency (required)
Coupon payments per year: 1 annual, 2 semiannual, 4 quarterly.
basis (optional)
Day-count convention: 0 or omitted = US 30/360, 1 = actual/actual, 2 = actual/360, 3 = actual/365, 4 = European 30/360.

Returns: Count of coupon payments still to come.

What Excel actually returns

Each formula below was run in desktop Excel. The result is exactly what the cell showed, errors included.

FormulaExcel showsCase
=COUPNUM(DATE(2024,2,15),DATE(2034,11,15),2,0) 22 Semiannual
=COUPNUM(DATE(2024,2,15),DATE(2034,11,15),2) 22 Basis omitted
=COUPNUM(DATE(2024,2,15),DATE(2034,11,15),1,0) 11 Frequency 1
=COUPNUM(DATE(2024,2,15),DATE(2034,11,15),4,0) 43 Frequency 4
=COUPNUM(DATE(2024,2,15),DATE(2034,11,15),2.9,0) 22 Frequency 2.9 truncates
=COUPNUM(DATE(2024,2,15),DATE(2034,11,15),2,0) 22 Basis 0
=COUPNUM(DATE(2024,2,15),DATE(2034,11,15),3,0) #NUM! Frequency 3
=COUPNUM(DATE(2024,2,15),DATE(2034,11,15),0,0) #NUM! Frequency 0

Watch out for

  • Pass dates with DATE() or cell references; text dates can misbehave and invalid dates return #VALUE!.

Where it works

Excel
COUPNUM is in every version of Excel from 2007 on.
Google Sheets
Same name, same behaviour for everyday use.

Part of Financial functions. Every result on this page was measured in desktop Excel, build 16.0.20527. Last updated .