Check Sheet Logo
Check Sheet

COUPDAYBS

The COUPDAYBS function calculates the number of days from the beginning of the coupon period to the settlement date for a security that pays periodic interest. It is commonly used in financial calculations to determine the accrued interest between the last coupon payment and the settlement date.

Usage

Use the COUPDAYBS formula with the syntax shown below, it has 3 required parameters and 1 optional parameter:

=COUPDAYBS(settlement, maturity, frequency, [day_count_convention])
Parameters:
  1. settlement (required):
    The settlement date of the security, formatted as a date or a reference to a cell containing a date.
  2. maturity (required):
    The maturity date of the security, formatted as a date or a reference to a cell containing a date.
  3. frequency (required):
    The number of coupon payments per year, as a number or a reference to a cell containing a number.
  4. day_count_convention (optional):
    Optional. The day count convention to use in the calculation. If omitted, defaults to 0, indicating the US (NASD) 30/360 day count convention.

Examples

Here are a few example use cases that explain how to use the COUPDAYBS formula in Google Sheets.

Calculating accrued interest for a bond

If you own a bond and sell it before the next coupon payment, you are entitled to receive accrued interest for the period of time you held the bond. Use COUPDAYBS to calculate the number of days between the last coupon payment and the settlement date, then use this value along with other financial functions to calculate the accrued interest.

Calculating the next coupon payment date

Use COUPDAYBS along with other financial functions to calculate the next coupon payment date for a security.

Calculating the yield to maturity

Use COUPDAYBS along with other financial functions to calculate the yield to maturity for a security.

Common Mistakes

COUPDAYBS not working? Here are some common mistakes people make when using the COUPDAYBS Google Sheets Formula:

Incorrect date format

One common mistake is entering the dates in the wrong format. Make sure the dates are entered as a valid date format, such as YYYY-MM-DD.

Using wrong frequency

Another common mistake is selecting the wrong frequency for the coupon payments. Make sure you are using the correct frequency for the bond you are working with.

Not including settlement date

Make sure to include the settlement date as the first argument in the formula.

Not including maturity date

Make sure to include the maturity date as the second argument in the formula.

Incorrect day count convention

The optional day_count_convention argument should only be used if you need to specify a particular day count convention. Make sure you are using the correct convention for your calculations.

The following functions are similar to COUPDAYBS or are often used with it in a formula:

  • COUPDAYS

    The COUPDAYS function returns the number of days in the coupon period that contains the settlement date. This function is commonly used in financial analysis to calculate the number of days between two dates for interest rate calculations.

  • COUPNCD

    The COUPNCD function calculates the next coupon date after the settlement date for a security with periodic interest payments. It is commonly used in finance to determine the next coupon payment date for bonds and other fixed income securities.

  • COUPNUM

    The COUPNUM formula calculates the number of coupons payable between the settlement date and maturity date of a security. This formula is commonly used in financial analysis to determine the amount of interest income earned on a bond or other fixed income security.

  • COUPPCD

    The COUPPCD function calculates the previous coupon payment date for a security that pays periodic interest. It is commonly used in financial analysis to determine the last coupon payment date for a bond or other fixed income security.

Learn More

You can learn more about the COUPDAYBS Google Sheets function on Google Support.