How to Use Excel COUPDAYBS Function in Office 365? (With Examples)

excelgeek profile image Andrews Originally published at geekexcel.com on ・3 min read

Excel COUPDAYBS Function:

This article explains the definition and basic syntax of Excel COUPDAYBS Function in Office 365. Further, we have described the usage of this function with practical examples. Get an official version of Microsoft Excel from the following link: https://www.microsoft.com/en-in/microsoft-365/excel.

Definition of COUPDAYBS Function:

  • The Excel COUPDAYBS function evaluates the number of days from the beginning of a coupon period up to its settlement date.

COUPDAYBS Function – Basic Syntax:

The fundamental syntax of Excel COUPDAYBS function is,

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

Important Note:

  • Dates must be entered with the use of the DATE function. It can also be a result of other functions or formulas. If dates are entered as text, issues can occur.
  • For example, use DATE(2020,7,13) for the 13th of July 2020.

Argument Explanation:

  • Settlement (Required): It is the settlement date of the security.
  • **Maturity (Required): **It is the maturity date of the security. The maturity date is the date when the security expires.
  • Frequency (Required): Number of coupon payments per year.
    • Annual – 1
    • Semiannual – 2
    • Quarterly – 4
  • Basis (Optional): It specifies the 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 |
| 4 | European 30/360 |

Important Note:

  • #NUM! Error – Occurs if either:
    • The supplied ‘settlement’ date is greater than or equal to the ‘maturity’ date;
    • The ‘frequency’ argument is not equal to 1, 2, or 4;
    • The ‘basis’ argument is not equal to 0, 1, 2, 3, or 4.
  • #VALUE! Error – Occurs if either:
    • The supplied ‘settlement’ or ‘maturity’ dates are not valid Excel dates;
    • Any of the supplied values are non-numeric characters.

Practical Examples:

Let’s look at s0ome practical examples of COUPDAYBS function and explore how to use it in Microsoft Excel.

Example 1:

  • We have entered the input values of settlement, maturity, frequency, and basis into the cell range B5: E7.
  • Here, in this example, we have selected the input cells B5, C5, D5, and E5 to calculate the number of days.
  • Refer to the below syntax of the COUPDAYBS function to return the output.

Example 1
Example 1

  • You need to click Enter to execute the formula.

Example 1 - Output
Example 1 – Output

  • Now, you can see the result in the G5 cell.
  • You can repeat the same for other input values.
  • In another way, you can just drag the cell G5 to G7 so it automatically returns the respective output.

Example 2:

  • The #NUM! Error appears if the supplied ‘settlement’ date is greater than or equal to the ‘maturity’ date or the ‘frequency’ argument is not equal to 1, 2, or 4 or the ‘basis’ argument is not equal to 0, 1, 2, 3, or 4.

Example 2
Example 2

Example 3:

  • The function returns the #VALUE! Error if any one of the supplied values is a non-numeric character.

Example 3
Example 3


We hope that this tutorial explained to you clearly about the use of the Excel COUPDAYBS Function in Office 365 with detailed practical examples. If you have any queries/doubts , kindly share it in the below comment section. Thanks for visiting Geek Excel!! Keep Learning With Us!!

Read Ahead:


Editor guide