What is COUPNUM in Google Sheets?
The COUPNUM function in Google Sheets calculates the number of coupon payments between a bond’s settlement date and maturity date. It’s useful for bond valuation, especially when the bond pays interest periodically (annually, semi-annually, or quarterly). The function uses the settlement date, maturity date, payment frequency, and day count basis to determine how many interest payments will occur.
For example, suppose a bond is settled on March 1, 2024, and matures on March 1, 2026, with quarterly interest payments. To find the number of payments, use:
=COUPNUM(DATE(2024,3,1), DATE(2026,3,1), 4, 0)
This returns 8, which meant that there are eight coupon payments from settlement to maturity.
Key Takeaways
- COUPNUM in Google Sheets returns the total number of coupon periods between a bond’s settlement date and maturity date.
- The function requires four inputs: settlement date, maturity date, payment frequency (1, 2, or 4), and an optional day count basis (0–4).
- Its syntax is: =COUPNUM(settlement, maturity, frequency, [basis])
- It helps investors estimate how many interest payments they will receive, which is useful for bond valuation and investment planning.
- Using the DATE function to enter settlement and maturity dates helps prevent errors and ensures correct calculations.
Syntax
To understand how COUPNUM in Google Sheets works, let’s understand its basic syntax. The following are its arguments:
=COUPNUM(settlement, maturity, frequency, [basis])
- settlement – The date the investor purchases the bond
- maturity – The date of maturity of the bond.
- frequency –The frequency with which the bond pays interest in a year:
- We enter 1 for annual, 2 for semi-annual, or 4 for quarterly payments.
- basis (optional) – Defines how days are counted between dates:
0 – US (NASD) 30/360 (default)
1 – Actual/Actual
2 – Actual/360
3 – Actual/365
4 – European 30/360
This function returns the total number of coupon periods between the settlement and maturity dates.
How To Use COUPNUM function in Google Sheets?
The COUPNUM helps financial analysts evaluate the total number of payments on a bond and plan financial returns accordingly. One can enter COUPNUM in Google Sheets in two main ways:
- Manually entering the function
- Through the Google Sheets Menu bar
Enter the COUPNUM Function Manually
Let us look at how to type the formula manually once you enter the bond details. Let’s look at a simple step-by-step example. A person purchases a bond on April 10, 2025. The bond matures on October 10, 2027. It pays a quarterly interest quarterly and uses the default day count basis. Let us calculate the number of coupon payments using COUPNUM.
Step 1: Enter all the bond details in a sheet, such as settlement date, maturity date, frequency, etc.
Step 2: Click on a blank cell where you want the result to appear (say, cell B6). Start typing the formula by opening the parentheses:
=COUPNUM(
Step 3: Enter the arguments in the order mentioned in the syntax, separated by commas. Use the DATE function to correctly input the dates if you enter the value directly. Here, we use the cell references.
=COUPNUM(DATE(2025,4,10), DATE(2027,10,10), 4)
=COUPNUM(DATE(B1, B2, B3)
Step 4: Press Enter. You get the number of interest payments scheduled between April 10, 2025, and October 10, 2027 with quarterly payment.
Entering COUPNUM Through the Menu Bar
One can insert the function from Google Sheets’ financial functions list:
- Click on any cell where you want the result.
- From the top menu, go to Insert → Function → Financial.
- Scroll through the list and select COUPNUM.
- Enter the arguments. Close the parentheses and press Enter to get the result.
Examples
COUPNUM stands for Coupon Number. It gives us the number of interest payments left between the bond’s purchase date and its maturity. Let us look at some simple examples to understand how this function works.
Example #1 – Determine the Total Number of Interest Payments For a Bond
An investor purchases a bond on January 1, 2023, which matures on January 1, 2027. The bond pays interest two times a year (semi-annual). We want to find out how many interest payments the investor will receive during the bond’s life using the COUPNUM function in Google Sheets.
Step 1: Open a Google Sheet and enter the bond details as follows:
- Settlement Date: April 1, 2023
- Maturity Date: April 1, 2027
- Frequency: 2 (semi-annual)
Step 2: Enter the COUPNUM formula in Google Sheets in an empty cell as shown below.
Type the function using cell references (the DATE function if you’re typing the dates directly).
=COUPNUM(B1, B2, B3)
If entering dates directly, use
=COUPNUM(DATE(2023,1,1), DATE(2027,1,1), 2)
This ensures the function reads the dates correctly and calculates the result.
Step 4: Press Enter. The result shows twelve coupon payments scheduled from Jan 1, 2023, until Jan 1, 2027. This calculation helps the investor plan expected interest income over the bond’s duration.
Example #2 – Calculate the Number of Coupon Payments For a Fixed Income Security
An investor purchases a fixed income bond on March 15, 2023. The bond matures on March 15, 2028 and pays interest semiannually. He wants to calculate how many coupon payments they will receive during this period.
Step 1: Enter the bond details in a Google Sheet.
Step 2: Select an empty cell to enter the formula where you want the number of coupon payments to appear.
Type the following formula:
=COUPNUM(B1, B2, B3, B4)
This function calculates how many semi-annual coupon periods fall between the settlement and maturity dates.
Step 4: Press Enter; you get the total number of coupon payments the investor will receive from the purchase date until maturity.
Here, the COUPNUM function returns 10 thereby showing ten semi-annual coupon payments between March 15, 2023, and March 15, 2028. This is a simple and effective way to estimate the bond’s income schedule and plan investments accordingly.
Example #3 – Estimate the Number of Coupon Payments For a Projected Investment
A person is considering buying a bond on August 1, 2025, which matures on August 1, 2027. The bond pays interest quarterly. The investor wants to estimate how many coupon payments they would receive if they invested now.
Step 1: Enter bond details in Google Sheets, as shown below.
Step 2: Enter the COUPNUM formula in an empty cell.
=COUPNUM(B1, B2, B3, B4)
This will calculate how many quarterly coupon payments will occur between the investment date and the maturity date.
Step 4: Press Enter.
Here COUPNUM returns 8, indicating the investor will receive 8 quarterly coupon payments if they invest on August 1, 2025. This quick estimate helps investors plan for cash flow and evaluate whether the bond meets their income expectations.
Important Things to Note
- The #NUM! error occurs when the settlement date is after the maturity date, the frequency is not 1, 2, or 4, or the basis is not between 0 and 4.
- The #VALUE! error appears when the settlement or maturity date is invalid, or when non-numeric values are entered for frequency or basis.
- Dates must be entered in proper date format or formatted using the DATE function to avoid formatting errors.
- The COUPNUM function always returns a whole number, representing the number of coupon periods between the settlement and maturity dates.
Frequently Asked Questions (FAQs)
The COUPNUM function returns the number of coupon periods between the settlement date and the maturity date for a bond that pays interest periodically.
This helps estimate how many interest payments an investor will receive during the holding period. It is commonly used in bond valuation and investment planning for fixed-income securities.
The function requires inputs such as settlement date, maturity date, frequency, and basis.
A #NUM! error occurs if the settlement date is later than the maturity date, which is not allowed. You’ll also see this error if the frequency is not 1 (annual), 2 (semi-annual), or 4 (quarterly).
Another reason could be if the basis argument is a number outside the valid range of 0 to 4. Ensuring all values are within accepted limits will prevent this error.
The #VALUE! error usually occurs when the settlement or maturity dates are not in a valid date format or are entered as plain text. Further, it happens if any of the numeric arguments like frequency or basis are given as non-numeric text. Using the DATE function to format dates and checking inputs can help avoid this error.
Google Sheets provide several functions for working with coupon-paying securities, similar to COUPNUM.
COUPNCD: Returns the next coupon date after the settlement date.
COUPPCD: Returns the previous coupon date before the settlement date.
COUPNUM: Returns the number of coupon periods between settlement and maturity.
COUPDAYS: Returns the total number of days in the current coupon period.
COUPDAYSNC: Returns the number of days from the settlement date to the next coupon date.
Download Template
This article must help understand COUPNUM Function in Google Sheets with its formulas and examples. You can download the template here to use it instantly.
Recommended Articles
Guide to What is COUPNUM Function in Google Sheets. We learn how to use it to find the number of coupon payments between settlement and maturity date. You can learn more from the following articles. –
Leave a Reply