COUPNCD function in Google Sheets – 2022 Easy Guide

COUPNCD function in Google Sheets – 2022 Easy Guide
Reading Time: 4 minutes

COUPNCD: Google Sheets Formulae, Examples Explained

Learn how to use the COUPNCD function in Google Sheets. 

What is the COUPNCD function in Google Sheets?

The COUPNCD function calculates the next coupon date (date on which the next payment is to be made) after the settlement date.

How do you use COUPNCD function in Google Sheets?

As a financial calculator – CoupnCD (A1, B1) where A1 is the interest rate and B1 is the number of time periods

Compare investment plans – CoupnCD (X1, Y1) where X1 and Y1 are interest rates of respective investment plans – the value returned is the number of days after which the first investment pays off more than the other.

Calculate Present Value – CoupnCD (L1, L2, M1, M2) where L1 = Annual Payments, L2 = Number of Payments, M1 = Interest Rate, M4 = Number of Periods.

Syntax of the COUPNCD function in Google Sheets

=COUPNCD(settlement, maturity, frequency, day_count_convention)

settlement– Date on which the security is to be submitted to the buyer. 

maturity– Last date of payment of security. 

frequency– Number of coupon (or interest) payments per year. Frequency takes a value of 1, 2, or 4 for annual, half-yearly, or quarterly payments respectively.

day_count_convention– Indicates the convention of the day count method to be used. This variable has a default value of 0. This can take values from 0 to 4

  • 0 – Calculations are done considering a 30 day month and 360 day year, as specified by the US (NASD) standards.
  • 1 – Calculations are done considering the actual number of days in the month and year. 
  • 2 – Calculations are done considering the actual number of days in the month and 360 days in a year.
  • 3 – Calculations are done considering the actual number of days in the month and 365 days in a year.
  • 4 – Similar to 0.Calculations are done considering a 30 day month and 360 day year, as specified by the European standards.

Data Setup for COUPNCD function in Google Sheets

The data given below, corresponds to five security amounts that are due for payment. The date of settlement, maturity date, and frequency of payment has also been specified for each security amount. We will make use of the COUPNCD function to fill in the next coupon date values in column E. 

Table showing five security amounts that are due for payment
Fig 1: Data

If dates are in a valid format

  • Select a cell to compute the corresponding next coupon date value. I have selected cell E2 as shown below. 
  • Start typing ‘=’ followed by the name of the function ‘COUPNCD’ in the cell. As you type, Google Sheets will automatically suggest the required function. Choose this function. 
COUPNCD Function in Google Sheets
Fig 2: Example of COUPNCD Function in Google Sheets
  • The following formula calculates the next coupon date for this cell:

=COUPNCD(A2, B2, C2, D2)

COUPNCD Function formula in Google Sheets
Fig 3: Formula to be used
  • This displays the next coupon date value for the corresponding security amount.
Next coupon date value is calculated and displayed
Fig 4: The next coupon date value is calculated
  • In a similar manner, we can calculate the next coupon date values for the remaining security amounts as well.  
Table displaying all the calculated Next coupon date values
Fig 5: Calculated next coupon date values

If dates are in invalid format

  • In this example, the dates are in TEXT format.
  • This gives us a #VALUE! Error as shown below.
#VALUE! Error is displayed while using COUPNCD Function in Google Sheets
Fig 6: #VALUE! Error is displayed
  • To resolve this error, we should either re-enter the dates in the Date format or use the DATE function as shown in the following formula:

=COUPNCD(DATE(2021, 11, 28), DATE(2027, 2, 31), C2, D2)

Using DATE Function in Google Sheets
Fig 7: Formula to be used
  • As shown below this helps us get rid of the #VALUE! Error.  
#VALUE! Error while using COUPNCD Function in Google Sheets is omitted
Fig 8: next coupon value date is calculated
  • In a similar manner, we can calculate the next coupon date values for the remaining security amounts.
Table displaying the next coupon date values
Fig 9: Final next coupon date values

How to avoid #NUM! Error for CoupnCD function in Google Sheets

While calculating the next coupon date value one can sometimes get the #NUM! Error. 

This error occurs in the following situations- 

  • Date of settlement is greater than or equal to the maturity date.
  • If the value of frequency is anything other than 1, 2, or 4.
  • If the value of day_count_convention is anything other than 0, 1, 2, 3 or 4.

To avoid getting this error ensure that the date of the settlement comes before the maturity date, the frequency value is either 1, 2 or 4, and the day_count_convention value is either 0, 1, 2, 3 or 4. 

Similar Formulae to CoupnCD function in Google Sheets

Just as the CoupnCD function in Google Sheets is extensively used in financial analysis – some other formulae that are frequently used are CountIF, AverageIFS and SumIFS functions. Use Amigo’s step-by-step guides to learn about each of these formulas.

If you liked reading this article, you may consider learning more tips, tricks and formulae using our definitive guide on Google Sheets. Enjoy Reading! 🤓