COUPNCD in Google Sheets

What Is COUPNCD Function in Google Sheets?

The COUPNCD function in Google Sheets calculates the next coupon date for a bond or security that pays interest periodically after a given settlement date. Here, the settlement date is the date when the buyer officially purchases the bond and is important because bond pricing and accrued interest are based on the settlement date. It is helpful in financial calculations where one must know when the next interest payment will occur. This function considers the details such as the bond’s maturity date, payment frequency, and day count basis.

To understand it better, let us suppose a bond is settled on January 15, 2024. It matures on December 31, 2024, and pays interest semi-annually. We use the COUPNCD in Google Sheets formula as follows:

=COUPNCD(DATE(2024,1,15), DATE(2024,12,31), 2, 0).

This will return the next coupon date after the settlement. The result here would be June 30, 2024.

COUPNCD in Excel returns the next coupon payment date after the settlement date for a bond.
The function requires a settlement date, maturity date, payment frequency, and an optional basis for day count convention.
It helps investors track upcoming interest payments and plan cash flow accordingly.
Using the DATE function for dates inside COUPNCD improves accuracy and avoids errors.
COUPNCD differs from COUPPCD, which returns the previous coupon date, making both useful for different bond analysis needs.

Syntax

Before going into the function’s study, let us understand its basic syntax. This useful function which gives the next payment date, requires the following arguments.

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

settlement is the date when the purchaser buys the bond.
maturity is the date when the bond ends or matures.
frequency means how often the bond pays interest yearly.

We use 1 for yearly, 2 for every 6 months (semi-annual), or 4 for every 3 months (quarterly).

basis – this is an optional argument. It tells how to count the days between dates.

For example, 0 means 30-day months and 360-day years, which is common in finance.

To use a different type of day count basis, we may enter the following:

1 – Actual/Actual

2 – Actual/360

3 – Actual/365

4 – European 30/360

This function returns the date of the next interest payment after the settlement date.

How To Use COUPNCD Function in Google Sheets?

#1 – How to Use COUPNCD in Google Sheets

The COUPNCD function in Google Sheets is a useful financial function that helps find the next coupon date after the settlement date of a bond. It is very important for investors and analysts who need to track bond interest schedules and plan cash flows accurately.

We use COUPNCD in Google Sheets in two ways:

Manually entering the function.
Selecting it through the Google Sheets Menu bar.

#2 – Entering the COUPNCD Function Manually

The formula in this case will be entered manually after entering the details. To understand this, let us look at a simple step-by-step example. A person has purchased a bond on March 1, 2025. It will mature on December 31, 2026. It pays interest twice a year (semi-annually) and uses the default day count method. Let us calculate the next coupon date for the following using COUPNCD in Google Sheets.

Step 1: Enter all the above details in a sheet as shown below.

Step 2: Click on an empty cell where you want the result. Here, we choose B5. Type the following by opening the braces.

=COUPNCD(

Step 3: Inside the parentheses, enter the arguments one by one in the order specified in the syntax. Each argument is separated by a comma.

We use the DATE function for the dates to make the given dates compatible for use in the formula. After entering the arguments, close the parentheses and press Enter.

=COUPNCD(DATE(2025,3,1), DATE(2026,12,31), 2)\

Step 4: You’ll see the next coupon date after March 1, 2025, based on the bond’s interest payment schedule.

#3 – Entering COUPNCD Through the Menu Bar

The formula can be entered directly using the Google menu bar.

Click on any cell, go to the top menu, and click on Insert → Function → Financial.
Scroll and select COUPNCD from the list.
Enter the required arguments, close the parentheses, and press Enter.

Examples

Did you know that COUPNCD stands for Coupon Next Coupon Date? Let us look at some simple examples to understand this function.

Example #1 – Calculate the Previous Coupon Payment Date for a Bond

A buyer has purchased a bond on Jan 10, 2022. The bond matures on December 31, 2025, and pays interest twice a year (semi-annually). We wish to find out the next coupon payment date after the purchase using the function COUPNCD in Google Sheets.

Step 1: Open a Google Sheet and enter the bond details as shown below:

Settlement Date: January 10, 2022
Maturity Date: December 31, 2025
Frequency: 2 (for semiannual)

Step 2: Click on an empty cell where you want the result. Here, we choose cell B5.

Step 3: Type the COUPNCD function using the DATE function for the dates:

=COUPNCD(B1,B2,B3,B4).

We use the DATE function in Google Sheets so that the dates are not a string and in the right format. However, here we have verified that the dates are in the correct format.

Step 4: Press Enter. Google Sheets will return the next coupon date after January 10, 2022. This tells you when the bond will next pay interest.

Example #2 – Determine the Accrued Interest For a Bond

In this example, a buyer buys a bond on June 20, 2022, which matures on July 20, 2026. The bond pays interest quarterly (4 times a year), and the annual coupon rate is 6% on a face value of $2,000. You want to find out how much interest has built up since the last coupon date, up to your purchase date.

Step-by-step solution in Google Sheets:

Step 1: Note the key bond details in Google Sheets as shown below.

Settlement Date: June 20, 2022
Maturity Date: July 20, 2026
Frequency: 4 (quarterly)
Coupon Rate: 6%
Face Value: $2,000

Step 2: Use the COUPPCD function to find the last coupon date before settlement:
=COUPPCD(DATE(2022,6,20), DATE(2026,7,20), 4)

Step 3: Use the COUPDAYS function to find the number of days in the current coupon period:
=COUPDAYS(DATE(2022,6,20), DATE(2026,7,20), 4)

Step 4: Use the COUPDAYSNC function to get days from settlement to next coupon date:
=COUPDAYSNC(DATE(2022,6,20), DATE(2026,7,20), 4)

Step 5: Calculate accrued interest using the following formula:

= (2000 * 6% / 4) * (1 – COUPDAYSNC(…) / COUPDAYS(…))
Replace the COUPDAYSNC(…) and COUPDAYS(…) with the values from steps 3 and 4.

Step 6: The result will show the amount of interest that has accumulated between the last payment and the bond purchase date.

Example #3 – Forecast Future Coupon Payment Dates for a Bond

Suppose we hold a bond that was purchased on January 15, 2024, and it matures on January 15, 2026. The bond pays interest semi-annually (twice a year). Let us try to forecast the upcoming coupon payment dates over the bond’s life.

Step 1: Enter these details in a sheet as shown below.

Settlement Date: January 15, 2024
Maturity Date: January 15, 2026
Frequency: 2 (for semi-annual payments)

Step 2: In a cell (say B2), enter the first formula to find the next coupon date:
=COUPNCD(DATE(2024,1,15), DATE(2026,1,15), 2)

Step 3: To find the next few coupon dates, all we need to do is add 6 months to the result (since it’s semi-annual).

It can be done using:
=EDATE(B2, 6).

Drag this formula to fill future dates.

Step 4: We must continue this till the maturity date is reached. This gives you a forecast of all future coupon dates for planning and tracking interest payments.

Important Things to Note

The #NUM! error occurs when we use COUPNCD when the settlement date is later than the maturity date, the frequency is not 1, 2, or 4, or the basis is not 0-4.
The #VALUE! error occurs when the settlement date or maturity date is not valid or when we supply any non-numeric arguments.
Dates should be entered in date format or converted using the DATE function to avoid this error.
The COUPNCD Excel function rounds all numbers to the nearest whole number.

Frequently Asked Questions (FAQs)

What does the COUPNCD function do in Excel?

The COUPNCD function returns the next coupon date after the settlement date for a bond that pays periodic interest. It is useful for tracking interest payment schedules on fixed-income securities. Some functions similar to COUPNCD include:

COUPDAYBS: It calculates the number of days from the beginning of the coupon period to the settlement date for a security that pays periodic interest.

COUPDAYS: It returns the number of days in the coupon period that contains the settlement date.

When do we get a #NUM! error with COUPNCD?

We get an #NUM! error under the following conditions:

The settlement date is after the maturity date.
Frequency is not either 1, 2, or 4.
The basis is a number outside the accepted value range of 0-4.

What is the difference between COUPNCD in Google Sheets and COUPPCD?

COUPNCD returns the next coupon date after the settlement date.
COUPPCD returns the previous coupon date before the settlement date.

The main difference lies in timing. COUPNCD helps forecast when the next interest payment is due, while COUPPCD helps determine the last payment that occurred. Both are useful for tracking bond cash flows.

Download Template

This article must help understand COUPNCD Function in Google Sheets with its formulas and examples. You can download the template here to use it instantly.

COUPNCD Function In Google Sheets Download

COUPNCD in Google Sheets