MS Excel: COUPDAYS function for coupon period

The COUPDAYS function in Excel returns the number of days in the coupon period that contains the settlement date of a bond.In other words, it tells you how many days are in the full coupon period (between two coupon payments) where your purchase date falls.

It’s very useful for bond pricing and interest accrual calculations.

Syntax

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

Argument	Description
settlement	The bond's settlement date (the date when the bond buyer takes ownership).
maturity	The bond's maturity date (the date when the bond will be fully paid off).
frequency	Number of coupon payments per year: 1 = Annual 2 = Semi-Annual 4 = Quarterly
basis (Optional)	Type of day count to use: 0 = US (NASD) 30/360 (default) 1 = Actual/Actual 2 = Actual/360 3 = Actual/365 4 = European 30/360

Key Points

• Tells you total number of days in the coupon period.

• Different from COUPDAYSBS (which gives days from period start to settlement).

• Critical for accrued interest and clean price bond calculations.

• The basis changes the way days are counted depending on regional standards.

Practical Example

You buy a bond on April 15, 2024, which matures on April 15, 2028.It pays interest semi-annually (2 times a year) and uses the standard 30/360 day count.

The formula:

=COUPDAYS(DATE(2024,4,15), DATE(2028,4,15), 2, 0)

Result: 180

Explanation: There are 180 days in each semi-annual coupon period based on 30/360 convention.

Another example:Suppose the bond pays quarterly (4 times per year) and you use Actual/Actual basis.

=COUPDAYS(DATE(2024,4,15), DATE(2028,4,15), 4, 1)

Depending on exact dates, the number of days would vary slightly — could be around 91 to 92 days (Actual/Actual basis counts exact calendar days).

Summary

Item	Value
Settlement Date	15-Apr-2024
Maturity Date	15-Apr-2028
Frequency	2 (Semi-Annual)
Basis	0 (30/360 US)
Days in Period	180 days

Important Notes

• If settlement date ≥ maturity date, Excel returns a #NUM! error.

• Frequency must be exactly 1, 2, or 4, otherwise #NUM! error appears.

• Basis is crucial for bonds in different countries (US vs Europe, etc.).

• Always ensure settlement date is before maturity date.

• 
  

When to Use COUPDAYS?

• When calculating full coupon periods for bond pricing.

• For accrued interest calculations between payment dates.

• When working on financial models involving bonds and securities.

  
  

Conclusion

The COUPDAYS function is an essential Excel tool for anyone dealing with bond investments and fixed-income securities.It helps ensure your calculations for coupon payments and accrued interest are accurate and reliable.