RATE in WPS Spreadsheet: Solve a Periodic Annuity Rate
Find a rate for a fixed-payment schedule, check its terminal balance and distinguish periodic, nominal annual and effective annual rates.
Verification status: See the evidence and test scope described in this guide. How formula examples are checked
RATE seeks the rate per period that reconciles a present value, equal periodic payments and a desired final balance. The WPS lesson explicitly warns that a solution may not exist or may not be unique. A returned number should therefore be checked against the schedule it is meant to describe.
Syntax and documented defaults
=RATE(nper, pmt, pv, [fv], [type], [guess])
Nper counts payments. Pmt is the fixed signed payment; pv is the initial present value. Fv is the desired balance after the last payment. WPS documents omitted fv as zero, type 0 or omission as end-period payment, type 1 as beginning-period payment, and omitted guess as 0.1. Its text also discusses omitting pmt when fv is supplied; the original controls do not use that alternative.
All examples here give six arguments explicitly and use end-period payments. Positive initial receipt and negative repayments reflect opposite cash-flow directions. Rate and guess use the same periodic unit as nper.
A schedule with a known root
Enter 2 in B1, −121 in B2 and 210 in B3. Use =RATE(B1,B2,B3,0,0,0.1) in B5.
The independently known rate is 0.1 per period. Check it without invoking a second solver:
- After first-period interest and payment: 210*1.1 − 121 = 110.
- After second-period interest and payment: 110*1.1 − 121 = 0.
Equivalently, the terminal balance equation is 210*(1.1)^2 − 121*1.1 − 121 = 0. This exact residual check explains the root; it does not certify that every application iteration will converge for arbitrary data.
Independent controls
| Formula | Mathematical periodic rate |
|---|---|
=RATE(2,-121,210,0,0,0.1) |
0.1 |
=RATE(2,-72,110,0,0,0.2) |
0.2 |
=RATE(2,-242,420,0,0,0.1) |
0.1 |
=RATE(1,-110,100,0,0,0.1) |
0.1 |
=RATE(1,-120,100,0,0,0.2) |
0.2 |
=RATE(2,-144,220,0,0,0.2) |
0.2 |
These guesses are explicit known-root starting points. These exact roots do not establish convergence for other starting guesses or cash-flow schedules. Scaling both present value and payments leaves these roots unchanged.
Convert units carefully
If the two periods represent years, 0.1 is an annual rate. If they represent months, it is a monthly rate. A monthly rate r corresponds to nominal annual scaling 12r, while the effective annual growth under twelve compounding periods is (1+r)^12 − 1. Those are distinct quantities.
The WPS walkthrough uses 120 monthly payments of −950 against an initial 80000. Its percentage-formatted cells show a monthly value rounded to 1% and the formula multiplied by 12 rounded to 8%. It does not mean the underlying monthly rate is exactly 1%, nor that multiplying the displayed 1% reproduces the annual display. The multiplication describes nominal annual scaling, not effective annual compounding.
Solver limits and reconciliation
The lesson describes a 20-iteration limit and #NUM! if consecutive estimates do not converge within 0.0000001. Treat this as its documented stopping rule, not a universal residual guarantee. A small change between rates is a different numerical condition from a small cash-flow balance error.
If a result is implausible, verify payment signs, units, timing and final balance before trying another guess. Evaluate the resulting balance recurrence independently. Multiple mathematical roots, no solution and unsuitable guesses can complicate interpretation. Fees, taxes, changing payments and irregular dates require a different or expanded model.
PMT solves for the payment given a rate, while RATE reverses that question under its own numerical limitations. CUMIPMT summarizes interest for an already specified schedule. Neither link establishes that every arrangement has a unique or financially meaningful rate.
Availability and evidence scope
The official WPS lesson is dated 2019-05-22 and explicitly labels WPS Office for Windows Personal. Its numeric build is unspecified. This documents the function by that date; it does not establish first introduction, the last functioning build, other-platform parity or a retirement/deprecation history.
The complete lesson and its formula images and walkthrough were inspected on October 18, 2026. Original examples are documentation-derived independent calculations, not WPS or other spreadsheet-engine executions. Solver examples identify mathematical roots and do not certify application convergence or displayed precision. These fictional financial calculations explain a function, not the suitability of a financial product.