Zoho Sheet RATE: Periodic Interest, Defaults and Loan Checks
Solve an annuity interest rate in Zoho Sheet with clear payment signs, a documented 10% guess default, annualization and a complete balance check.
Verification status: See the evidence and test scope described in this guide. How formula examples are checked
Quick answer. =RATE(2;-121;210) has an independently calculated result of 10% per period. A 210 loan growing at 10%, with 121 paid at each period-end, closes after two payments: 210×1.1−121=110, then 110×1.1−121=0.
Availability and version history
- Introduced: The original introduction release and date are not established by the checked sources.
- Availability: Currently documented in Zoho Sheet’s rolling web-service help; no numbered release or retirement endpoint is established.
- Scope: English-language web-service documentation. Mobile and offline-client parity is outside the verified scope.
- Checked: October 14, 2026, against the official RATE reference. Documentation and independent annuity models; no native Zoho Sheet test.
Syntax and documented defaults
=RATE(nper; pmt; pv; [fv]; [type]; [guess])
The required inputs are the number of payment periods, the constant payment per period and the present value. The future balance fv defaults to 0. Type defaults to 0, meaning period-end payments; 1 means period-start payments.
Guess is an initial estimate of the periodic rate and defaults to 10%, or 0.1. It is not a fee, a promised return or the annual rate. The short Zoho reference does not state an iteration limit or convergence tolerance, so those must not be borrowed from another spreadsheet’s RATE documentation.
Amounts received and paid have opposite signs. A loan received is positive and repayments are negative from the borrower’s viewpoint. A saver makes negative deposits and receives a positive future amount. Keep one viewpoint throughout the formula.
Six cases with independently known rates
Create a sheet named RateCases. Use A1:G1 for Periods, Payment, Present value, Future value, Type, Guess and Periodic rate. Enter A2:F7 below, put =RATE(A2;B2;C2;D2;E2;F2) in G2, and fill through G7. Format G as a percentage.
| Row | A | B | C | D | E | F | Expected G |
|---|---|---|---|---|---|---|---|
| 2 | 2 | -121 | 210 | 0 | 0 | 10% | 10% |
| 3 | 2 | -110 | 210 | 0 | 1 | 10% | 10% |
| 4 | 2 | -100 | 0 | 210 | 0 | 10% | 10% |
| 5 | 3 | 0 | -1000 | 1331 | 0 | 10% | 10% |
| 6 | 2 | -102.01 | 201 | 0 | 0 | 1% | 1% |
| 7 | 2 | -103.0225 | 201.5 | 0 | 0 | 1.5% | 1.5% |
Row 3 makes its first 110 payment immediately: (210−110)×1.1=110 before the second payment. Row 4 models two deposits of 100, with the first growing to 110 and the second adding 100 at the endpoint. Row 5 is a separate three-period lump-sum model.
In I2 enter =RATE(A2;B2;C2). Its omitted fv, type and guess use the documented defaults and have the row-2 target. In I3 enter =RATE(A3;B3;C3;D3;E3) to omit only guess while preserving beginning-payment timing.
These are rates per input period. If row 6 represents two monthly payments, its 1% is monthly. If row 7 represents two quarterly payments, its 1.5% is quarterly; the corresponding nominal annual rate is 6%, using four quarters per year.
Advanced: solve the rate implied by a payment
Create a separate sheet named ImpliedRate. In A1:F1 put Months, Payment, Initial loan, Guess, Monthly rate and Terminal check. Enter 36 in A2, -250 in B2, 8000 in C2 and 1% in D2.
Put =RATE(A2;B2;C2;0;0;D2) in E2. The independently calculated positive root is approximately 0.00651052510011236, or 0.6510525100% per month. Keep the computed cell’s precision rather than retyping its displayed percentage.
In F2 enter =FV(E2;A2;B2;C2;0). The mathematical ending balance is 0; a tiny numerical residual is possible. This reverse check uses the same payment timing, signs and term.
The 36 payments total 9000, with 1000 above the initial amount in this simplified model. That alone does not identify an annual rate: the declining balance and payment dates matter.
Rebuild all 36 balances
On ImpliedRate, put Month, Opening balance, Interest and Closing balance in A6:D6. Enter 1 in A7 and =$C$2 in B7. Put =B7*$E$2 in C7 and =B7+C7+$B$2 in D7.
Enter A8 =A7+1, B8 =D7, C8 =B8*$E$2 and D8 =B8+C8+$B$2. Fill A8:D8 through row 42, ending at month 36. The absolute references retain the solved rate and fixed negative payment.
| Month | Expected interest, 6 decimals | Expected closing balance, 6 decimals |
|---|---|---|
| 1 | 52.084201 | 7802.084201 |
| 12 | 37.439780 | 5538.094139 |
| 24 | 20.225495 | 2876.809726 |
| 36 | 1.617103 | 0.000000 |
The arithmetic model agrees with RATE’s target equation. Rounding each balance or payment to cents creates a different schedule; the table uses full intermediate precision.
Nominal and effective annual rates
In H2 enter =E2*12; expect approximately 0.0781263012, or 7.81263012% nominal annually. In H3 enter =(1+E2)^12-1; expect approximately 0.0809854492, or 8.09854492% effective annually under monthly compounding.
The first multiplies a periodic rate by twelve. The second compounds it twelve times. Neither includes fees, insurance, taxes or a jurisdiction-specific lending disclosure calculation. RATE itself does not identify those charges or decide which rate convention an agreement uses.
Troubleshooting and zero-rate limits
Check cash-flow signs and timing before changing guess. A reasonable guess near a plausible periodic rate can help an iterative calculation, but the checked page does not guarantee convergence or root uniqueness for every combination.
For the separate case of ten payments of 200 against 2000 with no remaining balance, the annuity model’s rate is zero. This is a mathematical boundary case; the page does not establish every native zero-rate, invalid-period or blank-input behavior. Its generic error table also does not supply a complete financial-domain error specification.
Use NPER to solve the term, PMT for the payment and PV for the initial value. FV provides the terminal-balance check used above. The formulas follow Zoho’s semicolon syntax.