Saturday, 10 November 2018

COUPDAYBS Finance Function in Google Sheet

How Coupon Bond Function Works in Google sheet

COUPDAYBS Function is a Finance Function in the Google sheet. This function returns the number of days from the first coupon, or the interest payment, until the settlement date of the coupon.

Coupon Bond 

Coupon Bond is a bond and it creates a debt obligation with small detachable coupons. By these coupons, the holder can claim the interest from the borrower.

The holder may or may not be the owner of the bond.

Coupon Bonds are also called Bearer Bonds. At present, these kinds of Bond are not issues because all the bonds and securities are registered in electronic format.

So, COUPDAYBS Finance Function is used to calculate the number of days from the beginning until the settlement date of the coupon.

COUPDAYBS Finance Function


COUPDAYBS(settlement, maturity, frequency, [day_count_convention])

Compulsory Parameters

Settlement: It means the once the security is traded to the buyer. 

Maturity: This the maturity date of the security.

Frequency: Number of coupon payment or interest payment per year. This is represented by 1 if the coupon payment is annual, 2 if the semi-annual,4 if the coupon payment is quarterly.

Basis: It denoted the day count method used in the formula. It may be 0,1,2,3 or 4. The criteria are given below.


How to apply the COUPDAYBS Finance Function in Google Sheet

To have a better understanding, let us consider an example 

Suppose the settlement date of a coupon bond is 31-01-2012 and the maturity date is 30-10-2013. The number of interest payment for the year is 2. The day count convention is US (NASD) 30/360.

COUPDAYBS Finance Function

Once we enter the equation as 


we can get the result as 90 days.

Important Points

Dates like Settlement Date, Maturity Date must be entered by suing DATE Function or any other DATE parsing function in Google sheet. Entering the DATE as a text may result in the error.

Errors in COUPDAYBS Finance Function

In some cases, the function may return error messages like #NUM! or #VALUE!.

#NUM! error

This type of error may happen if,

  • The Day Count Convention has entered a value other than 1,2,3 or 4.
  • The Settlement date and Maturity are same or Maturity Date is coming first.
  • The value entered for Frequency is a number other than 1,2 or 4.
In all these cases, the formula may retun #NUM! error

#VALUE! error

This type of error may retun if ,

  • The Data entered is not inputted by using DATE Function or any other date parsing function.
  • Inputting any non-numeric value in the formula

Click here to download the COUPDAYBS Finance Function file in Google sheet

Qatar Airways