How this instrument works
Modified internal rate of return answers a question plain IRR cannot cleanly answer once a project pays out twice instead of once: money leaves at year zero, part of it comes back partway through as an interim cash flow, and the rest arrives at the end as a terminal cash flow. Rather than solving an equation for the single rate that reconciles three dates at once, MIRR collapses the two positive dates into one. The interim amount is carried forward to the final year at a reinvestment rate you supply, added to the terminal payout, and only then is a single compound rate extracted between the initial outflow and that combined future sum.
The forward-value step is where timing does the work: the interim cash flow is raised to the power of years remaining after it arrives, not the whole horizon, so Year the interim cash flow occurs matters as much as the amount itself. A $10,000 distribution landing in year one of a five-year hold compounds for four years at the reinvestment rate; the same $10,000 landing in year four compounds for barely one. A landlord taking a refinance payout mid-hold, a fund reporting a dividend recap ahead of its eventual exit, or a plant manager booking a mid-project asset sale before the line finally closes all face the same shape of problem — two paydays, one number wanted — and this is the arithmetic that turns them into one.
This sheet mirrors the textbook MIRR formula for exactly the schedule it models — one outflow, one interim inflow, one terminal inflow — and no further. The full textbook version also lets negative cash flows appear mid-schedule and discounts each of those back to the present at a separate finance rate; because this instrument only ever takes money out once, at the start, there is nothing to discount on that side, only the reinvestment side above the line does any work. The reinvestment rate itself is an assumption you choose, not a market-quoted figure, and setting it equal to the project's own return quietly reintroduces the exact circularity MIRR exists to remove.
- Enter Initial investment (outflow at year 0), $ — the amount that left the account on day one.
- Enter Interim cash flow, $ and Year the interim cash flow occurs — the mid-project payout and when it landed.
- Enter Terminal cash flow at the final year, $ and Total investment horizon, years — the exit payout and how long the whole holding ran.
- Set Reinvestment rate, % to what the interim cash could actually earn sitting idle, not the project's own return.
- Read Future value of all positive cash flows and Modified internal rate of return, % for the combined, single-rate result.
Worked example — a $50,000 stake with a year-3 payout
Initial investment (outflow at year 0), $ 50,000. Interim cash flow, $ 10,000 lands in Year the interim cash flow occurs = 3. Terminal cash flow at the final year, $ 70,000 arrives at Total investment horizon, years = 5, with Reinvestment rate, % set to 6. The interim $10,000 is carried forward for the two years remaining after year 3, giving 10,000 times 1.06 squared, which is 11,236; adding the $70,000 terminal payout puts Future value of all positive cash flows at $81,236.
Taking the fifth root of 81,236 divided by 50,000 and subtracting one puts Modified internal rate of return, % at 10.193431 — call it 10.19%. Raise the assumed Reinvestment rate, % to 15 instead of 6 and Future value of all positive cash flows climbs to $83,225, nudging Modified internal rate of return, % up to about 10.73%: a more generous reinvestment assumption always pushes the headline number higher, which is exactly why it deserves scrutiny rather than being nudged upward to flatter a result.
Questions
How does MIRR avoid IRR's reinvestment assumption?
Plain IRR implicitly assumes every cash flow it touches, including anything paid out partway through, gets reinvested at the IRR itself — a rate that is often unrealistic for money that is just sitting in an account waiting to be redeployed. This sheet asks you to name Reinvestment rate, % directly and carries only the interim payout forward at that stated rate before computing a single return, so the assumption is visible and adjustable instead of hidden inside the answer.
Why does the timing of the interim cash flow change the answer?
Because the forward-value step compounds the interim amount only for the years left after it arrives — years minus Year the interim cash flow occurs — not the full horizon. An interim payout landing early has more years to compound at the reinvestment rate and adds more to Future value of all positive cash flows than an identical amount landing late in the same project, even though the dollar figure entered is unchanged.
What reinvestment rate should I enter?
That is a judgment call this sheet does not make for you — it only shows what a given rate does to the answer. A conservative choice reflects what idle cash could realistically earn parked somewhere safe until it is needed again; setting the rate to match the project's own return quietly reintroduces the same optimistic assumption plain IRR is criticized for, just applied to one cash flow instead of all of them.
Can this sheet handle more than one interim cash flow?
No — it models exactly three dates: one outflow at year zero, one interim inflow at Year the interim cash flow occurs, and one terminal inflow at the end of Total investment horizon, years. A schedule with several interim distributions, or a second outflow partway through, needs a full multi-period MIRR solver that forward-values every positive flow and discounts every negative one separately; this instrument is deliberately the simpler three-date case.
What happens if the interim cash flow is zero?
The reinvestment rate stops mattering and Modified internal rate of return, % falls back to the same figure a plain two-flow internal-rate-of-return calculation would give — dividing the terminal payout by the initial investment and taking the root over the years, since there is no interim amount left to carry forward. The reinvestment assumption only ever affects the answer when there is real interim cash to reinvest.
Why enter the initial investment as a positive number if it's an outflow?
The field asks for the size of the outflow, not its sign — Initial investment (outflow at year 0), $ must be entered greater than zero, and the calculation treats it as money that left at the start by definition. Keeping every input positive avoids the sign-juggling that trips up multi-cash-flow IRR formulas, where a misplaced minus sign can silently produce a nonsensical result.
References
- SEC Investor.gov — Glossary: Internal rate of return (IRR)
- NYU Stern (Damodaran) — corporate finance and valuation resources
Read this first: This instrument shows arithmetic, not advice. Real offers add fees, taxes and terms that vary by lender and place — verify the figures against your actual paperwork before deciding anything.