INTRATE Function in Google Sheets: Formula, Examples & Troubleshooting

=INTRATE(buy_date, sell_date, buy_price, sell_price, [day_count_convention])

Basis codes are 0 US NASD 30/360, 1 Actual/Actual, 2 Actual/360, 3 Actual/365, and 4 European 30/360. Build dates with DATE or supply genuine date cells. Google INTRATE reference.

There is no payment-frequency argument or list of intermediate cash flows. A purchase-to-sale model cannot represent a dividend received halfway through the holding period without changing the model.

Enter this hypothetical transaction:

1. In B8 enter =INTRATE(B2,B3,B4,B5,B6).
2. Format B8 as a percentage with six decimal places.
3. The independently expected result is 5.041436%.
4. In B9 enter =B3-B2. Display it as a number: 181 actual days.
5. In B10 enter =(B5-B4)/B4. This is the unannualized holding-period gain, 2.5%.

For this Actual/365 case, check the annualization in B11:

=((B5-B4)/B4)*365/(B3-B2)

B11 should match the modeled result. The 125 gain is measured against 5,000, not against the 5,125 sale proceeds. Reversing the two amounts changes both the gain and its denominator.

Google’s reference uses the phrase “effective interest rate.” Do not infer compound annual growth from that phrase alone. Its published example buys for 2,000 on May 1, 2006, sells for 3,300 on July 30, 2010, and uses basis 0. The published result is 0.1530412034. The independent check ((3300-2000)/2000)*360/1529 matches that result, supporting simple annualization for the illustrated convention. Google’s published INTRATE example.

For the new 181-day example, the separate compound-growth expression =(B5/B4)^(365/(B3-B2))-1 is approximately 5.105508%. That is a different mathematical question. Label the two results separately rather than treating their disagreement as rounding error.

Put basis codes 0, 2, and 3 in D2:D4. In E2 enter =INTRATE($B$2,$B$3,$B$4,$B$5,D2) and fill down.

The dates are ordinary mid-month dates, so the basis-0 audit is 180 convention days over a 360-day year. The actual interval is 181 days; basis 2 and basis 3 use different annual denominators.

If the investment size doubles, =INTRATE(B2,B3,2*B4,2*B5,B6) still has expected rate 5.041436%. Its money gain doubles to 250. This is a useful consistency check for a worksheet where amounts have been converted from individual units to thousands.

• A result near 0.05: Apply percentage formatting; do not multiply the stored formula by 100 as well.
• Different annualized rates: Check the basis before changing purchase or sale amounts.
• Unexpected dates: Use DATE and inspect =B3-B2; text such as 1/7/2026 can be ambiguous.
• Sale on or before purchase: Correct the chronology. A zero-length annualization has no meaningful denominator in this model.
• Zero purchase amount: A return relative to purchase cost cannot be audited by this calculation.
• Intermediate distributions: This two-price function does not accept them. Do not silently add them to the final price and call the timing equivalent.

The reviewed reference does not provide a complete error-code map for invalid inputs. This guide does not claim specific unobserved error tokens, negative-price behavior, or end-of-month convention adjustments.

Google currently documents INTRATE. Its introduction date, earliest functioning version, and any retirement date are unestablished in the reviewed sources. Current documentation does not establish historical-version or other-spreadsheet parity. All formulas here specify the basis to avoid relying on an omitted argument during comparison.

Related: DATE, DAYS360, and POWER.

References checked October 17, 2026: Google function contract and published example.