The `COUPDAYS` function in Excel is used to calculate the number of days in the coupon period that contains the settlement date for a security that pays periodic interest. Here is how you can use the `COUPDAYS` function:
Syntax
COUPDAYS(settlement, maturity, frequency, [basis])
Arguments
- settlement: (required) The security’s settlement date. This is the date after the issue date when the security is traded to the buyer. Enter this as a date in Excel’s date format or use a function like `DATE(year, month, day)`.
- maturity: (required) The security’s maturity date. This is the date when the security expires and the principal and last interest payment are due. Enter this as a date in Excel’s date format or using `DATE`.
- frequency: (required) The number of coupon payments per year. Possible values:
- `1` for annual payments
- `2` for semi-annual payments
- `4` for quarterly payments
- basis: (optional) The day count basis to be used. Possible values:
- `0` or omitted for US (NASD) 30/360
- `1` for Actual/actual
- `2` for Actual/360
- `3` for Actual/365
- `4` for European 30/360
Example
Suppose you have a security with a settlement date of January 15, 2022, a maturity date of December 31, 2025, a semi-annual payment frequency, and you want to use the US 30/360 day count basis:
=COUPDAYS(DATE(2022, 1, 15), DATE(2025, 12, 31), 2, 0)
This formula will return the number of days in the coupon period containing the settlement date based on the parameters provided.
Important Notes
- Ensure that the settlement date is before the maturity date; otherwise, Excel will return an error.
- Excel dates should be entered in a format recognized by Excel or calculated using functions like `DATE`.
By understanding how each argument influences the function, you can accurately compute the coupon days for financial securities in your worksheets.
Unlock Your Potential
Excel
Access
Power BI
