How To Use COUPNCD Function in Google Sheets

The COUPNCD function in Google Sheets is useful when you need to calculate the next coupon date after the settlement date.

This function is useful when trading securities, which guarantee an interest payment at a certain frequency for the buyer after the settlement date.

The rules for using the COUPNCD function in Google Sheets are as follows:

• The function requires several arguments: the settlement date, maturity date, and frequency of payment.
• You may also indicate the day count basis for the computation.
• The function returns the date of the next coupon date.

Let’s take a look at a quick example.

You have recently acquired a security bond with a settlement date of January 2nd, 2021. According to the bond’s contract, the maturity date of the security is December 9th, 2026. Payments are received quarterly, or a frequency of four payments a year. How do we compute for the next coupon date?

With all this info, you can use the COUPNCD function to output the next coupon date easily. Let’s dive into how to use all the provided information about our security and later work on an actual sample spreadsheet.

The Anatomy of the COUPNCD Function

So the syntax (the way we write) of the COUPNCD function is as follows:

=COUPNCD(settlement, maturity, frequency, day_count_convention)

Let’s dissect this thing and understand what each of these terms means:

• = the equal sign is how we start any function in Google Sheets.
• COUPNCD() is our COUPNCD function. It computes the next coupon payment date of a given security.
• settlement refers to the security’s settlement date, or the date when the security is delivered to the buyer. 
• maturity refers to the end date of the security.
• frequency refers to the number of coupon payments made per year. Annual payments correspond to a frequency of 1, quarterly payments correspond to a frequency of 4.
• day_count_convention is an indicator of what day count message to use. By default, the value is 0, which corresponds to the US (NASD) 30/360 convention, which assumes 30 day months and 360 day years.

A Real Example of Using COUPNCD Function

Let’s look into an example of the COUPNCD function being used in a Google Sheet spreadsheet.

In the image above, we have a table of securities with settlement dates, maturity dates, and frequency of payment. Using the COUPNCD function, we’re able to calculate the next coupon date of the given security.

To get the values in Column E, we just need to use the following formula:

=COUPNCD(A2,B2,C2,D2)

You can try it out yourself by making a copy of the spreadsheet above using the link I have attached below. 

If you’re ready to try out the COUPNCD function in Google Sheets yourself, let’s begin writing it from scratch!

How to Use COUPNCD Function in Google Sheets

1. To start using the COUPNCD function, select the cell we will first put our function’s output. In this example, we can start computing the first security by writing our formula in cell D2.
   
   
   
   
2. Next, we simply need to type the equal sign ‘=‘ to begin the function, followed by ‘COUPNCD(‘. 
3. A tooltip box may appear with info on the COUPNCD function. We can click on the arrow on the top-right-hand corner of the box to minimize it if necessary.
   
   
   
4. Next, we should enter our arguments into our function. Cells A2, B2, and C2 contain the settlement date, maturity date, and frequency, respectively.
   Afterward, simply hit Enter on your keyboard to let the function evaluate. As seen in the image below, now we know the next coupon date for our first security.
   
5. Finally, we can drag down the formula to fill out the rest of the table. We now have the next coupon date of all our securities!
   
    

Frequently Asked Questions (FAQ)

Why does my formula output a #VALUE! error?
Either the settlement date or the maturity date may be invalid. To assure that the dates are valid, you may input the date using the DATE formula, or use data validation for your dates. 

Why does my formula output a #NUM! error?
A #NUM! error indicates that there is something wrong with the arguments that prevent a result from being calculated. There could be multiple reasons why this can happen with the COUPNCD function. You must check if your frequency is either 1, 2 or 4, since any other value won’t be accepted. Your settlement date must also be earlier than your maturity date. Lastly, your day count convention, if specified, must be a number between 0 and 4 

That’s all you need to know on how to use the COUPNCD function in Google Sheets. This step-by-step guide shows how easy it is to find the next coupon date of a given security.

You can create powerful worksheets by using the COUPNCD functions in Google Sheets together with the various other Google Sheets formulas available on the platform. 

Make sure to subscribe to our newsletter to stay updated on the latest guides and tutorials from us!

Get emails from us about Google Sheets.

Our goal this year is to create lots of rich, bite-sized tutorials for Google Sheets users like you. If you liked this one, you'll love what we are working on! Readers receive ✨ early access ✨ to new content.

Subscribe