MS Excel: COUPDAYBS function for bond settlement date

The COUPDAYBS function in Excel calculates the number of days from the beginning of the coupon period to the settlement date of a bond. It’s mainly used when working with bonds and fixed-income securities that pay periodic interest (coupons).

This function is crucial for accurate bond pricing and interest accrual calculations.

Syntax

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

Argument	Description
settlement	The date when the bond is purchased (settlement date).
maturity	The date when the bond matures (final payment date).
frequency	Number of coupon payments per year: 1 = Annual 2 = Semi-Annual 4 = Quarterly
basis (Optional)	Day count basis: 0 = US (NASD) 30/360 (default) 1 = Actual/Actual 2 = Actual/360 3 = Actual/365 4 = European 30/360

Key Points

• Helps determine how much time has passed from the last coupon payment.

• Necessary for interest calculations between coupon periods.

• Basis setting is important for accurate day counts depending on bond rules.

• If inputs are invalid (e.g., settlement after maturity), Excel returns #NUM! error.

Practical Example

Imagine you purchase a bond on April 15, 2024, that matures on April 15, 2028.It pays semi-annually (2 times per year) and uses the standard 30/360 basis.

The formula:

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

Result: 0

Explanation: Since you purchased the bond exactly at the beginning of the new coupon period, days from the beginning are 0.

Now, suppose the purchase was May 1, 2024 (after the period began), then:

=COUPDAYBS(DATE(2024,5,1), DATE(2028,4,15), 2, 0)

The result would be 15 days, meaning 15 days have passed from the start of the coupon period to your settlement.

Summary

Item	Value
Settlement Date	01-May-2024
Maturity Date	15-Apr-2028
Frequency	2 (Semi-Annual)
Basis	0 (30/360 US)
Days from Start	15 days

Important Notes

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

• Always ensure frequency is 1, 2, or 4.

• Basis is crucial for bonds in different markets (e.g., US, Europe).

When to Use COUPDAYBS?

When calculating accrued interest on bonds.

When pricing fixed-income securities between payment dates.

For financial models involving amortized cost or clean/dirty bond prices.

Conclusion

The COUPDAYBS function is an important tool for bond investors and financial analysts, making it easy to calculate the number of days elapsed since the start of the current coupon period.Understanding this function ensures your bond valuation and interest calculations are precise and professional.