Zoho Sheet MIRR: Finance and Reinvestment Rates with Cash-Flow Checks
Calculate modified IRR in Zoho Sheet and rebuild its present-cost and terminal-inflow model, including later outflows and explicit rate assumptions.
Verification status: See the evidence and test scope described in this guide. How formula examples are checked
Quick answer. For -6000, 1500, 2500, 3500, using a 6% finance rate and 4% reinvestment rate per period gives an independently calculated MIRR of approximately 8.776149376% per period. MIRR uses both rate assumptions rather than an initial guess.
Availability and version history
- Introduced: The original introduction release and date are not established by the checked sources.
- Availability: Currently documented in Zoho Sheet’s rolling web-service help; no numbered release or retirement endpoint is established.
- Scope: English-language web-service documentation. Mobile and offline-client parity is outside the verified scope.
- Checked: October 14, 2026, against the official MIRR reference. Documentation and independent periodic cash-flow models; no native Zoho Sheet test.
Syntax and documented requirements
=MIRR(payments; finance_rate; reinvest_rate)
All three arguments are required. Payments is an array or range of periodic cash flows at regular intervals. Zoho explicitly requires at least one negative and one positive payment.
Finance_rate applies to the cost of the invested funds; reinvest_rate applies to reinvested proceeds. Neither is a default guess. Use rates for the same period as the equally spaced cash flows. The examples below use annual periods unless stated otherwise.
The independent model discounts negative flows to the initial date using finance_rate, grows positive flows to the final date using reinvest_rate, and finds the constant rate linking those two totals. Four flows at periods 0, 1, 2 and 3 span three intervals, not four.
First worksheet and a transparent terminal-value check
Create a new sheet named ModifiedReturn. Enter Period and Cash flow in A1:B1, then 0, 1, 2, 3 in A2:A5 and -6000, 1500, 2500, 3500 in B2:B5. Put 6% in E1 and 4% in E2, labeling D1 Finance rate and D2 Reinvestment rate.
In E4 enter =MIRR(B2:B5;E1;E2). The independently expected fraction is approximately 0.0877614937595894, or 8.776149376%.
Use G2:G4 for the three positive cash flows’ values at the final date:
- G2:
=B3*(1+$E$2)^2→ 1622.4 - G3:
=B4*(1+$E$2)→ 2600 - G4:
=B5→ 3500
Put =SUM(G2:G4) in G5; expect 7722.4. Put =(G5/(-B2))^(1/3)-1 in G6; it reproduces the independently expected MIRR.
The negative flow is entirely at period 0, so its present cost is 6000 and no finance-rate discount is needed in this particular dataset. The later positive amounts have different amounts of time to earn the assumed reinvestment rate.
Change one rate assumption at a time
Keep the cash-flow range unchanged. Put Finance rate, Reinvestment rate and MIRR in I1:K1. Enter these scenarios in I2:J4 and =MIRR($B$2:$B$5;I2;J2) in K2, then fill through K4.
| Row | I: Finance rate | J: Reinvestment rate | Expected K, percent |
|---|---|---|---|
| 2 | 6% | 4% | 8.776149376% |
| 3 | 10% | 4% | 8.776149376% |
| 4 | 6% | 8% | 9.832619633% |
Changing only finance_rate has no effect here because the only negative flow occurs at time zero. That is a property of these inputs, not a general statement that MIRR ignores finance_rate. With 8% reinvestment, the positive flows’ terminal total becomes 7949.6.
Advanced: include an additional later outflow
Create a separate sheet named LaterCost. Put Period, Cash flow, Present cost and Terminal inflow in A1:D1. Enter 0, 1, 2, 3 in A2:A5 and -6000, 1500, -1000, 7500 in B2:B5.
Put 6% in G1, 4% in G2 and 3 in G3 for finance rate, reinvestment rate and total intervals. Construct the two sides explicitly:
| Cell | Formula or numeric input | Expected value, 6 decimals |
|---|---|---|
| C2 | =-B2 |
6000.000000 |
| C3 | 0 |
0.000000 |
| C4 | =-B4/(1+$G$1)^A4 |
889.996440 |
| C5 | 0 |
0.000000 |
| D2 | 0 |
0.000000 |
| D3 | =B3*(1+$G$2)^($G$3-A3) |
1622.400000 |
| D4 | 0 |
0.000000 |
| D5 | =B5 |
7500.000000 |
Column C includes only negative flows, converted to positive cost magnitudes; column D includes only positive flows grown to the endpoint. The numeric zeros deliberately exclude the other side rather than replacing missing source data.
In G5 enter =MIRR(B2:B5;G1;G2). In G6 enter =(SUM(D2:D5)/SUM(C2:C5))^(1/G3)-1. Both independent expected results are approximately 9.807002893%.
Put =SUM(C2:C5) in G7; expect approximately 6889.9964400142. Put =SUM(D2:D5) in G8; expect 9122.4. The later 1000 cost now makes finance_rate affect the denominator.
For separate scenarios, put =MIRR(B2:B5;10%;4%) in I2 and =MIRR(B2:B5;6%;8%) in I3. The independently expected rates are 10.146696932% and 10.315020875%, respectively. A higher finance rate reduces the present-value weight of the later outflow in this model; that arithmetic effect does not mean borrowing at a higher rate is economically preferable.
Preserve no-payment periods
Create a separate sheet named Timing. Enter -1000, 0, 1210 in A2:A4 and =MIRR(A2:A4;6%;4%) in C2. The expected result is 10% over the two equally spaced intervals.
Enter -1000, 1210 in E2:E3 and =MIRR(E2:E3;6%;4%) in G2. With only one interval, the expected result becomes 21%. A numeric zero preserves elapsed time; deleting the interval changes the return calculation.
Limits and related functions
Use complete chronological numeric data, both flow signs and consistent periodic units. The short reference does not specify all blank/text handling, exact invalid-rate errors or extreme precision behavior. Do not assume an empty cell preserves a zero-flow period.
The result depends on the stated finance and reinvestment rates. It does not automatically include fees, taxes or uncertain future reinvestment opportunities. IRR solves a different cash-flow root problem without these two explicit rate arguments. PV addresses an annuity, while RATE solves its periodic rate. For irregular dates, consult Zoho’s XIRR documentation.