What is the COUPNUM Function?
The COUPNUM function is a Financial functionFunctionsThe COUPNUM function is a Financial function. It helps calculate the number of coupon payments that we will receive from the bond’s settlement date to its maturity date. Formula =COUPNUM(settlement, maturity, frequency, [basis]) The COUPNUM function uses the following arguments: Settlement (required argument) – It is. It helps calculate the number of coupon payments that we will receive from the settlement date of a bondMarketable SecuritiesMarketable securities are unrestricted short-term financial instruments that are issued either for equity securities or for debt securities of a publicly listed company. The issuing company creates these instruments for the express purpose of raising funds to further finance business activities and expansion. to its maturity date.
Formula
=COUPNUM(settlement, maturity, frequency, [basis])
The COUPNUM function uses the following arguments:
- Settlement (required argument) – It is the settlement date of a given security. It is the date after the security is traded to the buyer.
- Maturity (required argument) – It is the date when the security expires.
- Frequency (required argument) – It is the number of coupon payments per year. The argument can take a value of 1 (annual payment), 2 (semi-annual payments), or 4 (quarterly payments).
- Basis (optional argument) – It specifies the day count basis to be used. It uses one of the following values:
Basis | Day Count basis |
---|
0 or omitted | US(NASD) 30/360 |
1 | Actual/actual |
2 | Actual/360 |
3 | Actual/365 |
4 | European 30/360 |
The function will default to 0 when omitted. It indicates that the days in the month are counted using the US 30-day method with a 360-day year. When we enter 1 as the basis, the function uses the actual number of days in the month and year. Whereas, when we enter 2, it will count the actual days in the month with a 360-day year while 3 will assume a 365-day year. When we enter 4 as the basis, it is same as 1 except that it uses the European 30-day method.
How to use the COUPNUM Function in Excel?
To understand the uses of the COUPNUM function, let’s consider a few examples:
Example 1
Let’s see how the COUPNUM function works when we are given the following data:
Additionally, we are told that count basis would be 1.
Using the formula =COUPNUM(C5,C6,C7,1), we get 5 as the result, which means we will get five coupon payments.
Example 2
As the COUPNUM function doesn’t accept dates in text format, we need to convert them into DATE format. Let’s see an example to understand it. Suppose we are given the following data:
As we need to find the number of coupon payments that we will receive so using COUPNUM, we will need to first convert the dates given in text format.
The result we get here is 5. The COUPNUM function first converted the dates in text format into proper dates and then calculated the number of days. Here, we used 1 as frequency and 2 as basis.
A few notes about the COUPNUM Function:
- #NUM! error – Occurs in the following scenarios:
- When the settlement date provided is greater than or equal to (≥) the maturity date.
- When the given frequency argument provided by the user is not equal to 1, 2 or 4.
- When the given basis argument is a number other than 0, 1, 2, 3 or 4.
- #VALUE! error – Occurs in the following scenarios:
- When the given settlement date or maturity date is not a valid date. Remember that we need to enter dates in date format or else use the DATE function to convert them into proper dates. The function doesn’t work when dates given are in text format. For example, =COUPNUM(“1/25/2023″,”11/15/2024”,2,1) = #VALUE.
- Any of the arguments given is non-numeric.
- The COUPNUM function truncates all arguments to integers.
Additional resources
Thanks for reading CFI’s guide to important Excel functions! By taking the time to learn and master these functions, you’ll significantly speed up your financial analysis. To learn more, check out these additional resources:
- Excel Functions for FinanceExcel for FinanceThis Excel for Finance guide will teach the top 10 formulas and functions you must know to be a great financial analyst in Excel. This guide has examples, screenshots and step by step instructions. In the end, download the free Excel template that includes all the finance functions covered in the tutorial
- Advanced Excel Formulas Course
- Advanced Excel Formulas You Must KnowAdvanced Excel Formulas Must KnowThese advanced Excel formulas are critical to know and will take your financial analysis skills to the next level. Advanced Excel functions you must know. Learn the top 10 Excel formulas every world-class financial analyst uses on a regular basis. These skills will improve your spreadsheet work in any career
- Excel Shortcuts for PC and MacExcel Shortcuts PC MacExcel Shortcuts - List of the most important & common MS Excel shortcuts for PC & Mac users, finance, accounting professions. Keyboard shortcuts speed up your modeling skills and save time. Learn editing, formatting, navigation, ribbon, paste special, data manipulation, formula and cell editing, and other shortucts