COUPDAYS Function

Returns the number of days in the coupon period containing settlement.

Category

Financial

Available in

All versions, Microsoft 365

Returns

A number of days

Want to practice COUPDAYS in a real spreadsheet? Open a blank sheet in our Practice Sandbox and try it with live data, with the AI Excel tutor beside you. No Excel needed — just a free account.Try it free in the Sandbox

Overview

COUPDAYS returns the length of the current coupon period in days, using the chosen basis.

The coupon functions (COUPPCD, COUPNCD, COUPNUM, COUPDAYS, COUPDAYBS, COUPDAYSNC) describe the coupon period a settlement date falls in — the building blocks of accrued interest and bond pricing.

Syntax

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

Arguments

ArgumentRequired?What it does
settlementYesThe date the buyer takes ownership (after the issue date).
maturityYesThe date the security is repaid.
frequencyYesCoupon payments per year: 1 = annual, 2 = semiannual, 4 = quarterly.
basisNoDay count: 0 or omitted = US 30/360, 1 = actual/actual, 2 = actual/360, 3 = actual/365, 4 = European 30/360.

Examples

Example 1 — Northstar's corporate bond

Northstar's treasury buys a corporate bond: 5% coupon paid twice a year, maturing 11/15/2033, settling 3/15/2026.

B5fx=COUPDAYS(B1,B2,B3,B4)
AB
1Settlement3/15/2026
2Maturity11/15/2033
3Frequency2
4Basis0
5Days in period180
6Days (actual, basis 1)181

On a 30/360 basis every semiannual period is 180 days; on actual days (basis 1) this one is 181.

Common mistakes

  • #VALUE! or #NUM!. A date isn't a real date, settlement is on or after maturity, or frequency isn't 1, 2 or 4.
  • A date result shows as a number. Format the cell as Short Date.

Notes

Kyle Stott

Author

Kyle Stott

Certified Microsoft Excel Expert

Kyle has worked professionally with Microsoft Excel for over a decade and has consulted on and taught Excel best practices to over 400 companies across 30+ countries.