MS Excel: COUPDAYSNC function for bond pricing

Fakhriddinbek
Apr 27
2 min read

The COUPDAYSNC function in Excel calculates the number of days from the settlement date to the next coupon date for a bond. In simple terms, it answers:"How many days are left until the next interest payment?"

This function is very useful in bond pricing, interest accrual, and financial modeling.

Excel interface with formula window open for COUPDAYSNC function. Columns are visible in the background with a blank cell selected.

Syntax

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

Argument	Description
settlement	The bond's settlement date (date buyer receives bond ownership).
maturity	The bond's maturity date (when the bond will be fully repaid).
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

Calculates days remaining until the next coupon payment.
Helps in interest and accrued income calculations.
Basis determines how days are counted (360 days, actual, etc.).
Requires correct frequency and date inputs to avoid errors.

Practical Example

Suppose you purchase a bond on April 15, 2024, maturing on April 15, 2028.It pays semi-annually (2 times a year), and the day count basis is standard 30/360.

The formula:

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

Result: 180

Explanation:Since the bond pays every 6 months, and you purchased it exactly at the start of the coupon period, there are 180 days until the next coupon date.

Now, if the settlement date were May 1, 2024, instead:

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

Then, the result would be 150 days (approximately), because 30 days have passed since April 1st in a 30/360 system.

Summary

Item	Value
Settlement Date	01-May-2024
Maturity Date	15-Apr-2028
Frequency	2 (Semi-Annual)
Basis	0 (30/360 US)
Days to Next Coupon	~150 days

Important Notes

Settlement date must be before maturity date.
Frequency must be exactly 1, 2, or 4 (otherwise Excel returns a #NUM! error).
Make sure you choose the correct basis for the market (US vs Europe).

When to Use COUPDAYSNC?

When calculating days left until the next bond interest payment.
When pricing bonds accurately for buyers and sellers.
When building financial models involving bond cashflows.

Conclusion

The COUPDAYSNC function is an excellent tool for investors, analysts, and financial modelers working with bond markets.It ensures accurate timing for coupon payments, which is critical for fair bond pricing and interest calculations.