How do you calculate MIRR for a real estate investment?

A value-add hold with a capital call in year two, worked by hand and with Excel's MIRR function, and why two 15 per cent IRRs are not the same.

Julian R. Sterling · from Real Estate Financial Modeling

MIRR, the modified internal rate of return, is the growth rate between two numbers: the present value of the outflows at a finance rate and the future value of the inflows at a reinvestment rate. On an illustrative five-year value-add hold with a capital call in year two, a 9.53 per cent IRR becomes a 9.03 per cent MIRR at a 6 per cent finance rate and 5 per cent reinvestment rate. In Excel it is one formula: =MIRR(values, finance_rate, reinvest_rate).

Worked in full in Real Estate Financial Modeling by Julian R. Sterling, with every figure reproduced in a free workbook.See the book on Amazon →

The IRR carries two silent assumptions: every distribution is reinvested at the IRR, and every capital call costs the IRR. On a deal that pays steady income and calls equity mid-hold, both are generous. MIRR makes them explicit, gives one answer where the IRR may give two, and ranks deals with different cash flow shapes more honestly. It is also a standard modelling test question.

The assumptions

Illustrative equity cash flows on a value-add office, $.
Year	Event	Cash flow
0	Equity at acquisition	−10,000,000
1	Distribution	500,000
2	Capital call for the refurbishment	−2,000,000
3	Distribution	900,000
4	Distribution	1,000,000
5	Distribution and net sale proceeds	15,500,000
	Profit	5,900,000

Finance rate: 6.00 per cent, the cost of funding the outflows. Reinvestment rate: 5.00 per cent, what distributed cash can earn until the end of the hold. The IRR of these flows, =IRR(B2:B7), is 9.53 per cent, and the multiple on the $12,000,000 invested is 1.49x.

Step 1: present value of the outflows at the finance rate

PV of outflows = Σ outflow_t / (1 + finance rate)^t

10,000,000 + 2,000,000 / 1.06² = 10,000,000 + 1,779,993 = 11,779,993

Step 2: future value of the inflows at the reinvestment rate

Each inflow compounded to the end of year 5 at 5.00%.
Year	Inflow	Factor	Value at year 5
1	500,000	1.2155	607,753
3	900,000	1.1025	992,250
4	1,000,000	1.0500	1,050,000
5	15,500,000	1.0000	15,500,000
Total			18,150,003

Step 3: the growth rate between them

MIRR = (FV of inflows / PV of outflows)^1/n − 1

(18,150,003 / 11,779,993)^1/5 − 1 = 1.5407^0.2 − 1 = 9.03 per cent

In Excel: =MIRR(B2:B7,6%,5%). Excel takes n as the number of cash flows minus one, so the flows must be evenly spaced; for monthly flows the result is a monthly rate to compound to an annual one.

The MIRR is 50 basis points below the IRR because the interim distributions earn 5 per cent rather than 9.53 while waiting, and the capital call is charged at 6 per cent. The gap is modest here because most of the value arrives in one sale cheque at the end, which needs no reinvestment.

What if: the reinvestment rate changes

Same cash flows, finance rate 6.00%.
Reinvestment rate	MIRR
3.00%	8.91%
5.00%	9.03%
8.00%	9.22%
9.53%, the IRR	9.32%
9.53% for both rates	9.53%

Setting both rates equal to the IRR gives the IRR back: that is the IRR's own assumption made visible.

Where MIRR changes the decision: two deals at the same IRR

Take two illustrative five-year deals on $10,000,000 of equity. The first pays $3,000,000 a year for five years and nothing at the end. The second pays nothing until a single $20,322,776 at year 5. Both have an IRR of 15.24 per cent.

Same IRR, different shape, 6% finance and 5% reinvestment.
Deal	IRR	MIRR	Multiple
Even income, no terminal value	15.24%	10.64%	1.50x
Single payout at year 5	15.24%	15.24%	2.03x

The IRR says they are equal. MIRR says the income deal is worth 10.64 per cent unless the investor can redeploy $3,000,000 a year at 15.24 per cent, which few can. The multiple agrees with MIRR. This is why investment committees that rank by IRR alone drift towards short, income-heavy deals.

The common mistakes

Using the IRR as the reinvestment rate. It returns the IRR and defeats the purpose.
Mixing periods. Excel's MIRR treats each value as one period. Feed it quarterly flows and annualise with (1 + q)⁴ − 1, as in how to annualise a quarterly IRR.
Netting the capital call into a distribution. If the year-2 call is netted against income in the same year, the finance rate is never applied to it. Keep calls and distributions on separate lines.

Takeaway

MIRR = (FV of inflows at the reinvestment rate / PV of outflows at the finance rate)^1/n − 1: 9.03 per cent against a 9.53 per cent IRR here. Report it beside the IRR whenever the cash flows change sign mid-hold or income arrives early. For the IRR itself, see how to calculate IRR in Excel for real estate. Real Estate Financial Modeling builds the returns sheet of a complete property model, and the free workbooks for the book include the Meridian House model with every figure live.

Questions readers ask

What is the difference between IRR and MIRR?

IRR implicitly reinvests every interim cash flow at the IRR itself and treats every outflow as financed at the same rate. MIRR replaces both with explicit rates: a finance rate for outflows and a reinvestment rate for inflows. In the example the IRR is 9.53 per cent; with a 6 per cent finance rate and 5 per cent reinvestment the MIRR is 9.03 per cent, 50 basis points lower.

What reinvestment rate should I use for MIRR in real estate?

A rate the investor can actually earn on distributed cash over the hold: a fund's cost of capital, a cash or bond yield, or a conservative expected return on new deals. It should not be the deal's own IRR, which makes MIRR collapse back to the IRR. In the example moving it from 3 to 8 per cent moves the MIRR from 8.91 to 9.22 per cent.

Does MIRR solve the multiple IRR problem?

Yes. Cash flows that change sign more than once, such as a capital call in the middle of a hold or a large final cost, can give more than one IRR or none. MIRR always returns one answer, because it collapses all outflows into one present value and all inflows into one future value, leaving a single growth rate between them.

Read the whole case

This article is one calculation from Real Estate Financial Modeling. The book takes the same case from first principles to the decision, chapter by chapter, and every figure it prints is a live formula in the free companion workbooks.

Get the book on Amazon →Free companion files

Also on Amazon UK · Amazon Germany · Amazon France · Amazon Canada

Also on this site

How do you calculate IRR in Excel for a real estate investment?Put the price with costs in year 0, NOI each year and the net sale in the final year, then =IRR(): an illustrative 20,000,000 office returns 6.84 per cent.
IRR vs XIRR: which should a private equity fund use?XIRR, on actual value dates. The same fund gives 11.33 per cent with XIRR and 12.43 per cent with IRR on annual buckets: 1.10 points of pure date error.
Should you multiply a quarterly IRR by four to annualise it?No: compound it, (1 + q)^4 minus 1. A 3.091 per cent quarterly IRR is 12.95 per cent a year, not 12.36. Times four understates at every positive rate.
Should the exit cap rate apply to forward or trailing NOI?Forward NOI, the year after the sale, as at entry. On a 5-year hold with 3% growth, capping trailing NOI cuts exit value by 3.0% and the IRR by 53 bp.
How do you build a tornado chart for a property IRR?Flex each input alone and sort by IRR swing. Here the exit cap at +/-50 bp swings a 7.28% levered IRR by 6.10 points, ahead of rental growth at 4.43.
How do you calculate the break-even exit yield on a property?Goal Seek the IRR to the target by changing the exit yield. Here the 7 per cent break-even is 6.71 per cent, 21 bp of headroom, not the 10.25 money-back yield.

Reading guide: real estate investing, finance and fund management → · All 1340 articles →

If this book helped, or didn’t, a few lines on Amazon are worth more than they look: they are what the next reader goes on. Write a review. The workbook stays free either way.