COUPPCD function

The COUPPCD function in Excel calculates the previous coupon date before the settlement date for a bond or security. This function is useful for bondholders who want to know when the last coupon payment occurred prior to purchasing the bond.

Syntax

COUPPCD(settlement, maturity, frequency, [basis])

Parameters

• settlement: The bond’s settlement date, which is the date after the bond is issued and the date when the bondholder receives ownership of the bond.
• maturity: The bond’s maturity date, which is the date the bond’s principal is repaid.
• frequency: The number of coupon payments per year:
  • 1 for annual payments
  • 2 for semiannual payments
  • 4 for quarterly payments
• [basis] (optional): The day count basis to use. If omitted, it defaults to 0 (US (NASD) 30/360 method). Possible values are:
  • 0: US (NASD) 30/360
  • 1: Actual/Actual
  • 2: Actual/360
  • 3: 360/360
  • 4: European 30/360

How It Works

The COUPPCD function calculates the previous coupon date that occurred before the settlement date. This is helpful for bondholders when calculating accrued interest or determining the exact date of the most recent coupon payment before purchasing the bond.

Example

Suppose a bond was purchased on March 15, 2023, and it matures on March 15, 2028. The bond pays interest semiannually (twice a year). We want to calculate the previous coupon date before the settlement date, March 15, 2023.

The formula would be:

=COUPPCD("3/15/2023", "3/15/2028", 2)

Explanation of the Example:

• "3/15/2023": The settlement date, i.e., the date the bondholder purchases the bond.
• "3/15/2028": The maturity date, i.e., the date the bond will mature.
• 2: Semiannual frequency, meaning the bond pays interest twice a year.

The function will return the previous coupon date before March 15, 2023.

Important Notes

• The COUPPCD function calculates the last coupon date that occurred before the settlement date.
• The frequency argument indicates how often the bond pays interest: 1 for annual, 2 for semiannual, or 4 for quarterly payments.
• The basis argument is optional and, if omitted, defaults to 0 (US 30/360).
• The result is a date representing the previous coupon payment before the settlement date.

Summary

The COUPPCD function in Excel calculates the previous coupon date that occurred before the settlement date for a bond, making it useful for bondholders to determine when the most recent coupon payment was made before purchasing the bond. This is particularly important for calculating accrued interest on the bond.