Syntax. COUPPCD(Settlement,Maturity,Frequency,Basis)
Definition. This function determines the date of the latest interest payment before the change of ownership of 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 it 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.
The result of this function can be used to manually calculate accrued interest (see the COUPDAYBS() function) or to include the date in a worksheet for better orientation. 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(). See the examples in that section for more information about this function.