COUPNUM Function in Google Sheets: Formula, Examples & Troubleshooting

=COUPNUM(settlement, maturity, frequency, [day_count_convention])

Google lists the basis choices as 0 US NASD 30/360, 1 Actual/Actual, 2 Actual/360, 3 Actual/365, and 4 European 30/360. It recommends proper date inputs such as DATE instead of text. Google COUPNUM documentation.

The returned number is a payment count. It is neither years to maturity nor the amount of interest due. A partial interval before the next payment still leads to a future scheduled payment; multiplying a rough year fraction by frequency is not a reliable substitute for enumerating coupons.

Assume a hypothetical security pays January 15 and July 15 each year and matures January 15, 2028. Set up:

1. Enter =COUPNUM(B2,B3,B4,B5) in B7.
2. Format B7 as a number, not a date or percentage.
3. The independently expected count is 4.
4. Reconcile it against these four remaining dates: July 15, 2026; January 15, 2027; July 15, 2027; January 15, 2028.

This model includes the final scheduled coupon on the stated maturity date. It assumes a regular schedule, not a short or long final coupon. The earlier January 15, 2026 coupon is outside the remaining-payment list because it precedes settlement.

If settlement moves to =DATE(2026,7,16), the expected count becomes 3: January 15, 2027; July 15, 2027; and January 15, 2028. The example avoids settlement exactly on a coupon date, whose boundary treatment was not established from the retrieved text.

Suppose, separately, the modeled holding has principal 1,000 and a fixed annual coupon rate of 6%, paid in two equal installments. Put 1000 in E2 and 6% in E3.

Calculate the assumed coupon installment in E5:

=E2*E3/B4

The expected amount is 30. In E6 use =B7*E5, giving an expected 120 of remaining coupons for the original four-payment schedule. If the holding also redeems at the assumed principal 1,000, the combined undiscounted future receipts would be 1,120.

These extra amounts come from explicitly supplied assumptions, not from COUPNUM. They are not present value, profit, yield, accrued interest, or a guarantee that payments will occur. The initial purchase price, discounting, fees, and taxes are absent from this simple arithmetic.

For a frequency comparison with the same settlement and maturity, regular annual January-15 payments give a count of 2. Regular quarterly payments on January, April, July, and October 15 give a count of 8. At the same hypothetical 6% annual coupon on 1,000 principal, those installments would be 60 and 15 respectively, again totaling 120 for these particular future schedules.

• The result looks like a date: apply a number display to the count cell.
• You expected 3.67 instead of 4: COUPNUM is counting scheduled payments, not reporting a fractional number of years or periods.
• The frequency is entered as 6: semiannual means two payments per year, so use 2. The documented accepted choices are 1, 2, and 4.
• A coupon appears missing: enumerate the regular dates and check settlement relative to each one. Confirm the maturity is correct rather than merely rounding a year calculation.
• Irregular schedules: the signature has no issue-date, first-coupon, or custom-payment-list argument. Do not assume it models every irregular bond schedule.
• Unusual or invalid inputs: the reviewed source does not specify exact error codes, fractional frequency handling, or same-day boundary behavior. Use explicit valid dates, an ordered settlement/maturity pair, and documented codes before investigating those cases.

Google currently documents COUPNUM. Its first introduction, retirement date, and last working version remain unknown from the reviewed evidence. Other spreadsheet implementations were not used as proof of Google behavior. This synthetic schedule is an educational worksheet, not an investment recommendation.

Reviewed October 17, 2026: Google COUPNUM and Google DATE.

Related published guides: DATE, SUM, and ROUND.