PlanMaker RATE Function: Calculate Interest Per Payment Period
Use the SoftMaker PlanMaker RATE function to estimate loan or annuity interest per period, with correct cash-flow signs, payment timing, and checked examples.
Verification status: See the evidence and test scope described in this guide. How formula examples are checked
RATE estimates interest per payment period. SoftMaker’s =RATE(12*10,-650,60000) example gives approximately 0.455087% per month, equivalent to about 5.46104% nominal annually.
Availability and version history
- Introduced: Documented in the PlanMaker 2016 manual; the original introduction release and date remain unknown.
- Availability: Documented in the 2026 English reference; no final supported-version endpoint is established.
- Scope: Versioned documentation does not establish identical behavior in every desktop, mobile or localized build.
- Checked: October 14, 2026, against the official RATE reference, with independent arithmetic. No native PlanMaker test.
Official syntax, meaning and defaults
=RATE(NPer, Pmt, PV [, FV] [, Type] [, Guess])
| Position | Argument | Required? | Value and meaning |
|---|---|---|---|
| 1 | NPer |
Yes | Numeric total number of payment periods, e.g. 10*12 monthly periods for ten years. |
| 2 | Pmt |
Yes | Signed, fixed payment in each period. In a borrower’s loan model, payments are negative. |
| 3 | PV |
Yes | Signed present amount; borrowing 60,000 is represented by positive 60000 from the borrower’s cash-flow perspective. |
| 4 | FV |
No | Target balance after the final payment; defaults to 0 if omitted. |
| 5 | Type |
No | 0 or omitted: payments at period-end; 1: payments at period-start. |
| 6 | Guess |
No | Numeric starting estimate of rate per period for iterative solving. The SoftMaker manual does not specify the default Guess value. |
The square brackets show optional arguments; do not type the brackets. PlanMaker describes RATE as an iterative calculation. The manual documents a 20-iteration limit and a target accuracy of 0.00001%. Failure to reach that accuracy returns #NUM!; an informed alternative Guess can help. It does not guarantee that a valid root exists, is unique, or is found for every guess. Official syntax and convergence note.
Reproduce the example: infer an interest rate from a loan
Enter these numeric values (without currency symbols) into a new sheet:
| Cell | Label or input |
|---|---|
| A2 | Months |
| B2 | 120 |
| A3 | Payment per month |
| B3 | -650 |
| A4 | Amount borrowed |
| B4 | 60000 |
| A5 | Balance remaining at end |
| B5 | 0 |
| A6 | Payment timing |
| B6 | 0 |
| A8 | Interest per month |
| B8 | =RATE(B2,B3,B4,B5,B6) |
| A9 | Nominal annual rate |
| B9 | =B8*12 |
| A10 | Effective annual rate |
| B10 | =(1+B8)^12-1 |
Format B8:B10 as percentages. Expected B8 is approximately 0.455087%. Expected B9 is approximately 5.46104% and B10 approximately 5.5998% (calculated by compounding). Keep the unrounded monthly rate in the annualization formulas.
Why the negative payment? Borrowing brings funds in, whereas making repayments sends funds out. If you enter all cash flows as positive, the equation may have no relevant solution.
Independent sanity check
At monthly rate r, 120 payments should satisfy the end-of-period cash-flow equation:
60,000*(1+r)^120 - 650*((1+r)^120 - 1)/r = 0
Substituting the independently calculated r ≈ 0.004550867119 leaves an approximately zero residual. This checks the rate against the complete payment stream.
Advanced uses
1. Beginning-of-month payments. With the following worksheet inputs, the Type changes the answer:
| Case | Formula | Independently expected output |
|---|---|---|
| Payments at month-end | =RATE(36,-300,10000) |
About 0.422067% monthly |
| Payments at month-start | =RATE(36,-300,10000,0,1) |
About 0.446902% monthly |
For the same present amount, term and payment, the model can support a higher rate when each repayment arrives at the beginning rather than the end of its period; compare results using the correct Type. Be sure to include 0 for FV when specifying Type.
2. Supply your own starting guess. If a plausible solution is around 0.5% monthly, use =RATE(120,-650,60000,0,0,0.005). The explicit guess 0.005 means 0.5% per period, not 0.5 or 50%. The result should be checked, especially if your cash-flow pattern has more than one potential root.
3. Distinguish nominal from effective annual percentages. =RATE(...)*12 is a nominal annualized percentage for monthly installments. =(1+RATE(...))^12-1 is the corresponding compounded effective annual rate. These are calculations based on a periodic rate; not a promise that a loan’s disclosed APR follows either expression, since fees and conventions can differ.
4. Cross-check a proposed payment. SoftMaker documents PMT for the reverse problem: if you already know the rate, =PMT(annual_rate/12, months, loan_amount) estimates the payment. Using the worksheet above, enter =PMT(B8,B2,B4) in B12. Its independent expected result is -650, matching B3. A mismatch identifies a changed input, timing assumption or rounding issue. Official PMT reference.
Troubleshooting and limits
| Symptom | Likely cause | What to check |
|---|---|---|
#NUM! |
Iterative search failed to converge in SoftMaker’s documented 20-iteration budget, or the requested cash flows may not support a suitable root. | Verify signs, amounts, period count and timing; try an informed Guess such as 0.005. Do not claim a solution merely because the estimate changed. |
| Unexpectedly huge or tiny percentage | You interpreted a per-month result as a per-year result, or used 5 for 5%. |
Use 5%, 0.05, or 5%/12 consistently and apply correct percentage formatting. |
| Error after entering a formula | Missing argument, wrong separator, localized function name or invalid input type. | Confirm English documented order; use the insertion dialog on a non-English installation. |
| Different value from a comparison sheet | Beginning versus end timing, FV, displayed rounding or solver guess may differ. | Compare all inputs and convert periodic rates on the same basis; test both products natively. |
| Rate seems physically unreasonable | Payments may not cover interest/principal as assumed. | Validate the underlying amortization or investment cash flows rather than accepting a plausible-looking percentage. |
RATE models constant periodic payments and one fixed rate. It does not directly model changing rates, irregular payment dates or transaction fees; those need additional cash-flow modeling.
Related guides and workbook portability
PMT solves for a constant payment. NPV discounts a sequence of periodic cash flows with different amounts. Keep rates, periods and cash-flow signs consistent when comparing results. Similar function names in another spreadsheet do not prove identical solver behavior, locale syntax or imported-file recalculation.