Syntax. COUPNCD(Settlement,Maturity,Frequency,Basis)
Definition. This function determines the date of the first coupon interest payment after a fixed-interest security with regular coupon dates has changed ownership.
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, shown earlier. 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.
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. Different annual day counters can lead to different results in the intra-annual scope.
Examples. This function was used in the detailed calculations of the examples for PRICE() and YIELD(). See those sections for examples of the use of this function.