What Is The COUPDAYSNC Excel Function?

The COUPDAYSNC Excel function is a financial function used to calculate the number of days in a coupon period for bonds that pay interest on an annual basis. This function requires specific inputs, including settlement dates, maturity dates, frequency of coupon payments, and the basis for calculating days

In the following example, we will look into the COUPDAYSNC Excel function and how it is used. The table exhibits fraction values, fraction dollar values, and fractions.


To implement the formula, input it in cell B5, as illustrated below:


The resultant value will be promptly displayed in cell B5.

Key Takeaways
  • The COUPDAYSNC function in Excel calculates the number of days in a coupon period for bonds that pay annual interest. It needs inputs like settlement and maturity dates, coupon payment frequency, and basis for days calculation.
  • The formula for this function is =COUPDAYSNC (settlement, maturity, frequency, basis).
  • Using the COUPDAYSNCNC Excel function, financial professionals can accurately determine the exact number of days within a coupon period, which can be crucial for making informed decisions regarding bond investments and cash flow management strategies.
  • The COUPDAYSNC function provides valuable insights into bond coupon accruals.


  1. Settlement – This is the mandatory argument. This is the settlement date specific security is transferred to the buyer after a trade.
  2. Maturity – This is the mandatory argument. This is the expiration date of the security.
  3. Frequency – This is the mandatory argument. This is the frequency of coupon payments per year is a crucial factor to consider.
1Annual Payment
2Semi-annual Payment

Basis – This is the optional argument. This document specifies the day count basis that will be used.

BasisDay Count Basis
0US(NASD) 30/360
4European 30/360

How To Use COUPDAYSNCNC Function In Excel? (With Steps)

To utilize the COUPDAYSNC function in Excel, follow these steps.

#1 – Access From The Excel Ribbon

  1. Find the Cell Where the Result Will Be Displayed. To begin with, select an empty cell. Then, select the “Formulas” tab and click OK.

    Step 1(1)

  2. To unlock features, just stroll over to the menu and tap on the Financial option.

    Step 1(2)

  3. Let’s look to the drop-down menu and choose the COUPDAYSNC option.

    Step 1(3)

  4. The window known as Function Arguments opens, entering the values for the number of arguments in the “settlement,” “maturity,” “frequency,” and “basis” fields. Once you have entered the values, click on the OK button to continue.

    Step 1(4)

#2 – Enter The Worksheet Manually

Step 1: To ensure a vacant cell is assigned for the output, input the formula “=COUPDAYSNC()” into the desired cell. Alternatively, you may type “=C” and promptly double-click on the COUPDAYSNC function from the comprehensive list of suggestions offered by Excel.


Step 2: In order to achieve the desired outcome, please press the Enter key.


Example #1 – Calculate The Number Of Days From The Settlement Date Until The Next Coupon Payment

To gain an understanding of the COUPDAYSNC Excel function to calculate the number of days from the settlement date until the next coupon payment annually, let us delve into the following example. Look at the values in the table.


Let us follow the steps below.

Step 1: To start with, select cell B6 to enter the formula. Proceed by entering the COUPDAYSNC formula in the cell:



Step 2: The resulting value will be displayed in cell B6.


Example #2

To find the number of days to the next coupon payment for this bond, you would count the actual number of days from the settlement date of 15/2/2010 to the next coupon payment date, considering that the bond has a quarterly coupon frequency and a day-count basis of Actual/360.


Look at the table above. To utilize the COUPDAYSNC Excel function, please follow the steps below:

Step 1: In cell B6, enter the following formula.



Step 2: We can see the result in cell B6, as shown in the image below.


Important Things To Note

  • The #NUM! error occurs when the settlement date is after the maturity date; the user provides a frequency argument that is not 1, 2, or 4, or the basis argument is not 0, 1, 2, 3, or 4.
  • The #VALUE error occurs in the COUPDAYSNC function when the settlement date or maturity date is not a valid date or when any of the arguments provided are non-numeric.
  • In Excel, dates are treated as serial numbers. When using arguments, they are converted to whole numbers, meaning that decimals like time are disregarded.

Frequently Asked Questions (FAQs)

1. Are there any common errors while using the COUPDAYSNC function?

One common error is entering incorrect inputs, such as an inaccurate settlement date or maturity date.
• Another mistake is misunderstanding the function’s syntax and parameters, such as incorrectly specifying the frequency of coupon payments or omitting required arguments altogether.
• Additionally, failing to properly account for holidays or weekends when calculating days between two dates can also lead to inaccuracies in the output of the COUPDAYSNC function. 

2. Explain common errors with COUPDAYSNC function with example.

In the following example, we will explore the occurrence of errors in the COUPDAYSNC Excel function. The table displays fraction values, fraction dollar values, and fractions.


To make use of the formula, it’s as easy as typing it in cells B6 F6, like this:
= COUPDAYSNC(B2,B3,2,0) in cell B5, which might cause a #NUM! error because the settlement date is before the maturity date.

= COUPDAYSNC(C2,C3,2,0) in cell C5, which might cause a #VALUE error because wrong date format of settlement date.
The result right away in cells B5 and C5. This will give us some valuable insights to dig deeper and interpret the data.


3. What are the limitations of using the COUPDAYSNC Excel function?

• The COUPDAYSNC function only calculates the number of accrued days based on an actual/actual day count convention, which may not always be suitable for all types of bonds or financial instruments.
• This function does not account for any irregularities in the coupon payment schedule or any adjustments needed for different day count conventions. 
• The COUPDAYSNC function requires specific input parameters and exact formatting to ensure accurate results, making it potentially difficult to use correctly without a clear understanding of its requirements.

Download Template

This article must help us understand COUPDAYSNC’s Excel Function’s formula and examples. You can download the template here to use it instantly.

Guide to COUPDAYSNC Excel Function. Here we explain how to use COUPDAYSNC function with examples & downloadable excel template. You can learn more from the following articles –

Reader Interactions

Leave a Reply

Your email address will not be published. Required fields are marked *