Help with my XIRR please! What am I doing wrong (if anything)?

[HM7]

Thanks for all of this. I will consider EM more in my future underwriting.

Not sure why you say it's a VERY unrealistic example... I used conservative numbers

Lol. 36% return for 16 years, doesn't sound too conservative to me.
If you can actually replicate 36% for 16 years, you should open up a private equity fund and run it.

[bovie]

I bolded above, but this is just what I'm saying: If we have 50-100% returns every single year, HOW can the final - with a sale of $2million or even without! - be only 36-38%?

Easy—CAGR of “only” 36% will double every two years, on average…sounds a bit like “50-100% returns every single year,” no?

Just math.

Regarding the negative versus positive, the calculation is not based on the perspective of your wallet, but the perspective of the investment.

So money in is positive, and money out is negative.

That said, if you flip them for every single cash flow, the resulting return will be identical.

And yes, your numbers are unrealistic and the opposite of conservative.

With that initial investment and those annual cash flows, over the course of 16 years, it’s completely unsurprising that the IRR is minimally changed by a $2M sale.

As stated above, you must also use equity multiple to fully evaluate an investment with periodic cash flows.

IRR on its own is useless, and very easily manipulated.

[HM7]

https://www.oaktreecapital.com/docs/default-source/memos/2006-07-12-you-cant-eat-irr.pdf

If anyone is interested, I think this is a good summary of the benefits and limitations of IRR and other return metrics. More intended for real estate and other private investments. It is a lengthy read and gets technical at times.

Some conclusions from it:

Some conclusions:
1. IRR is superior to the average annual return because it is a dollar-weighted return. It accounts not only for the return during a certain period of time but also for how much money was at stake during that time frame.

2. IRR is better but it has some limitations.

• If the capital is put to work slowly or if a smaller percentage of the capital is mobilized, the IRR will be misleading. Adding the EM will help clarify how successful the returns were and help in comparing them to other funds.
• If a fund exits some of its assets early with high IRRs, the IRR might overstate the success of the fund. You’ll need to add EM here as well. I've seen examples of a fund closing after 10 years quoting an IRR of 20%. But the EM is only 2.0. The reason is that most of the apartments in the fund were exited in years 3-5.

3. The EM is also inadequate by itself. Certain scenarios can be misleading if not used with the EM.
If you have a fund with early exits at high IRRs but future projects (remaining in the fund) have lower IRRs at exit, then EM by itself might be misleading.

4. Bottom line is that you must use both together.
Even with that, the returns do not tell you how much risk was taken and much leverage was used to obtain those results. You would have to add qualitative judgments to assess what the risk was.

[SeattleCPA]

https://www.oaktreecapital.com/docs/...nt-eat-irr.pdf

If anyone is interested, I think this is a good summary of the benefits and limitations of IRR and other return metrics. More intended for real estate and other private investments. It is a lengthy read and gets technical at times.

Some conclusions from it:

Some conclusions:
1. IRR is superior to the average annual return because it is a dollar-weighted return. It accounts not only for the return during a certain period of time but also for how much money was at stake during that time frame.

2. IRR is better but it has some limitations.

• If the capital is put to work slowly or if a smaller percentage of the capital is mobilized, the IRR will be misleading. Adding the EM will help clarify how successful the returns were and help in comparing them to other funds.
• If a fund exits some of its assets early with high IRRs, the IRR might overstate the success of the fund. You’ll need to add EM here as well. I've seen examples of a fund closing after 10 years quoting an IRR of 20%. But the EM is only 2.0. The reason is that most of the apartments in the fund were exited in years 3-5.

3. The EM is also inadequate by itself. Certain scenarios can be misleading if not used with the EM.
If you have a fund with early exits at high IRRs but future projects (remaining in the fund) have lower IRRs at exit, then EM by itself might be misleading.

4. Bottom line is that you must use both together.
Even with that, the returns do not tell you how much risk was taken and much leverage was used to obtain those results. You would have to add qualitative judgments to assess what the risk was.

I said this before, but the tool that solves the IRR's flaws is the NPV measure, aka net present value. Also, just to point them out, other problems exist with IRR too. E.g., you can't always calculate an IRR. And sometimes the IRR formula returns multiple solutions and the solution the function returns depends on the starting guess.