Table of Contents
The COUPNUM function in Excel is a useful tool for determining the number of coupon payments that occur between two specified dates. This function takes into account the frequency of coupon payments and the settlement and maturity dates of a bond. By inputting these parameters, the COUPNUM function calculates the total number of coupon payments that will occur within the given time frame. This can be particularly helpful for individuals or businesses looking to track their bond payments and plan their finances accordingly. The COUPNUM function is easy to use and provides a reliable and efficient solution for determining the number of coupon payments in Excel.
This article describes the formula syntax and usage of the COUPNUM function in Microsoft Excel.
Description
Returns the number of coupons payable between the settlement date and maturity date, rounded up to the nearest whole coupon.
Syntax
COUPNUM(settlement, maturity, frequency, [basis])
Important: Dates should be entered by using the DATE function, or as results of other formulas or functions. For example, use DATE(2008,5,23) for the 23rd day of May, 2008. Problems can occur if dates are entered as text.
The COUPNUM function syntax has the following arguments:
Settlement Required. The security’s settlement date. The security settlement date is the date after the issue date when the security is traded to the buyer.
Maturity Required. The security’s maturity date. The maturity date is the date when the security expires.
Frequency Required. The number of coupon payments per year. For annual payments, frequency = 1; for semiannual, frequency = 2; for quarterly, frequency = 4.
Basis Optional. The type of day count basis to use.
Basis | Day count basis |
0 or omitted | US (NASD) 30/360 |
1 | Actual/actual |
2 | Actual/360 |
3 | Actual/365 |
4 | European 30/360 |
Remarks
Microsoft Excel stores dates as sequential serial numbers so they can be used in calculations. By default, January 1, 1900 is serial number 1, and January 1, 2008 is serial number 39448 because it is 39,448 days after January 1, 1900.
The settlement date is the date a buyer purchases a coupon, such as a bond. The maturity date is the date when a coupon expires. For example, suppose a 30-year bond is issued on January 1, 2008, and is purchased by a buyer six months later. The issue date would be January 1, 2008, the settlement date would be July 1, 2008, and the maturity date would be January 1, 2038, which is 30 years after the January 1, 2008, issue date.
All arguments are truncated to integers.
If settlement or maturity is not a valid date, COUPNUM returns the #VALUE! error value.
If frequency is any number other than 1, 2, or 4, COUPNUM returns the #NUM! error value.
If basis < 0 or if basis > 4, COUPNUM returns the #NUM! error value.
If settlement ≥ maturity, COUPNUM returns the #NUM! error value.
Cite this article
stats writer (2024). How can I use the COUPNUM function in Excel to determine the number of coupon payments between two dates?. PSYCHOLOGICAL SCALES. Retrieved from https://scales.arabpsychology.com/stats/how-can-i-use-the-coupnum-function-in-excel-to-determine-the-number-of-coupon-payments-between-two-dates/
stats writer. "How can I use the COUPNUM function in Excel to determine the number of coupon payments between two dates?." PSYCHOLOGICAL SCALES, 29 Jun. 2024, https://scales.arabpsychology.com/stats/how-can-i-use-the-coupnum-function-in-excel-to-determine-the-number-of-coupon-payments-between-two-dates/.
stats writer. "How can I use the COUPNUM function in Excel to determine the number of coupon payments between two dates?." PSYCHOLOGICAL SCALES, 2024. https://scales.arabpsychology.com/stats/how-can-i-use-the-coupnum-function-in-excel-to-determine-the-number-of-coupon-payments-between-two-dates/.
stats writer (2024) 'How can I use the COUPNUM function in Excel to determine the number of coupon payments between two dates?', PSYCHOLOGICAL SCALES. Available at: https://scales.arabpsychology.com/stats/how-can-i-use-the-coupnum-function-in-excel-to-determine-the-number-of-coupon-payments-between-two-dates/.
[1] stats writer, "How can I use the COUPNUM function in Excel to determine the number of coupon payments between two dates?," PSYCHOLOGICAL SCALES, vol. X, no. Y, ص Z-Z, June, 2024.
stats writer. How can I use the COUPNUM function in Excel to determine the number of coupon payments between two dates?. PSYCHOLOGICAL SCALES. 2024;vol(issue):pages.