Practice free

COUPNCD function

COUPNCD finds the next coupon payment date after you buy a bond. The result is a date serial number, so format the cell as a date to read it.

Example

Read it as a sentence: for a bond settled March 1, 2024 that matures January 1, 2025 and pays twice a year, find the next coupon date after settlement, using basis 0.

Practice this in the app

Syntax

COUPNCD(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: Serial date of the next coupon after settlement.

What Excel actually returns

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

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

Watch out for

  • The result shows as a plain number until you apply a date format.

Where it works

Excel
COUPNCD 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 .