COUPDAYS Function in Google Sheets: Formula, Examples & Troubleshooting
Calculate coupon-period length with COUPDAYS in Google Sheets. Compare actual and 30/360 counts, audit semiannual dates, and build an explicit coupon fraction.
Verification status: See the evidence and test scope described in this guide. How formula examples are checked
Quick answer: =COUPDAYS(settlement,maturity,frequency,basis) calculates the length of the coupon period containing settlement under the selected convention. It does not count only the days from settlement to the next payment.
Verification: NOT_TESTED_IN_GOOGLE_SHEETS. Google sources were reviewed October 17, 2026. The regular-schedule calendar and financial-convention examples below were independently calculated and remain expected outputs.
Syntax and arguments
=COUPDAYS(settlement, maturity, frequency, [day_count_convention])
The required inputs are settlement date, maturity date, and payments per year. Google lists frequencies 1, 2, or 4. The optional basis defaults to 0; the choices are 0 US NASD 30/360, 1 Actual/Actual, 2 Actual/360, 3 Actual/365, and 4 European 30/360. Use DATE or another date conversion rather than ambiguous text. Google COUPDAYS reference.
The result describes a whole coupon period. It is not a date, a coupon count, or an interest amount. The function has no principal or coupon-rate argument.
Example: the coupon period around a March settlement
Assume a regular bond pays each January 15 and July 15 and matures January 15, 2028. Enter:
| Cell | Label | Entry |
|---|---|---|
| B2 | Settlement | =DATE(2026,3,15) |
| B3 | Maturity | =DATE(2028,1,15) |
| B4 | Payments per year | 2 |
| B5 | Basis | 1 |
- Enter
=COUPDAYS(B2,B3,B4,B5)in B7. - Display the result as a number.
- The independently expected result is 181 for Actual/Actual.
- Audit the regular calendar interval with
=DATE(2026,7,15)-DATE(2026,1,15), also giving 181.
January 15 and July 15 bracket the March 15 settlement in this explicitly stated schedule. There are 59 actual days before settlement within the period and 122 after it; 59 + 122 equals the full 181-day interval.
Changing only settlement to May 15, 2026 still leaves it inside the same January-to-July coupon period. Its expected COUPDAYS result remains 181, although the elapsed and remaining portions change. That is a useful check that the function is answering a full-period question rather than a countdown question.
Compare actual and 30/360 conventions
Keep the same dates and frequency, but use an explicit basis 0:
=COUPDAYS(B2,B3,B4,0)
The expected 30/360 period length is 180 for this ordinary six-month schedule. Both results can be sensible: 181 is the actual calendar interval; 180 is the length under the stated 30/360 convention. Do not describe a convention count as a literal count of calendar midnights.
A regular quarterly scenario with January, April, July, and October 15 payments puts the same March settlement in January 15 to April 15, 2026. With frequency 4 and basis 1, the expected period length is 90 days. For a regular annual January-15 schedule and frequency 1, the interval January 15, 2026 to January 15, 2027 contains 365 days.
These examples exercise bases 0 and 1. The reviewed help names bases 2 and 3 but does not fully explain every coupon-period denominator detail. Do not assume that ordinary date subtraction is a verified implementation of all basis codes.
Advanced application: make a coupon fraction visible
For an illustrative 1,000 principal, 6% annual coupon, and two equal payments per year, a full coupon is 30. Under this example’s actual-day allocation, the elapsed fraction on March 15 is 59/181.
A transparent calculation is:
=1000*6%/B4*(B2-DATE(2026,1,15))/B7
The expected modeled amount is approximately 9.779006. This is an independently constructed day-fraction allocation, not a claim that COUPDAYS calculates accrued interest or that it replaces a security’s contractual accrual rules.
If you use the 30/360 period denominator of 180, the matching simple non-month-end elapsed convention count is 60, not the 59 actual days. The consistent illustrative allocation is therefore 30*60/180, or 10. Mixing an actual numerator with a convention denominator can produce a number that belongs to neither chosen model.
Errors and troubleshooting
- You expected 122: that is the actual interval from March 15 to July 15 in this schedule, not the whole coupon period.
- The count appears as a date: format the output as a number.
- Different spreadsheets disagree: compare basis, frequency, exact dates, and boundary behavior before rounding either result.
- The schedule crosses February or a coupon date: verify those cases separately. The examples intentionally avoid month ends and settlement exactly on a coupon date.
- Invalid inputs: use documented frequency and basis values and maturity after settlement. Exact native error codes, text coercion, fractional inputs, and all boundary rules were not established from the reviewed reference.
- An irregular first or last coupon exists: this signature supplies no issue or first-coupon date. Do not assume this regular-schedule demonstration captures an irregular period.
Support and references
Google currently documents COUPDAYS. Introduction, retirement, and last functioning version are not established. No other engine’s denominator algorithm is substituted for Google evidence.
Reviewed October 17, 2026: Google COUPDAYS and Google DATE.