COUPDAYS function

The COUPDAYS function in Excel calculates the number of days in the coupon period that contains the settlement date for a bond or security. This function is useful for determining how much time has elapsed within a coupon period when a bondholder purchases the bond, as it calculates the days from one coupon payment to the next.

Syntax

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

Parameters

• settlement: The bond’s settlement date, which is the date the bond is purchased. This is the date after the bond is issued and is the date 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 COUPDAYS function calculates the number of days within the coupon period that contains the settlement date. The coupon period is the time between two successive coupon payments. This is important for bondholders who need to know how much time has passed since the last coupon payment when they purchase 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 number of days in the coupon period that contains the settlement date, which is March 15, 2023.

The formula would be:

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

Explanation of the Example:

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

Important Notes

• The COUPDAYS function assumes that the bond’s coupon payments are spread evenly across the coupon period, based on the frequency provided.
• The basis argument is optional. If omitted, the default basis is 0 (US 30/360), but you can select other basis types depending on the accounting or financial standards.
• The result gives the number of days that have passed within the coupon period containing the settlement date, which is useful for calculating accrued interest on a bond purchase.

Summary

The COUPDAYS function in Excel is used to calculate the number of days in the coupon period that contains the settlement date for a bond. It helps bondholders understand how much of the coupon period has passed at the time of purchasing the bond, which is important when calculating accrued interest or bond pricing.