Modified Internal Rate of Return Formula: Formula, Examples & Interpretation

Modified Internal Rate of Return (MIRR) is a version of IRR that treats financing and reinvestment separately: it converts negative cash flows at a finance rate and positive cash flows to a terminal value at a reinvestment rate, then finds the single rate that links those values across the project life. This approach is implemented in Excel’s MIRR function Microsoft Support.

Educational note: This article is for educational purposes only and does not constitute financial, investment, legal, or tax advice. Finelo does not recommend any security, strategy, platform, or transaction. Investing and trading involve risk, including possible loss of principal. Verify current rules, fees, product terms, and suitability with official sources or a qualified professional.

Introduction to MIRR

MIRR is a performance metric for capital projects and investments that fixes two common IRR problems: (1) the implicit assumption that interim cash inflows are reinvested at the IRR, and (2) multiple IRR solutions for nonconventional cash flows. By allowing separate finance and reinvestment rates, MIRR gives a single, economically plausible rate of return you can compare to your hurdle rate or cost of capital. The core idea: compound positive cash flows forward at the reinvestment rate, discount negative cash flows at the financing rate, and solve for the consistent annual rate between them Microsoft Support.

Understanding the MIRR Formula

The MIRR calculation has three conceptual parts; understanding each maps directly to the formula.

• Terminal value (FV) of positive cash flows: compound each positive cash flow forward to the project end using the chosen reinvestment rate.
• Present value (PV) of negative cash flows: discount or treat negative flows using the finance (borrowing) rate as appropriate.
• Single-rate link: find the constant rate r that grows PV (of negatives) to the FV (of positives) over n periods.

Mathematically (compact form):

MIRR = (FVpos / -PVneg)^(1/n) − 1

Where:

• FVpos = sum of each positive cash flow compounded to the final period at the reinvestment rate.
• PVneg = sum of each negative cash flow discounted (or simply the absolute value if at time zero) using the finance rate.
• n = number of periods (years).

Excel provides a built-in MIRR function that accepts the cash-flow range, finance rate, and reinvestment rate, implementing this method directly Microsoft Support.

Why the two rates matter

• Finance rate models the realistic cost to fund negative cash flows (e.g., borrowing costs or required return on capital).
• Reinvestment rate reflects where you expect interim positive cash flows can be plausibly reinvested (e.g., a market short-term rate or firm reinvestment policy).

Choosing rates should reflect your firm's cost structure and market expectations; the resulting MIRR is only as meaningful as those inputs.

How to Calculate MIRR

This section walks through a step-by-step numeric example, an Excel quick method, and common calculation pitfalls.

Step-by-step example (worked):

• Project: initial investment −$1,000 at year 0; inflows +$400 at years 1, 2, and 3.
• Finance rate (cost to finance negatives): 5% (annual).
• Reinvestment rate (rate at which inflows are reinvested): 6% (annual).

1. Compound positive cash flows to end of year 3:
   • Year 1 inflow 400 → 400 × 1.06^2 = 400 × 1.1236 = 449.44
   • Year 2 inflow 400 → 400 × 1.06^1 = 424.00
   • Year 3 inflow 400 → 400 × 1.06^0 = 400.00
   • FVpos = 449.44 + 424.00 + 400.00 = 1,273.44
2. PV of negatives at finance rate:
   • PVneg = 1,000 (initial outlay at time 0; finance rate does not change a time‑0 amount)
3. Apply MIRR formula:
   • MIRR = (1,273.44 / 1,000)^(1/3) − 1 ≈ 0.0839 → 8.39%

Interpretation: The project’s MIRR ≈ 8.4% per year given those finance and reinvestment assumptions.

Using Excel quickly:

• Put cash flows in consecutive cells (including the initial negative value).
• Use =MIRR(values, finance_rate, reinvest_rate); Excel computes the result using the same approach described above Microsoft Support.

Common calculation pitfalls

• Mixed sign timing: Ensure cash flows are in the correct order with time 0 first.
• Wrong rate placement: Finance rate and reinvestment rate are not interchangeable.
• Period consistency: Rates and cash-flow timing must use the same period (e.g., both annual).
• Terminal period definition: All inflows are compounded to the same final period (n). If cash flows stop before your intended horizon, you must still compound to the chosen end.

MIRR vs. IRR: Key Differences

Compare the metrics side-by-side to choose the right one for your decision.

Why MIRR often beats IRR for decision-making

• Avoids multiple IRRs for nonconventional flows: because it uses explicit rates for signs, MIRR returns a single rate.
• Uses economically sensible reinvestment: you can set the reinvestment rate to a market or firm rate, rather than the project’s IRR.
• Aligns with financing reality: borrowing costs and opportunity costs can differ, and MIRR lets you model that.

When IRR and MIRR disagree

• If cash flows are conventional (initial negative then positives) and you believe interim proceeds can earn the IRR, IRR and MIRR will be similar.
• If reinvestment prospects differ from IRR, MIRR gives a clearer picture. Use NPV alongside MIRR when choosing between mutually exclusive projects—NPV shows absolute value added.