COUPDAYBS Excel Formula

Author Zaheer    Category Formulas     Tags ,

Microsoft Excel is one of the popular software programs created by the Microsoft Company. This software features lots of powerful built-in functions that can make your life easier when you are working with complicated tasks. Excel is increasingly becoming popular and in fact, it is one of the essential components in business. It incorporates functions that can be used to present sales, financial as well as accounting reports. These functions have the ability to perform calculation necessary in the field of finance and accounting.

One of the accounting functions that are included in Excel is the COUPDAYBS function. This article will discuss a brief overview of this function and will include an example that will help you to learn about its usage, so read on.

Description of the COUPDAYBS Function in Excel

The COUPDAYBS function in Microsoft Excel is used to calculate for the number of days between the date when the coupon period starts and the date of settlement. The formula of the COUPDAYBS function is:

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

The arguments in this formula are the following:

  • Settlement – This argument is required and it refers to the date at which the security is purchased by the user or sold to the buyer.
  • Maturity – This is the date of expiration of the security.
  • Frequency – This argument refers to the number of coupon payments per year. The possible values of this argument are the following:
    • Annually
    • Semi-Annually
    • Quarterly
  • [basis] – This argument is optional. It indicates the day count basis that will be used in the computation. The possible values of this argument are the following:

0 (or omitted) – US (NASD) 30/360

  •          actual/actual
  •          actual/360
  •          actual/365
  •          European 30/360

The maturity and settlement dates can be supplied as either of the following:

  • Cell references which contain the dates
  • Dates that are returned from other functions or formulas

Common Errors in the COUPDAYBS Function

If the dates that are supplied are not valid, the function displays a result which is #VALUE!.

If the supplied [basis] or frequency argument is a non-numeric value, the function returns the result which is #VALUE!.

The allowed values for frequency are only 4, 2 and 1. If you have supplied other values than those mentioned, the function will return a #NUM! error value.

If the basis argument is less than zero or greater than 4, the function will return a #NUM! error value.

If the Analysis ToolPak is not activated in your Excel spreadsheet, the function will return a #NAME! error value. You are required to activate the add-in before you can use the COUPDAYBS function in Excel.

Example in Using the COUPDAYBS Function

As an example, the security has the maturity date of 25-Oct-2012, the date of settlement is 01-Jan-2011 and 4 payments are made per year. The day count basis that is used is the US (NASD) 30/360. Let’s say that the settlement date is contained in cell A1 and the maturity date is contained in cell A2.

Then you can use this formula, =COUPDAYBS(A1, A2, 4). After using the formula and supplying all the values, the function will return the result which is 76.

You have to take note that it is advised by Microsoft to input the date arguments as cell references which contain the dates as you can see in the given example.

It is also shown in the above example the [basis] argument is omitted. Since it is omitted, the function will automatically use the default value of 0 which indicates the US (NADS) 30/360 day count basis.

Post comment