How to use the COUPDAYSNC Function in Excel

In this article, we will learn about how to use the COUPDAYSNC function in Excel.

COUPDAYSNC function returns the number of days from the settlement date to the next coupon date. This happens after when the security is traded to the buyer. The formula takes input arguments which are explained as shown below.

settlement date : security settlement date after the issue date when the security is traded to the buyer.

maturity date : first period end date

Frequency : number of payments per year (annual = 1, semi annual = 2, quarterly = 4)


Basis Day count basis
0 US (NASD) 30 / 360
1 actual / actual 
2 actual / 360
3 actual / 365
4 European 30 / 360

The COUPDAYSNC function gives the number of days to the next coupon date. that includes the settlement date and takes input as argument.


=COUPDAYSNC(date_settlement, date_maturity, frequency, [basis])


All of these might be confusing to understand. So, let's test this formula via running it on the example shown below. Here we will perform the COUPDAYSNC function over values and significance with the numbers.

Use the formula:

= COUPDAYSNC( A2 ,A3 ,A4 ,A5 )


A2 : settlement date in A2 cell

A3 : maturity date in A3 cell

A4 : Frequency in A4 cell

A5 : [basis] value in A5 cell

Here all the arguments to the function are given as cell references. Now press Enter to get the days.

The function returns 119 days to the next coupon date after when the security is traded to the buyer. Usually problems occur when the date used for settlement date and Maturity date is not in valid format.

Use the DATE function with COUPDAYSNC function to give a date argument.

Use the formula:

=COUPDAYSNC(DATE ( 2019 ,3 ,1 ),DATE (2025 ,12 ,31 ) ,A4 ,A5)

As you can see the COUPDAYSNC function when used with the DATE function in a formula returns the same result.

Here are some observational notes using the COUPDAYSNC function shown below.


  1. Excel stores dates as serial numbers and is used in calculation by the function. So it’s recommended to use dates as cell reference instead of giving direct argument to the function.
  2. settlement date, Maturity date & basis are truncated to integers.
  3. The function returns the #VALUE! Error if settlement date and Maturity date is not a valid date format.
  4. The function returns the #NUM! Error if: 
    1. frequency can take integer values and cannot be less than 1.


Related Articles







Popular Articles:





Leave a Reply


Terms and Conditions of use


Visit Us On TwitterVisit Us On FacebookVisit Us On Youtube