RATE Function in Google Sheets: Solve for a constant rate per payment period

=RATE(number_of_periods, payment_per_period, present_value, [future_value, end_or_beginning, rate_guess])

The required inputs are the number of payments, payment amount and present value. Future value is optional but its default is not stated in the retrieved reference. Timing defaults to 0 for end-of-period payments; 1 means beginning. Google’s default initial rate guess is 0.1. Every example below supplies all six inputs so the intended model is explicit.

RATE returns a rate for the payment period, not automatically an annual rate. Google’s own example uses a positive present value and negative payments; the scenarios below follow that borrower’s cash-flow perspective.

Receive 100 now and pay 110 at the end of one period, with no remaining balance:

=RATE(1,-110,100,0,0,0.1)

The independently calculated rate is 0.1, displayed as 10% when formatted as a percentage. The cash-flow equation is 100(1+r) − 110 = 0, giving r = 0.1. This algebra establishes the expected root; it does not demonstrate the native solver’s path to it.

Receive 100 now and make two end-of-period payments of 60:

=RATE(2,-60,100,0,0,0.1)

The positive mathematical root is approximately 0.1306623863, or 13.06623863% per period. Let y = 1+r. The balance equation is 100y² − 60y − 60 = 0, so y = (60 + sqrt(27600))/200. Substituting the resulting rate into 60/(1+r) + 60/(1+r)² recovers 100.

The quadratic also has another algebraic root outside this example’s positive-rate interpretation. That is a useful reminder that an equation and an iterative implementation are different things; no universal uniqueness or convergence guarantee follows from this example.

If a solved periodic rate r applies monthly, 12r is its nominal annualized rate and (1+r)^12 − 1 is its effective annual rate under monthly compounding. These are different quantities. For a hypothetical 1% monthly result, they are 12% and approximately 12.68250301%. These arithmetic controls illustrate interpretation; they are not additional observed RATE results.

• A displayed decimal such as 0.1 is 10%, not 0.1%. Apply percentage formatting intentionally.
• Check the payment count, signs, timing and residual balance before changing the guess.
• The guess is an estimate, not a guaranteed answer or a constraint that selects a unique rate.
• The retrieved Google documentation does not establish iteration counts, tolerances or universal convergence. Do not import such promises from Excel.
• A mathematical no-interest balance can be checked without a solver by adding the signed cash flows. No native zero-rate convergence behavior is claimed here.

PV addresses the inverse present-value problem; consult its official documentation before using it. These synthetic examples do not establish a quoted loan’s fees, effective cost or legal terms.

Documented in the rolling Google Sheets service. Documentation reviewed October 19, 2026; an introduction date or numbered release was not established. Examples use English function names, decimal points and comma separators; adapt separators to the sheet’s locale.

The official reference establishes the syntax and function-specific contract. Worked values are original, independently derived expectations, not native Google Sheets observations. No account access, UI screenshot or cross-version execution was performed. Native behavior outside the documented scope remains unverified.