The Excel COUPPCD function returns the previous coupon date before the settlement date for a coupon bond
Syntax:= COUPPCD(settlement, maturity, frequency, [basis])
The COUPPCD function syntax has the following arguments:
- Settlement Required. The security’s settlement date. The security settlement date is the date after the issue date when the security is traded to the buyer.
- Maturity Required. The security’s maturity date. The maturity date is the date when the security expires.
- Frequency Required. The number of coupon payments per year. For annual payments, frequency = 1; for semiannual, frequency = 2; for quarterly, frequency = 4.
- Basis Optional. The type of day count basis to use.
| Basis | Day count basis |
|---|---|
| 0 or omitted | US (NASD) 30/360 |
| 1 | Actual/actual |
| 2 | Actual/360 |
| 3 | Actual/365 |
| 4 | European 30/360 |
Example: Let’s look at some Excel COUPPCD function examples and explore how to use the COUPPCD function as a worksheet function in Microsoft Excel:
Syntax: =COUPPCD(B1,B2,B3,B4)
Result:
Based on the Excel spreadsheet above, the following COUPPCD examples would return:
Syntax: =COUPPCD(DATE(2019,3,25),DATE(2019,12,15),2,1)
Result: 15/12/2018
Syntax: =COUPPCD(DATE(2019,3,25),DATE(2019,12,15),B3,B4)
Result: 15/12/2018
Note:
- #NUM! error – Occurs in the following scenarios:
- When the settlement date provided is greater than or equal to (≥) the maturity date.
- When the given frequency argument provided by the user is not equal to 1, 2 or 4.
- When the given basis argument is a number other than 0, 1, 2, 3 or 4.
- #VALUE! – Occurs in the following scenarios:
- When the given settlement date or maturity date is not a valid date. Remember that we need to enter dates in date format or else use the DATE function to convert them into proper dates. The function doesn’t work when dates given are in text format. For example, =COUPPCD(“1/25/2023″,”11/15/2024”,2,1) = #VALUE.
- Any of the arguments given is non-numeric.
- The COUPPCD function truncates all arguments to integers
