Excel COUPPCD Function
The COUPPCD function returns the previous coupon date before the settlement date.
The COUPPCD function returns a number that represents the previous coupon date.
- COUPPCD will return the #VALUE! error if:
- settlement or maturity is not a valid date;
- frequency or basis is not a numeric value.
- COUPPCD will return the #NUM! error if:
- frequency is not the number 1, 2, or 4;
- basis < 0 or basis > 4;
- settlement ≥ maturity.
- All arguments are truncated to integers.
To get the previous coupon date before the settlement date for a bond with the terms below, please copy or enter the formula below in a blank cell, and press Enter to get the result:
Or, use cell references to make the formula dynamic:
As you can see, the function returns a numeric value, which can be converted into a date format. Since in Excel, dates are stored as sequential numbers. By default, 1/1/1900 is serial number 1, because it is 1 day after 1/0/1900. To make the results show properly in date format, you should apply the Format Cells feature:
Select the cells where you want to apply the date format, then press Ctrl + 1. Under the tab Number in the pop-up Format Cells dialog as shown below, choose Date, and pick the type you need in the right Type section:
After you click OK, you will see the number is converted to a date.