Financial & Accounting
Modified Internal Rate of Return (MIRR): Separate Finance & Reinvestment Rates
Compute the exact Modified Internal Rate of Return (MIRR) by discounting negative cash outflows at your cost of capital and compounding positive cash inflows at realistic treasury reinvestment rates.
Why standard IRR overstates project returns and breaks on mid-life capex
Classic IRR implicitly assumes every intermediate positive cash flow can be reinvested at the project’s own high internal rate of return, and can yield multiple roots when mid-life decommissioning or overhaul costs create negative cash flows after Year 1.
MIRR resolves both flaws in closed form over N = COUNT(flows) - 1 periods: discount all negative cash outflows to t = 0 at financing cost r_f (PV_neg), compound all positive cash inflows forward to t = N at realistic reinvestment rate r_r (FV_pos), and compute MIRR = (FV_pos / PV_neg)^(1 / N) - 1.
| Period (t) | Cash Flow | Treatment in MIRR (r_f = 8%, r_r = 6%) | Present / Terminal Contribution |
|---|---|---|---|
| Year 0 | -$100,000 | Discounted at 8% for 0 yrs | PV_neg += $100,000.00 |
| Year 1 | +$30,000 | Compounded at 6% for 3 yrs | FV_pos += $35,730.48 |
| Year 2 | -$15,000 | Discounted at 8% for 2 yrs | PV_neg += $12,859.91 |
| Year 3 | +$55,000 | Compounded at 6% for 1 yr | FV_pos += $58,300.00 |
| Year 4 | +$65,000 | Compounded at 6% for 0 yrs | FV_pos += $65,000.00 (MIRR = 8.9519%) |
Exact Closed-Form MIRR Formula (SUMPRODUCT & SEQUENCE)
Discounts negative cash flows in B2:B6 at finance rate E2 and compounds positive cash flows at reinvestment rate E3 across 4 annual intervals.
=LET(tIdx, SEQUENCE(5, 1, 0, 1), pvNeg, SUMPRODUCT((B2:B6 < 0) * (-B2:B6) / ((1 + E2) ^ tIdx)), fvPos, SUMPRODUCT((B2:B6 > 0) * B2:B6 * ((1 + E3) ^ (4 - tIdx))), IF(OR(pvNeg <= 0, fvPos <= 0), "Requires Both Sign Flows", ROUND((fvPos / pvNeg) ^ (1 / 4) - 1, 6)))Compare Against Standard IRR on the Same Cash-Flow Vector
Evaluates classic IRR(B2:B6) alongside MIRR so investment committees see the reinvestment-rate gap explicitly.
=ROUND(IRR(B2:B6), 6)Try the example
Use the original values or change them to test your understanding.