IRR

[Anonymous]

Hi Michael

I wanted to ask the question for IRR.
My understanding of IRR is that the underlying assumption is the cashflows generated in each period are re-invested at the IRR rate.

This works fine in the case of conventional cashflows i.e. negatives cashflows first and then the positive cashflow on the time line . (T0 – (-10000), T1(-2000),T3-0,T4(+15000))

But would IRR function on Excel give the correct return if there are non-conventional cashflows i.e. you have negative cashflows, positive cashflows,negative cashflows (re-investment), positive cashflows.

I am asking this question in the context of a multi phase model where reversion money from first phase is used for development of second phase and so on till the final phase reversion.

thanks
Pratik

[Spencer Burton]

Hi Pratik,

Spencer here. Fielding this one for Michael.

First off, you are correct that the underlying assumption is that cash flows generated each period are assumed to be re-invested at the IRR rate. While not typical in the real estate industry, if you feel this is overly aggressive you can use the MIRR() function instead of the IRR() or XIRR() function (we’ll discuss both IRR() and XIRR() in course 3a). The MIRR() function offers the option to choose a reinvestment rate for cash flows, different from the IRR rate. For more information, check out this article from McKinsey.

In terms of your question about handling cash flow streams with oscillating positive and negative values. The short answer is, you can’t be confident of an IRR() calculation by Excel when the cash flows change from negative to positive repeatedly. That’s because each time your cash flows change from negative to positive, or from positive to negative, the Excel IRR calculation generates an additional solution .

So what to do when your cash flow stream goes negative to positive to negative repeatedly? I personally ignore IRR in those instances, and instead rely on non time value of money profitability metrics such as Equity Multiple and investment spread (yield-on-cost minus market cap rate).

I can’t speak authoritatively to other solutions to this conundrum, but a quick Google search yields another solution. This article offers MIRR as an alternative for positive-negative-positive cash flows, but I can’t confirm it’s effectiveness as I haven’t used MIRR for this issue.

Great question, by the way!

Spencer

[Author]

Posts