COUPNUM function

The COUPNUM function in Excel calculates the number of coupon periods between the bond’s settlement date and its maturity date. This function is useful for bondholders who want to determine how many coupon payments (interest payments) will be made during the life of the bond, starting from the settlement date to the bond’s maturity.

Syntax

COUPNUM(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 COUPNUM function calculates the number of coupon periods between the settlement date and the maturity date. The result indicates how many coupon payments will be made during the life of the bond, given the payment frequency. This is important for bondholders who want to know how many times they will receive coupon payments until the bond matures.

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 number of coupon periods between the settlement date and the maturity date.

The formula would be:

=COUPNUM("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 number of coupon periods (i.e., how many coupon payments are made from the settlement date to the maturity date).

Important Notes

• The COUPNUM function calculates the number of coupon payments made between the settlement date and the maturity 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 an integer representing the total number of coupon periods.

Summary

The COUPNUM function in Excel calculates the number of coupon periods between the settlement date and the maturity date for a bond, based on the bond’s coupon payment frequency. It helps bondholders understand how many coupon payments they will receive during the life of the bond.