March 1, 2024

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 COUPDAYS Function in Excel

Leave a Reply

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