What it does: Calculates next coupon, or interest payment, date after the settlement date.
Syntax=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.
Sample Usage
=COUPNCD(DATE(2021, 11, 28), DATE(2027, 2, 31), 1, 1)
//This returns March 3, 2022 as the next coupon date.
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.
How to use the COUPNCD function in Google Sheets
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.
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.
- The following formula calculates the next coupon date for this cell:
=COUPNCD(A2, B2, C2, D2)
- This displays the next coupon date value for the corresponding security amount.
- In a similar manner, we can calculate the next coupon date values for the remaining security amounts as well.
If dates are in invalid format
- In this example, the dates are in TEXT format.
- This gives us a #VALUE! Error as shown below.
- 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)
- As shown below this helps us get rid of the #VALUE! Error.
- In a similar manner, we can calculate the next coupon date values for the remaining security amounts.
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! 🤓