The COUPDAYSNC function returns the number of days from the settlement date to the next coupon date. 




Argument Description
settlement The security’s settlement date – the date after the issue date when the security is traded to the buyer
maturity The security’s maturity date – the date when the security expires
frequency The number of coupon payments per year. Must be one of the following:

  1 Annually
  2 Semi-Annually
  3 Quarterly
[basis] Optional. Defines the day count basis to be used in the calculation

  [basis] Day Count Basis
  0 (or omitted) US (NASD) 30/360
  1 actual/actual
  2 actual/360
  3 actual/365
  4 European 30/360

The financial day count basis rules are explained in detail on the Wikipedia Day Count Convention page

Note: The settlement and maturity dates should be input as either:

  • References to cells containing dates, or
  • Dates returned from formulas

If you attempt to input these date arguments as text, Excel may misinterpret them, due to different date systems, or date interpretation settings.

Warning: Although you can input the date arguments as date serial numbers, this is not recommended as date serial numbering does vary across different computer systems.


  A B C D
1 Data Description    
2 25-Jan-2014 Settlement date    
3 15-Nov-2014 Maturity date    
4 2 Semiannual coupon    
5 1 Actual/actual basis    
7 Formula Result Notes
8 =COUPDAYSNC(A2,A3,A4,A5) 110 The number of days from the settlement date to the next coupon date, for a bond with the above terms

Common Function Error(s)

Problem What went wrong
#VALUE! Occurs if either:

  the supplied settlement or the supplied maturity date is not a valid date
  the supplied frequency value or the supplied [basis] argument is non-numeric
#NUM! Occurs if either:

  the settlement date is ≥ maturity date
  the frequency argument is any number other than 1, 2 or 4
  the supplied [basis] argument is any number other than 0, 1, 2, 3 or 4
#NAME? Occurs when Analysis ToolPak add-in is not enabled