Excel's built-in MIRR function makes the calculation straightforward — but there
are a few gotchas worth knowing before you use it in a model.
Excel MIRR syntax
=MIRR(values, finance_rate, reinvest_rate)
values: a range of cells containing the cash flows (Year 0 through Year N). The first value must be negative (initial investment).
finance_rate: the interest rate you pay on negative cash flows (cost of borrowing). Use your WACC or cost of debt.
reinvest_rate: the interest rate you earn on reinvested positive cash flows. Use a conservative rate — Treasury yield, savings rate, or opportunity cost.
Example
Cash flows: Year 0 = −$100,000, Year 1–4 = $30,000 each. Finance rate = 10%, Reinvest rate = 8%.
=MIRR({-100000,30000,30000,30000,30000}, 10%, 8%)
= 12.66%
With standard IRR: =IRR({-100000,30000,30000,30000,30000}) = 7.71%
MIRR is lower here because the reinvest rate (8%) is lower than the IRR (7.71%... wait, that seems backwards. Let me correct: MIRR can be higher OR lower than IRR depending on the rates used. When reinvest rate < IRR, MIRR < IRR.)
Common mistakes
- Not including Year 0: the values array must include the initial outflow at position 0. If you start from Year 1, the function gives wrong results.
- Using the same rate for both arguments: this reduces MIRR to a simple IRR approximation and defeats the purpose.
- Mixing periods: MIRR assumes annual periods unless you adjust. For monthly cash flows, divide annual rates by 12.
Use the MIRR calculator for a web-based version without needing Excel.