Use the COUPDAYBS (coupon days before settlement) function to calculate the number of days from the beginning of the coupon period until its settlement date.
The number returned includes both the first day of the period and the settlement date.
COUPDAYBS(settlement, maturity, frequency[, basis])
The COUPDAYBS function has the following arguments:
|settlement (required)||Date||The bond settlement date — the date the bond is traded to the buyer.|
|maturity (required)||Date||The bond maturity date — 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 COUPDAYBS. 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 COUPDAYBS 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 COUPDAYBS function.
You can reference line items or list properties in your formula.
|COUPDAYBS(DATE(2015, 1, 15), DATE(2018, 1, 31), 1, 1)||
This example shows how the number of days before the settlement date can be calculated when a basis is specified.
The basis is given as 1 (Actual/Actual).
The example uses:
|COUPDAYBS(DATE(2015, 1, 15), DATE(2018, 1, 31), 4)||
In this example the number of days before the settlement date is calculated without specifying a basis. As a result, this defaults to US 30/360.