Contact
12 programs
Explore the site

RATE Function in ONLYOFFICE Spreadsheet Editor: Syntax, Examples and Troubleshooting

Financial Intermediate ONLYOFFICE Spreadsheet Editor

Find the periodic interest rate implied by scheduled payments with ONLYOFFICE RATE.

Verification status: See the evidence and test scope described in this guide. How formula examples are checked

RATE finds the interest rate per payment period implied by a fixed payment schedule. A monthly result is a monthly rate, not a ready-made annual APR disclosure.

Availability and version history

  • Introduced: The original introduction release and date are not established by the official sources checked.
  • Availability: Documented in the current ONLYOFFICE Spreadsheet Editor help; no final working-version endpoint is established.
  • Scope: Docs and Desktop Editors release separately. The unversioned reference does not establish support in every older build or mobile client.
  • Checked: October 14, 2026, against the official RATE reference. Documentation check; not a new native test.

Exact documented syntax and argument order

=RATE(nper, pmt, pv, [fv], [type], [guess])

Square brackets show optional arguments; do not type the brackets into a formula.

Position Argument Type, interpretation, required status Omitted default
1 nper Required number of payment periods —
2 pmt Required signed fixed cash payment per period —
3 pv Required signed present value —
4 fv Optional signed future value target 0
5 type Optional payment timing, 0 for period-end or 1 for period-start 0
6 guess Optional numeric initial estimate for the rate per period 10% (0.1)

guess is an initial rate estimate, not the annual APR and not an extra payment. The vendor default is 10% per period if omitted, which can be an unusually distant guess for monthly loans.

Signs: The vendor explains that money paid out is negative and money received is positive. Thus a loan with pv=10000 and negative payments models the borrower receiving $10,000 then making outflows. Interest must be expressed per payment period, not automatically per year. type=0 means end-of-period; type=1 means beginning-of-period.

Set up a reproducible worksheet

Enter the following in a blank worksheet. Keep B2:B7 unchanged and put each result in an unused cell starting at D2. Inputs with a leading equals sign are formulas, not literal text.

Cell Enter exactly Meaning / expected displayed value
B2 =6%/12 Periodic rate = 0.005 (0.5% per month)
B3 60 60 monthly payments
B4 10000 $10,000 received at outset
B5 0 Zero remaining future cash flow
B6 0 Payments due at period end
B7 =PMT(B2,B3,B4,B5,B6) Baseline monthly payment, approximately −193.328015

Worked formulas and expected outputs

Expected numerical results are shown to six decimal places. Rates are decimal fractions: 0.005 means 0.5%.

# Formula to enter in a fresh output cell Expected numeric value What this tests
1 =RATE(B3,B7,B4,0,0,0.005) 0.005000 Infer known 0.5% monthly rate with a nearby explicit starting guess.
2 =RATE(B3,B7,B4) 0.005000 Default fv=0, type=0, guess=10%; result depends on solver convergence.
3 =RATE(60,PMT(6%/12,60,10000,0,1),10000,0,1,0.005) 0.005000 Same loan with payments at the beginning of each month.
4 =RATE(12,-100,1200,0,0,0.005) 0.000000 Zero-interest loan: 12 payments × $100 equal $1,200.
5 =RATE(36,-300,10000,0,0,0.005) 0.004221 Solve the implied monthly rate of a three-year $300 payment schedule.
6 =RATE(20,PMT(6%/4,20,10000),10000,0,0,0.015) 0.015000 6% nominal annual rate with quarterly compounding; result 1.5% per quarter.
7 =RATE(60,-150,0,10000,0,0.005) 0.003515 Implied monthly return on a 60-payment savings plan.
8 =RATE(60,PMT(B2,B3,B4,-2000,0),B4,-2000,0,0.005) 0.005000 Loan balloon/future-value target: invert documented cash-flow equation.

Advanced uses and independent mathematical explanation

Solve an implicit cash-flow equation

RATE is not merely an APR lookup. For a rate r, payments P, count n, present value V, terminal cash flow F and timing t, the fixed-payment identity is 0 = V(1+r)^n + P(1+r×t)×(((1+r)^n−1)/r) + F for r ≠ 0; at r=0, it reduces to 0=V+nP+F. Because RATE solves an implicit nonlinear equation, different initial estimates may lead to different convergence paths; the current vendor page documents guess but does not document algorithm, iteration tolerance or error display. Do not assert a specific error token without a native test.

Derive an approximate annual figure without conflating terminology

If the monthly period rate is 0.005, an annualized nominal percentage is 0.005×12=0.06 or 6%, while an effective annual rate is (1+0.005)^12−1, approximately 6.1678%. These are distinct. Add a separate explanatory cell =RATE(B3,B7,B4,0,0,0.005)*12 for nominal annual rate and =(1+RATE(B3,B7,B4,0,0,0.005))^12-1 for effective annual rate. Keep the unrounded rate in those formulas; round only the displayed percentage.

Verify a recovered rate

The vendor explicitly states guess defaults to 10% per payment period if omitted, which can be far from a 0.5% monthly rate. In uncertain cases, start with a plausible per-period guess such as 0.005, then independently check that plugging the returned rate back into PMT reproduces the stated payment. Put the result of =RATE(B3,B7,B4,0,0,0.005) in D2, then use =PMT(D2,B3,B4,0,0) in D12. The expected payment is approximately -193.328015, matching B7. This checks the returned rate against the original cash flows without assuming a particular solver tolerance.

Savings case

=RATE(60,-150,0,10000,0,0.005) asks what monthly return grows sixty $150 end-period deposits into $10,000. Its independent expected rate is approximately 0.003515. With type=1, the corresponding expected rate is approximately 0.003403, because deposits earn interest for one additional period. This model ignores variable returns, fees and tax.

Troubleshooting and limits — documented versus inferred

Problem / observation Likely issue and actionable response
Returned interest seems huge The result is a rate per payment period; compare against 0.005 for 0.5% per month, not 6% annual directly.
Solver fails or returns an unexpected root RATE uses a supplied or default estimate; try an economically plausible nearby guess and verify residual without asserting vendor-specific numeric tolerances.
Monthly versus annual mismatch Keep nper equal to number of monthly payments, pmt monthly, and multiply inferred periodic rate by 12 only when reporting nominal annual rate.
Impossible sign pattern The chosen cash flows may have no positive solution; confirm a sensible borrower/lender sign convention.
Zero/very small rate instability At zero rate use pv+nper×pmt+fv=0 as the independent benchmark; inspect the returned residual rate before treating it as meaningful interest.
Effective annual rate confused with nominal Compounding changes effective annual return: compute (1+r)^12−1 from a monthly r.

Entry settings and verification

Examples use English function names, commas and numeric decimal rates. The function-entry guide describes the function dialog; Advanced Settings covers formula language and regional formatting. ROUND explains deliberate numeric rounding, and SUM helps build cash-flow checks.

Expected values were independently calculated from the stated financial equations and separately cross-checked. No example was executed in ONLYOFFICE. Fixed-rate models omit fees, taxes, irregular dates and contractual rounding unless those assumptions are added explicitly.