Excel COUPDAYS Function in Office 365 – With Examples!!

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

Excel COUPDAYS Function:


We will illustrate the definition, fundamental syntax, and usage of the Excel COUPDAYS Function in Office 365. Get an official version of Microsoft Excel from the following link: https://www.microsoft.com/en-in/microsoft-365/excel.

COUPDAYS Function – Explanation:

  • The Excel COUPDAYS function calculates the number of days in a coupon period that contains the settlement date.

Fundamental Syntax of COUPDAYS Function:

The fundamental syntax of Excel COUPDAYS function is,

=COUPDAYS(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:

Refer to the below examples of COUPDAYS function so you can 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 COUPDAYS 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


The above tutorial assisted you to learn how to use the EXCEL COUPDAYS Function in Office 365 with clear-cut practical examples. Don’t forget to share your queries/doubts in the below comment section. Thanks for visiting Geek Excel!! Keep Learning With Us!!

Read Ahead:


Editor guide