1. Calculation functions
  2. All Functions
  3. Financial functions
  4. COUPDAYSNC

Use the COUPDAYSNC function to calculate the number of coupon days from the settlement date until the next coupon date.

The number returned excludes the settlement date and includes the last day of the next coupon period.

Syntax

COUPDAYSNC(settlement, maturity, frequency[, basis])

The COUPDAYSNC function has the following arguments:

Argument Data type Description
settlement (required) Date The date the bond is traded to the buyer — the settlement date.
maturity (required) Date The bond maturity date — the date when the bond expires.
frequency (required) Number

The number of coupon payments per year.

Enter:

  • 1 for annual,
  • 2 for semi-annual, or
  • 4 for quarterly.
basis (optional) Number

The basis determines how many days exist in a year.

A full year has:

  • 360 days when basis US 30/360, Actual/360, and EUR 30/360 are used;
  • 365 days when basis Actual/365 is used; and
  • 365 or 366 days when Actual/Actual is used.

US 30/360 is the default basis for COUPDAYSNC. It can also be specified by entering 0.

To use a different type of day count basis, enter:

  • 1 for Actual/Actual,
  • 2 for  Actual/360,
  • 3 for  Actual/365, or
  • 4 for European 30/360.

Learn about the conventions used to calculate the day count for basis.
Note: When US 30/360 is used, the conventions used to calculate the days after settlement will vary depending on whether the start date and end date are independent. Where the end date is dependent on the start date, the full set of NASD day count conventions will be used.
 
Result
Number

Constraints

The COUPDAYSNC 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 30/360), 1 (Actual/Actual), 2 (Actual/360), 3 (Actual/365), or 4 (EUR 30/360).

Examples

The following tables show some example formulas using the COUPDAYSNC function.

You can reference line items or list properties in your formula.

Formula Description Result
COUPDAYSNC(DATE(2015, 1, 15), DATE(2018, 1, 31), 1, 1)

This example shows how the number of days from the settlement date until the next coupon date can be calculated when a basis is specified.

The basis is given as 1 (Actual/Actual).

The example uses:

  • a settlement date of 01/15/2015,
  • a maturity date is 01/15/2018, and
  • a frequency of 1 (annual).
 16
COUPDAYSNC(DATE(2015, 1, 15), DATE(2018, 1, 31), 4)

In this example, number of days from the settlement date until the next coupon date is calculated without specifying a basis. As a result, the basis defaults to US 30/360.

Here:

  • the settlement date is 01/15/2015,
  • the maturity date is 01/15/2018, and
  • the frequency is 4 (quarterly). 
 15

Excel equivalent