Syntax. COUPNUM(Settlement,Maturity,Frequency,Basis)
Definition. This function determines the number of interest payments (coupon dates) the new owner is facing after buying a fixed-interest security with regular coupon dates.
Arguments
Settlement (required) The date when the ownership of the security changes.
Maturity (required) The date when the repayment of the loan certified by the security takes place.
Frequency (required) Because it is quite common to have fixed-interest securities with several interest dates per year, the required argument Frequency supplies the dates-per-year number. Allowed values are: 1 = annual; 2 = biannual; 4 = quarterly.
Basis (optional) The method you want to use for determining the days in the year according to Table 15-2. If this argument is omitted, Excel calculates with Basis = 0.
The arguments have the following requirements:
Date specifications must not have a time; decimal places are truncated. The arguments Frequency and Basis are also truncated to integers.
If date arguments cannot be resolved to a valid date, the function returns the
#VALUE!error.If Frequency cannot be resolved to 1, 2, or 4, or Basis cannot be resolved to a number from 0 through 4, the function returns the
#NUMBER!error. The same happens when the settlement date is later than the maturity.
Important
Excel Help contains the following note:
“Dates should be entered with the DATE() function or as results of other formulas or functions. Problems might occur if dates are entered as text.”
Background. Regular coupon interest for a fixed-interest security (bond) is paid according to the maturity date and the frequency within a year.
The result of this function can be used to calculate the cash value of the future payments from the security (price) at the next interest date. For comparison, take a look at the explanations in the detailed calculation example of the PRICE() and YIELD() functions. Different annual day counters can lead to different results in the intra-annual scope.
Example. This function was used in the detailed calculations of the examples for RATE() and YIELD(). See those sections for examples of the use of this function.