MS Excel: COUPNUM function for interest payment

The COUPNUM function in Excel calculates the number of coupon periods between the settlement date and the maturity date of a bond. In simple words, it tells you how many interest payments are left for the bond from the purchase date until it matures.

This function is very useful for bond investment analysis, amortization schedules, and pricing bonds.

Syntax

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

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

Key Points

• Returns the number of coupons (interest payments) left.

• Frequency is crucial: more frequent payments = more periods.

• Output is always rounded up to the next whole number (Excel always counts the final coupon period even if partial).

  
  

Practical Example

Suppose you buy a bond on April 15, 2024, and it matures on April 15, 2028.The bond pays interest semi-annually (2 times a year) using the 30/360 basis.

The formula:

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

Result: 8

Explanation:There are 4 years × 2 payments per year = 8 coupon periods remaining from the settlement date to maturity.

Another example:Suppose you settle on May 1, 2024, instead:

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

Result: 8

Even if you purchase the bond shortly after a coupon date, Excel still counts the full number of periods until maturity by rounding up.

Summary

Item	Value
Settlement Date	01-May-2024
Maturity Date	15-Apr-2028
Frequency	2 (Semi-Annual)
Basis	0 (30/360 US)
Number of Coupons	8

Important Notes

• If the settlement date is on or after the maturity date, Excel returns a #NUM! error.

• Frequency must be 1, 2, or 4; otherwise, #NUM! error occurs.

• Basis affects only day counts for accrued interest calculations — not the number of coupons.

When to Use COUPNUM?

• When you need to know how many coupon payments are left.

• For accrued interest and bond pricing calculations

• To schedule cash flows for fixed-income investments.

• When building financial models involving bonds.

Conclusion

The COUPNUM function in Excel is an excellent tool for fixed-income investors, financial analysts, and accountants who work with bonds.It quickly shows you how many future payments a bond has, making valuation and planning much easier and more accurate.