Excel gives me false IRR values|How do I find the real IRR on this investment
 

I have an investment with $100M outflow then an inflow of $230M finally an outflow of $132M

I used Excel IRR "fuction" with default guess of 10% and a guess rate of 15% to get two IRR values shown below

=IRR({-100,230,-132})

10%

=IRR({-100,230,-132},0.15)

20%

But when I showed the IRR values to my CFO, he showed a grin on his face and yelled what non sense you throwing at me. How in the world we have an IRR of 10% and 20% when lost $2,000,000

So how do I find the real IRR that reflects the true return on my company's investment.

Excel gives me false IRR values|How do I find the real IRR on this investment
 

"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.

Excel gives me false IRR values|How do I find the real IRR onthis investment
 

On Wednesday, October 22, 2014 6:26:08 AM UTC+5, joeu2004 wrote:
"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. ;-)

Hello Joeu2004! Thank you very much for your explanation or definition of the IRR - internal rate of return. I passed on this information to my CFO


joeu2004 wrote:
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.

Just about an hour ago my CFO called me to come over to his offices. When I arrived he said he read the biopsy report on the IRR moratorium.

This is what he had to say

"If an investment does not have a payback period then there is no return from the investment. And an IRR and payback period have a one to one relationship whereby an IRR occurs at the payback period."

So is there any way to find the payback period on this investment?

Thank you, answers to this paradox holds high stakes for me as I depend on this "job" to support my family

Excel gives me false IRR values|How do I find the real IRR on this investment
 

"LaWhore Mama" wrote:
Just about an hour ago my CFO called me to come over to his offices.
[....]
This is what he had to say
"If an investment does not have a payback period then there is no
return from the investment. And an IRR and payback period have a
one to one relationship whereby an IRR occurs at the payback period."

So is there any way to find the payback period on this investment?

That is utter nonsense!

And you do not provide sufficient information to calculate either a payback
or discounted payback period.

Google "irr payback period" without quotes. You will find numerous sites
that explain that payback, IRR, average return, MIRR, NPV, etc are all
__different__ methods for making financial decisions.

To keep your job, you need to give your CFO what he wants -- whatever that
is. Frankly, I no longer know what that is.

But do take some time to educate yourself. Here are some interesting sites
to look at.

http://www.investopedia.com/walkthro...back-rule.aspx
http://www.investopedia.com/walkthro...te-return.aspx
http://www.investopedia.com/walkthro...ng-return.aspx

Note the additional links to related "chapters" and "sections" at the top of
the page.

Of course, the internet is not a reliable source of information. (That
might be "news" to some people.) But if you look at some other sites that
Google turns up, you will see a "common theme".

This will be my last response on the subject, since this is a financial
management issue, not an Excel question. Good luck!