COUPPCD function
COUPPCD finds the coupon date that came before the settlement date. The result is a date serial number, so apply a date format 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 coupon date just before settlement, using basis 0.
A
B
C
D
1
45292
2
3
Syntax
COUPPCD(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 last coupon on or before settlement.
What Excel actually returns
Each formula below was run in desktop Excel. The result is exactly what the cell showed, errors included.
| Formula | Excel shows | Case |
|---|---|---|
=COUPPCD(DATE(2024,2,15),DATE(2034,11,15),2,0) | 45245 | Semiannual |
=COUPPCD(DATE(2024,2,15),DATE(2034,11,15),2) | 45245 | Basis omitted |
=COUPPCD(DATE(2024,2,15),DATE(2034,11,15),1,0) | 45245 | Frequency 1 |
=COUPPCD(DATE(2024,2,15),DATE(2034,11,15),4,0) | 45337 | Frequency 4 |
=COUPPCD(DATE(2024,2,15),DATE(2034,11,15),2.9,0) | 45245 | Frequency 2.9 truncates |
=COUPPCD(DATE(2024,2,15),DATE(2034,11,15),2,0) | 45245 | Basis 0 |
=COUPPCD(DATE(2024,2,15),DATE(2034,11,15),3,0) | #NUM! | Frequency 3 |
=COUPPCD(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
- COUPPCD 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 .