COUPDAYBS Function in Google Sheets

Calculate the number of days from the beginning of the coupon period to the settlement date using the COUPDAYBS function.

COUPDAYBS Function

The COUPDAYBS function in Google Sheets calculates the number of days from the beginning of the coupon period to the settlement date.

Syntax

COUPDAYBS(settlement, maturity, frequency, [day_count_convention])
  • settlement: The security’s settlement date.
  • maturity: The security’s maturity date.
  • frequency: The number of coupon payments per year (1, 2, or 4).
  • day_count_convention: (Optional) The day count convention to use. Default is 0 (actual/actual).

Examples

  1. Basic Coupon Day Calculation

Calculate the days from the beginning of the coupon period to the settlement date:

=COUPDAYBS("1/1/2023", "1/1/2028", 2)

This will output the number of days.

  1. Calculation with Different Day Count Convention

Calculate using a 30/360 basis:

=COUPDAYBS("1/1/2023", "1/1/2028", 2, 1)

This will output the number of days based on the 30/360 basis.

Notes

  • The function is useful for bond investors to understand the timeline of coupon payments.
  • Ensure that the settlement date is before the maturity date to avoid errors.
  • COUPDAYS: Calculate the number of days in the coupon period containing the settlement date.
  • COUPNCD: Calculate the next coupon date after the settlement date.