"LaWhore Mama" wrote:
I have an investment with $100M outflow then an inflow of $230M
finally an outflow of $132M
[....]
How in the world we have an IRR of 10% and 20% when lost
$2,000,000

There is nothing wrong with the Excel IRR implementation in this case.

There is something wrong with your understanding of what the IRR
mathematical concept is.

Very simply, the IRR is the rate causes the sum of the discounted cash (NPV)
to be zero.

There is no doubt that 10% and 20% do just that.

-100 + 230/(1+10%) - 132/(1+10%)^2
= -100 + 209.09 - 109.09 = 0

-100 + 230/(1+20%) - 132/(1+20%)^2
= -100 + 191.67 - 91.67 = 0

I hope even your CFO can understand the arithmetic. ;-)

Why are there are there two IRRs?

Because you have multiple sign changes in the cash flow sequence.

"LaWhore Mama" wrote:
So how do I find the real IRR that reflects the true return on
my company's investment.

That is not the purpose of the mathematical IRR.

In financial analysis, the purpose of the mathematical IRR is to compare
different investment opportunities with cash flows arises at different
times. Hopefully your CFO will remember that from his/her Business 101
class.

The mathematical IRR is the interest rate of a simple investment with one
outflow followed by multiple inflows (or vice versa) because we chose to
define the amortization of such simple cash flows the same way as the
mathematical IRR.

And the same principle works for multiple outfows followed by multiple
inflows (or vice versa).

But that is something of a coincidence. The mathematical properties of the
power series is unstable when there are multiple sign changes (changes cash
flow directions).

What you want to (should want to) find is the "true rate of return", not
necessarily the "real IRR".

There are many definitions of "rate of return" that people use.

Some people put some value in the MIRR definition.

Some peole use the "time weighted" rate of return, which is not "weighted"
at all.

The SEC also has a definition of net return. Sorry, the term escapes me at
the moment.

For me, the simplest method is the time-honored "average return", aka
"simple return".

In your case:

(230 / (100+132) - 1) / 2 = -0.4310% per year (not compounded)

or

(230 / (100+132))^(1/2) - 1 = -0.4320% per year (compounded)

In this case, the results are very close. Nevertheless, I prefer the
non-compounded method.

The division by 2 or exponential 1/2 is because of the 2-year term. Change
2 according for other terms.