MIRR Function in ONLYOFFICE Spreadsheet Editor: Syntax, Worked Examples and Limitations
Step-by-step ONLYOFFICE MIRR function tutorial with verified vendor syntax, financial worksheet examples, independent numerical expectations and troubleshooting.
Verification status: See the evidence and test scope described in this guide. How formula examples are checked
MIRR calculates a modified return using separate financing and reinvestment assumptions. It is useful when you want those assumptions stated explicitly rather than relying on an IRR root.
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 MIRR reference. Documentation check; not a new native test.
Exact vendor syntax and argument order
=MIRR(values, finance_rate, reinvest_rate)
| Position | Argument | Type and role | Omission/default |
|---|---|---|---|
| 1 | values |
Required range/array of periodic cash flows with at least one positive and at least one negative | No default |
| 2 | finance_rate |
Required numeric financing rate per cash-flow period | No default |
| 3 | reinvest_rate |
Required numeric reinvestment rate per cash-flow period | No default |
All three arguments are required. The cash-flow range must include at least one positive and one negative value; there are no documented optional defaults.
Mathematical explanation — independent from runtime claims
For n≥2 equally spaced cash flows Ct (time zero through n−1), the independent model brings positive flows forward to the last period using the reinvestment rate, and discounts negative flows to time zero using the financing rate. Specifically, FVpositive=Σ max(Ct,0)×(1+reinvest_rate)^(n−1−t) and PVnegative=Σ max(−Ct,0)/(1+finance_rate)^t. The independent modified rate is (FVpositive/PVnegative)^(1/(n−1)) − 1. It discounts later investments back to time zero and compounds receipts forward to the final period.
Set up the cash-flow ranges
Use a blank worksheet. A semicolon in the setup table separates consecutive cells; do not type the whole sequence into one cell. Keep inputs numeric and put results in column M, starting at M2, without overwriting any input range.
| Cell or range | Enter this value (or consecutive cells) | Meaning |
|---|---|---|
B2:B5 |
-1000; 200; 300; 700 |
Initial outlay plus three payment periods |
C2:C6 |
-10000; 0; 3000; -500; 9000 |
Initial outlay plus four periods, including an additional outlay |
D2:D5 |
-5000; 0; 0; 6655 |
Lump-sum receipt at the end |
B8 |
8% |
Financing rate: 0.08 per period |
B9 |
5% |
Reinvestment rate: 0.05 per period |
C8 |
12% |
Alternative financing rate |
C9 |
8% |
Alternative reinvestment rate |
Worked examples
Expected rates below are decimal fractions shown to six places, so 0.100000 means 10%.
| # | Enter in an unused output cell | Expected model result | What this demonstrates |
|---|---|---|---|
| 1 | =MIRR(B2:B5,B8,B9) |
0.073036 |
Baseline financing/reinvestment assumptions |
| 2 | =MIRR(B2:B5,0,0) |
0.062659 |
Both rates zero |
| 3 | =MIRR(B2:B5,C8,C9) |
0.079305 |
Higher rates; only reinvestment applies without later negative flows |
| 4 | =MIRR(C2:C6,B8,B9) |
0.043077 |
Additional negative flow affects financing |
| 5 | =MIRR(C2:C6,C8,C9) |
0.048151 |
Compare both rates with additional investment |
| 6 | =MIRR(D2:D5,B8,B9) |
0.100000 |
Only final receipt |
| 7 | =MIRR(D2:D5,10%,20%) |
0.100000 |
Rates have no effect on zero interim flows |
Advanced uses and audit-ready patterns
Build the positive-future-value and negative-present-value audit
For B2:B5 the independent positive future value at period three is 200×(1.05)^2 + 300×1.05 + 700 = 1235.50. The negative present value is 1000, because the only cash outflow occurs at period zero. The independent MIRR is therefore (1235.50/1000)^(1/3)−1. Use =MIRR(B2:B5,B8,B9) in M2, and compare it to this separately calculated expectation.
Understand when financing matters
In the B case, all negative money is at time zero, so changing finance_rate alone does not change the independent result: there are no later negative flows to discount. The C dataset includes an additional negative cash flow later. For it, finance_rate changes the negative present-value denominator. This is a valuable diagnostic if a spreadsheet result seems insensitive to one rate input.
Preserve the period count with zero cash flows
For D2:D5, both zero interim receipts remain real periods. Deleting them changes the horizon from three periods to one: the rate would become 33.1% rather than 10%. For projects with dates that are not equally spaced, use date-aware analysis rather than treating adjacent rows as equal intervals.
Compare MIRR with IRR, NPV and XIRR
IRR finds an NPV root and has a documented optional numerical guess. NPV requires a discount rate you choose. XIRR is date-based. MIRR instead asks the analyst to supply two economic assumptions, each expressed in the same per-period units as the cash flows. They answer related but different questions.
Scenario sensitivity and rate units
Use an assumption matrix with alternative finance and reinvestment rates rather than changing the cash-flow ranges in each scenario. Monthly flows require monthly rates, not annual rates inserted directly. To convert an annual effective rate mathematically to an equivalent monthly rate, use (1+annual_rate)^(1/12)−1; the vendor page does not supply a built-in period-conversion parameter.
Economic limitations
MIRR is not a guarantee of a future return. Its usefulness depends on the realism of the rates and whether all cash flows are included. Do not use it as a standalone project-selection criterion when projects differ materially in size, dates, financing constraints or risk. Present an absolute value measure at a chosen hurdle rate as well.
Troubleshooting: limits, ambiguous behaviour and common errors
| Symptom or pitfall | What to verify |
|---|---|
| Missing positive or negative cash flows | The official values argument requires at least one of each sign. Do not fabricate an ONLYOFFICE error string; validate the data first. |
| A rate fails to affect the result | With all negatives at t=0, the mathematical result can be insensitive to financing rate. Use the C scenario to see a later negative flow. |
| Formula produces an unexpected level | Ensure rates are decimal percentages (8% = 0.08), not 8 for 8%. |
| Project length differs between worksheets | Zero-valued rows still occupy periods in the independent model. Maintain the same timetable. |
| Comparison with IRR differs | MIRR explicitly uses two rates; IRR solves a different zero-NPV condition. Differences alone do not prove a spreadsheet defect. |
| Formula cannot be evaluated | Recheck English function name, three required arguments, commas or localized separators, signs and version availability. |
The short official reference does not specify every invalid-input error token, coercion rule or solver limit. Check numeric inputs and the actual error explanation rather than assuming another spreadsheet’s behavior.
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.