RATE Function (LibreOffice Calc)

• Introduced: Documented by LibreOffice 26.8.1.1; original introduction not established.
• Availability: Supported in LibreOffice 26.8.1.1, verified against its function registry. This identifies a supported release, not a final working version.
• Scope: Desktop Calc, using the English function name. Availability in other spreadsheet applications is separate.
• Checked: 2026-10-14 — function reference; versioned support. Documentation/source check; not a new native test. Existing example-specific test claims below retain their stated scope.

Validation scope: Native observations below were recorded in saved LibreOffice Calc 25.2.3.2 Linux evidence on 13 October 2026 at 16:15 UTC, using XLSX import and recalculation. This later review verified the script/artifact hashes and all 39 ODS/result cases, independently recomputed 28 numerical assertions and checked annualizations. Calc was not rerun; direct manual entry and other platforms are not certified.

RATE finds the constant interest rate per payment period implied by an opening value, a level recurring payment, a period count and (optionally) a closing value. Typical uses include loan repayment schedules, savings annuities and lease-style payment models.

For example, =RATE(36;-300;9000) gives approximately 0.010207449002719 (1.0207449003% per month) for a 9,000 loan repaid through 36 end-of-month payments of 300. The negative sign on the payment records money leaving the borrower, while the loan proceeds enter as a positive present value.

=RATE(NPer; Pmt; PV [; [FV] [; [Type] [; Guess]]])

LibreOffice Help and OpenFormula agree on the required arguments and optional order. The precise defaults are stated in OASIS OpenFormula 1.4 §6.12.42. Optional arguments are trailing: to provide Type or Guess, provide the preceding FV and Type positions explicitly rather than shifting later arguments to the left. For clarity, use =RATE(12;-100;1000;0;1;0.02) when specifying all six arguments.

Argument separators: These formulas use the usual English Calc ; separator, and decimal point examples such as 0.1. Locale and formula language settings can alter displayed names, separators and decimal marks. Do not copy separators uncritically across regional installations.

Output unit: RATE returns a numerical interest rate per period, not automatically per year. Format as Percent; do not multiply by 100 in the formula and then also apply Percent formatting.

Set up these values:

Format B8 as Percent to display about 1.0207%; underlying result approximately 0.010207449002719. A high initial Guess does not force a high actual result: another native test using a 2% guess returned the same rate within floating-point accuracy.

For an illustrative effective annualization, use:

=(1+B8)^12-1

This returns approximately 12.96055% effective annualized if the monthly rate remains constant for all 12 months. The arithmetic nominal 12× monthly rate, =12*B8, is about 12.24894% and is not the same thing. Neither figure independently accounts for lender fees, insurance or amortization rules that were not included in the cash flows. For a real loan, verify the applicable disclosure and compounding conventions.

Recheck the payment amount

Reverse the calculation by entering:

=PMT(B8;B2;B4;B5;B6)

It should reproduce approximately −300, up to floating-point and displayed rounding. Keep the full calculated rate when feeding it back into PMT. This links directly to the PMT tutorial.

RRI can be evaluated from a simple compound-growth expression. RATE, by contrast, must find a rate r that makes a regular-payment present/future-value equation balance.

For a nonzero rate, OpenFormula 1.4 §6.12.42 defines:

0 = FV + PV*(1+r)^NPer
      + Pmt*(1+r*Type)*((1+r)^NPer-1)/r

At r=0, the continuous limit is:

0 = PV + FV + Pmt*NPer

The function uses iterative numerical methods. Depending on the financial data, the solver may find a rate, struggle to converge or produce a questionable result when the inputs do not describe a meaningful annuity. The Guess argument changes the starting point of the numerical search; it does not change the contractual cash flows.

The model assumes a single consistent payment amount every period and a single fixed rate throughout. For changing payment sizes, irregular dates or more complicated investments, consider IRR or XIRR as appropriate, and check their distinct assumptions.

All results below came from native recalculation in Calc 25.2.3.2 for Linux, with expectations calculated independently from the annuity equation and a numerical root finder.

The official negative-rate example is especially important: if a person receives 900 but repays only 30 in total across three periods, no positive interest rate could balance that schedule. A negative algebraic result is not necessarily a desirable or economically normal loan offer; it reflects the stated cash flows.

Payment timing is material. With the same opening 1,000 and 12 payments of 100, beginning-of-period payments yield a different implied interest rate than end-of-period payments. An accidental Type=1 can silently change your result.

1. Compare two loan offers on a consistent basis

Put the offered term (NPer), actual net amount advanced (PV), fixed recurring payment (Pmt) and end balance (FV) on separate rows. Apply =RATE(NPer;Pmt;PV;FV;0) to each. Ensure all periods are equally long, payment timing agrees, up-front fees have been modeled in the net cash advanced where appropriate, and no payment is silently omitted. Then convert both rates to the same nominal/effective convention before comparison.

This is an analysis technique; it does not substitute for a lender’s legally required cost-of-borrowing disclosure.

2. A zero-payment investment comparison

To find a constant compound rate for an investment without interim payments, a pair of positive balances naturally fits RRI. The signed cash-flow equivalent using RATE is:

=RATE(4;0;-7500;10000)

This produces approximately 7.456993%, matching:

=RRI(4;7500;10000)

Do not use opposite-sign PV in RRI here: the RRI investment-balances model requires a positive starting balance. For RATE, the negative PV marks an outflow when money is invested.

3. Build a safeguarded calculator

Assuming B2=NPer, B3=Pmt, B4=PV, B5=FV, B6=Type, and B7=Guess, a basic guard is:

=IF(OR(NOT(ISNUMBER(B2));B2<=0;AND(B6<>0;B6<>1));NA();RATE(B2;B3;B4;B5;B6;B7))

This rejects nonpositive period counts, non-numeric NPer and timing other than 0/1. For production, extend validation to require numeric all inputs, reasonable Guess, meaningful opposite-signed cash flows and a plausible maximum rate, and verify the returned rate using a reverse PMT/FV relationship. The guard alone does not prove the numerical root is valid.

For fewer optional arguments and a fully repaid loan, the shorter call =RATE(B2;B3;B4) is easier to maintain.

4. Model remaining debt

If payments do not retire the loan completely, represent the remaining terminal liability as a separate signed FV. In one test:

=RATE(24;-400;10000;-2000;0;0.1)

Calc returned approximately 1.052566% per period. Here the -2000 is a terminal liability from the perspective of the borrower cash-flow convention, not an extra payment on every period. Check your sign convention carefully when comparing a bank’s positive outstanding-balance display to the model’s signed terminal cash flow.

Validation over blind trust: The OpenFormula requirement NPer > 0 is stricter than some behavior seen in a single Calc build. Do not treat acceptance of nonsensical values as a permanent Calc contract.

• LibreOffice Calc: 14 numerical assertions for RATE passed on LibreOffice 25.2.3.2, Linux, recalculating an XLSX-origin test workbook; 5 additional diagnostic observations were recorded separately. The core calculation was independently solved via a bracketed numerical root finder, with the resulting annuity residual checked.
• OpenDocument: OASIS OpenFormula 1.4 §6.12.42 documents the calculation, optional defaults, NPer>0 constraint, payment timing and guess semantics. Published standard compliance cannot be inferred solely from a single native test.
• Other versions: The latest official Help page may reflect a newer help build than 25.2.3.2, so observed error codes and coercions are version-specific.
• Other applications/platforms: No independent execution of this workbook in Microsoft Excel, Google Sheets, Apple Numbers, mobile Calc or other spreadsheet software was performed. Do not promise cross-product parity, file-import retention or result equivalence without separate testing.
• Financial limitations: The fixed periodic rate does not automatically incorporate compounding disclosures, taxes, optional repayments, fees, changing rate schedules, dates or balloon terms omitted from inputs. RATE is a mathematical periodic cash-flow solver, not an amortization or regulatory APR certification engine.

Official documentation accessed 2026-10-13. The relevant function definitions, constraints, defaults, and formula are drawn from:

1. LibreOffice Calc Help, Financial Functions Part Three, RATE, RRI, FV and NPER sections: https://help.libreoffice.org/latest/en-US/text/scalc/01/04060118.html — Calc-native argument order, documented end-user examples and use of an iterative guess.
2. LibreOffice Calc Help, Financial Functions Part One, PV section: https://help.libreoffice.org/latest/en-US/text/scalc/01/04060103.html — payment-timing convention, period units and related model context.
3. OASIS OpenDocument 1.4 OpenFormula Part 4 §6.12.42 RATE: https://docs.oasis-open.org/office/OpenDocument/v1.4/os/part4-formula/OpenDocument-v1.4-os-part4-formula.html — formal syntax/defaults, solver equation, NPer>0 requirement.
4. OASIS OpenDocument 1.4 OpenFormula Part 4 §6.12.41 PV and §6.12.44 RRI: same URL — zero-rate limit and related compound-growth model.
5. Native evidence (2026-10-13): saved ODS/CSV outputs, original XLSX and calculation script. Assertions were executed through Calc and compared to independent Python/SciPy equation solutions. Diagnostic anomalies were recorded instead of relabelled as successful assertions.