How to use COUPDAYBS Function in Excel

The Excel COUPDAYBS function returns the number of days from the beginning of the coupon period to the settlement date

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

The COUPDAYBS 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

European 30/360

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

COUPDAYBS Function 1 - How to use COUPDAYBS Function in Excel

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


COUPDAYBS Function in Excel - How to use COUPDAYBS Function in Excel

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

Syntax: =COUPDAYBS(DATE(2019,3,25),DATE(2019,12,15),2,1)
Result: 100

Syntax: =COUPDAYBS(DATE(2019,3,25),DATE(2019,12,15),B3,B4)
Result: 100


  • 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, COUPDAYBS returns #VALUE!
  • If basis is out-of-range , COUPDAYBS returns #NUM!
  • If maturity date is not later than settlement date, COUPDAYBS returns #NUM!
READ:  How to use PRICE Function in Excel

Add a Comment

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