Most analysts I work with discover MIRR the day after they discover the multiple IRR problem, and they treat it as the cure. It is not.
Sometimes a real estate cash flow gives us two internal rates of return, and the textbook answer is to switch to MIRR. In this post we will see why that switch does not solve the underlying problem. It only hides it behind two assumptions you cannot defend on a development deal.
We will work through the multiple IRR problem on one cash flow, run three calculations (IRR, XIRR, MIRR), and end with the three-part test I use to decide which result, if any, belongs in the IC paper.
Why the multiple IRR problem keeps showing up on real estate deals
Multiple IRRs are not a textbook curiosity. We see them all the time on real deals.
The cause is always the same: cash flows that change sign more than once. Each sign change opens the door for one more real IRR. This is Descartes' rule of signs, and you can take it as a hard ceiling. It does not guarantee that every additional IRR will appear. It does guarantee that you cannot have more roots than sign changes.
In a corporate finance textbook, multiple sign changes are presented as exotic. In real estate development they are routine. A few of the most common shapes:
- Off-plan settlements with post-handover liabilities. The developer spends in years 0 and 1, takes in a large positive cash flow at handover in year 2 from settlement of off-plan sales, and then spends again in year 3 on defect rectification, retention release, snag remediation, and service-charge subsidy during stabilization.
- Phased land sales with later infrastructure cost-share. A masterplan developer sells parcels in phase 1 (positive), then has to come back and fund spine infrastructure for phase 2 (negative), then sells more land in phase 3 (positive).
- Refinancing recapture. A hold deal that refinances out partway through the hold period: large positive cash event, followed by potential later capex or buyout.
- Promote crystallisation in joint ventures. The promote payout to the sponsor can flip a year's net cash flow when the LP and GP positions are split.
Each of these is a pattern any practising analyst has seen. None of them is exotic. So when Excel hands us back two IRRs and our line manager says "just use MIRR," we owe ourselves a harder look at what that actually means.
What MIRR was invented to do
The intellectual case for MIRR is simple. The standard IRR formula has a hidden assumption built into it: any positive interim cash flow is reinvested at the IRR itself. On a 35% IRR deal, the formula behaves as if the developer is taking each positive inflow and putting it back to work at 35% until the end of the project. Anyone who has actually run a development knows this is not what happens. Distributions go to the LP. The 35% does not live forever.
MIRR replaces this single hidden assumption with two visible ones:
- A finance rate, applied to negative cash flows. It is meant to represent the cost of funding the outflows.
- A reinvestment rate, applied to positive cash flows. It is meant to represent the rate at which the developer can actually reinvest those positives.
In Excel:
=MIRR(values, finance_rate, reinvest_rate)The output is a single number, regardless of how many sign changes the cash flow contains. That is the appeal. And that is also where the trouble starts.
Worked example: same cash flow, two IRRs, one MIRR
Consider a mid-rise residential development with off-plan sales that settle at handover, and post-handover liabilities that come back in year 2.
| Year | Cash flow | What it represents |
|---|---|---|
| 0 | −$10,000,000 | Land, permits, design |
| 1 | +$25,000,000 | Off-plan handover settlement |
| 2 | −$15,000,000 | Defect rectification, retention release, service-charge subsidy |
Sum of cash flows: zero. Sign pattern: minus, plus, minus. Two sign changes, up to two real IRRs.
Drop the flows into Excel and ask for IRR with no guess, then with a higher guess:
=IRR(values) → 0%
=IRR(values, 30%) → 50%Both answers are mathematically correct. NPV is exactly zero at 0% and exactly zero at 50%. They are both real IRRs of this cash flow.
Wasn't that simple? It is.
But neither answer is useful on its own. The 0% IRR is the cash-basis return, money out equals money in, and reading it as the deal IRR understates what the timing of the cash flows actually delivered. The 50% IRR is the time-weighted root that emerges from the same polynomial, and reading it as the deal IRR overstates a project that returned the equity exactly. Both are roots of the same equation. Picking one over the other requires a judgment the formula will not make for us.
Now we run XIRR. XIRR lets us put real dates on the cash flows instead of assuming year-end. On a real handover that concentrates in months 22 to 24 of a development, this matters. The time-weighting changes. But XIRR is solving the same equation as IRR. If IRR has two roots, XIRR has two roots. XIRR fixes when the cash arrives. It does not fix the multiple-roots problem.
Now MIRR. Assume a finance rate of 8% (a reasonable proxy for the cost of debt against the negative outflows) and start with a reinvestment rate of 8% as well:
=MIRR(values, 8%, 8%) → 8.67%One number. Clean. Quotable. Drop it into the IC paper and move on.
Or so the textbook says. Let me change one assumption.
=MIRR(values, 8%, 5%) → 7.16%
=MIRR(values, 8%, 8%) → 8.67%
=MIRR(values, 8%, 12%) → 10.68%Same cash flow. The MIRR moves by 350 basis points just by changing the reinvestment rate from 5% to 12%. That is the entire IC's debate margin on most deals. We are not doing analysis at this point. We are picking the answer we want.
Note that none of the MIRRs we just calculated are equal to either IRR (0% or 50%) of the same cash flow. They are calculations of a different quantity altogether. MIRR is not "the IRR with the multiple-roots problem fixed." It is a separate measure that uses the same letters.
That is the central trick. The multiple-IRR problem looks resolved because we get a single number. But the number we get is not the same number we were trying to find. We have not solved the problem. We have changed the question.
The two MIRR assumptions you cannot defend on a real estate deal
Take the two MIRR inputs one at a time.
The reinvestment rate. MIRR assumes that every positive cash inflow is reinvested at the reinvestment rate from the moment it arrives until the end of the project. On the cash flow above, the $25M handover settlement in year 1 is assumed to earn the reinvest rate for the remaining year of project life.
Note that this is rarely how a real estate development actually works. The $25M does not sit in a treasury account earning a portfolio return. It is distributed to equity holders, who use it for their own next deal, at their own next return, on their own next risk profile, on their own next timing. The reinvest rate inside MIRR is a fiction borrowed from corporate finance, where a firm with a portfolio of projects might genuinely reinvest interim cash at the firm's marginal rate. A single-asset real estate deal does not have that mechanism.
The finance rate. MIRR assumes that every negative cash flow is financed at a single rate from time zero until the cash flow occurs. On a real estate deal, the negative flows in our example are funded by a capital stack: senior debt at one rate, mezzanine at another, sponsor equity at a third, LP equity at a fourth, with a promote and a waterfall sitting on top. The "single finance rate" MIRR asks for does not correspond to any real cost of capital that the deal experiences.
Note that we used 8% for both rates above. There is no defensible way to derive that 8% from the underlying capital structure of the deal. We picked it because it sounds reasonable. We are guessing, and the MIRR result swings with our guess.
So MIRR does produce a single number. The single number is the answer to a question that no investment committee actually asks: "if these flows were financed and reinvested at these two arbitrary rates, what would the geometric average return be?"
What XIRR fixes (and what it doesn't)
A short note before we move on, because XIRR comes up every time MIRR does.
XIRR is genuinely useful, but for a different reason. On a real estate cash flow, money does not move on December 31st. Construction draws hit in May. Handover settlement clears across August, September, and October. Retentions release nine months after that. The annual IRR formula squashes all of this into year-end timing and produces a result that is wrong on time-weighting alone.
XIRR fixes that. Use it whenever you have meaningful date irregularity. On a real estate development, you almost always do.
But XIRR is solving the same equation as IRR. If IRR has two roots for a given cash flow, XIRR has two roots. XIRR fixes when the cash arrives. It does not fix what to do when the math gives you two answers about how it grew.
The cost-of-capital anchor: the rule we already use
In the post on negative IRR I wrote that the IRR closest to the cost of capital is the one we should treat as the true IRR. Let us extend that rule here.
For our worked example, take the project WACC at 9.8%, the same number we used in the project-IRR and equity-IRR post. Readers landing here from there can hold the assumption set constant.
Of the two IRRs:
- 0% is far below WACC. The deal does not earn its cost of capital at this root.
- 50% is far above WACC. The deal would print money at this root.
Neither is close to 9.8%. The cost-of-capital anchor does not pick a winner here, and this is itself information. When neither IRR is anywhere near the cost of capital, the cash flow is telling us that IRR is the wrong metric to lead with on this deal. We need to look elsewhere.
This is also why MIRR feels comforting. A MIRR of 8.67% is near the cost of capital, and it tempts us to declare the deal a marginal go. But that 8.67% was not derived from anything real. It was constructed by assuming a finance rate and a reinvestment rate that do not correspond to anything in the capital stack.
The triangulation test: what to put in the IC paper
In my opinion, on any deal where the IRR calculation is ambiguous, no single number is an honest answer. We triangulate. Three checks before any IRR is the IRR:
1. NPV at the cost of capital. Discount the cash flows at WACC. What does NPV say?
NPV(9.8%) on our worked example = −10 + 25 ÷ 1.098 − 15 ÷ 1.098² = +$0.33M. Slightly positive. The deal returns just over WACC. That is the first honest read of the cash flow.
2. Equity multiple. Total inflows divided by total outflows, on a cash basis, ignoring time. On our example: $25M ÷ ($10M + $15M) = 1.0×.
A 1.0× equity multiple means the developer gets back exactly what they put in. The 50% IRR was technically correct and entirely misleading. Any deal where the IRR is high but the equity multiple is at or near 1.0× is a deal where the timing flatters the result and the cash basis kills it.
3. Coverage in the financed years. If senior debt sits in the capital stack, can the deal service it through the financing window with the cash flows as projected? On our example, the year-2 negative flow of $15M arrives after the year-1 positive of $25M, so coverage is fine. But if the timing flipped, this deal would not be financeable, regardless of any IRR calculation.
The triangulation test for our example produces:
- NPV at WACC: marginally positive (+$0.33M)
- Equity multiple: 1.0×
- Coverage: holds
The honest summary: this deal returns roughly the cost of capital, with no margin for error and no equity multiple to speak of. That is the recommendation. No IRR (neither 0%, nor 50%, nor 8.67% MIRR) would have got us there on its own.
Closing
The job is not to find a single number. The job is to make a defensible recommendation.
MIRR is a useful diagnostic. It tells us how an artificial cash-flow stream would compound under stated assumptions. As a number to put forward to an IC as the return on the deal, it is theatrical accuracy. The kind of false precision that gets discovered later, in front of people whose money is in the deal.
Use IRR. Run XIRR for date timing. Look at MIRR if it helps you see assumption sensitivity. Anchor against the cost of capital. Triangulate against NPV, equity multiple, and coverage. Then write the recommendation in words, not in a single percentage.
