How to use COUPDAYSNC Function in Excel

The COUPDAYSNC Function is categorized under Excel Financial functions. It helps calculate the number of days from the settlement date to the next coupon rate.As we are aware, coupon bonds pay interest at regular intervals. MS Excel introduced the COUPDAYSYNC function to calculate the days before we get paid. Thus, it allows us to manage cash flows in an efficient manner.

Syntax:= COUPDAYSNC(settlement, maturity, frequency, [basis])

The COUPDAYSNC function syntax has the following arguments:

  • Settlement    Required. The security’s settlement date. The security settlement date is the date after the issue date when the security is traded to the buyer.
  • Maturity    Required. The security’s maturity date. The maturity date is the date when the security expires.
  • Frequency    Required. The number of coupon payments per year. For annual payments, frequency = 1; for semiannual, frequency = 2; for quarterly, frequency = 4.
  • Basis    Optional. The type of day count basis to use.
Basis Day count basis
0 or omitted US (NASD) 30/360
1 Actual/actual
2 Actual/360
3 Actual/365
4 European 30/360

Example: Let’s look at some Excel COUPDAYSNC function examples and explore how to use the COUPDAYSNC function as a worksheet function in Microsoft Excel:

COUPDAYSNC Function - How to use COUPDAYSNC Function in Excel

Syntax:  =COUPDAYSNC(B1,B2,B3,B4)

Result:

COUPDAYSNC Function in Excel - How to use COUPDAYSNC Function in Excel

Based on the Excel spreadsheet above, the following COUPDAYSNC examples would return:

Syntax: =COUPDAYSNC(DATE(2019,3,25),DATE(2019,12,15),2,1)
Result: 82

Syntax: =COUPDAYSNC(DATE(2019,3,25),DATE(2019,12,15),B3,B4)
Result: 82

Note:

  • In Excel, dates are serial numbers.
  • All arguments are truncated to integers, so for example, time is ignored.
  • If settlement or maturity dates are not valid, COUPDAYSNC returns #VALUE!
  • If basis is out-of-range , COUPDAYSNC returns #NUM!
  • If maturity date is not later than settlement date, COUPDAYSNC returns #NUM!

Add a Comment

Your email address will not be published. Required fields are marked *