Use the COUPDAYS function to return the number of coupon days in the coupon period that contains the settlement date.
COUPDAYS(settlement, maturity, frequency[, basis])
The COUPDAYS function has the following arguments:
|settlement (required)||Date||The date the bond is traded to the buyer — the settlement date.|
|maturity (required)||Date||The bond maturity date — that is, the date when the bond expires.|
The number of coupon payments per year.
The basis determines how many days exist in a year.
A full year has:
US 30/360 is the default basis for COUPDAYS. It can also be specified by entering 0.
To use a different type of day count basis, enter:
Learn about the conventions used to calculate the day count for basis.
The COUPDAYS function has the following constraints:
- the settlement and maturity dates must be valid dates between 01/01/1900 and 12/31/2399;
- the maturity date must be later than the settlement date;
- the frequency must be either 1 (annual), 2 (semi-annual), or 4 (quarterly); and
- the basis, when specified, must be either 0 (US (NASD) 30/360), 1 (Actual/Actual), 2 (Actual/360), 3 (Actual/365), or 4 (EUR 30/360).
The following tables show some example formulas using the COUPDAYS function.
You can reference line items or list properties in your formula.
|COUPDAYS(DATE(2015, 1, 15), DATE(2018, 1, 15), 1, 1)||
This example shows how the number of days in the coupon period that contains the settlement date can be calculated when a basis is specified.
The basis is given as (Actual/Actual).
The example uses:
|COUPDAYS(DATE(2015, 1, 15), DATE(2018, 1, 15), 4)||
In this example, the number of days in the coupon period that contains the settlement date is calculated without specifying a basis.
As a result, the basis defaults to US 30/360.