IRR function gives false result

[herman]

Hello,
If you enter from left to right in contiguous cells:
-10 -10 -10 +20
the IRR applied to this series gives -18.9%
and that's just fine.

But

10 10 10 -20
also results in -18.9% !!!
which cannot be right as this should obviously be +18.9%.

I encounter similar problems with IRR( where + should be - or vice
versa) in other series.

Does this sound familiar ?
Does anybody know how to cope with this problem?

The series I'm working on are very volatile.
Thank you very much
Herman

 

[A.W.J. Ales]

Herman,

As I understand him the IRR is calculating correct Herman.

The rate is that percentage for which the NPV of the "cashflows" = 0.

20 / (1+i)^3 - 10 / (1+i) ^2 - 10 / (1+i) -10 is (apart from a small
roundingdiffence) equal to 0 and so is
-20 / (1+i)^3 + 10 / (1+i) ^2 + 10 / (1+i) +10 =0

I think you should understand it in this way :

-10 -10 -10 +20 You pay three times 10 and get back 20 ; you
arn -18.9% intrest.

+10 +10 +10 -20 You borrow three times 10 and pay back 20 ; you
pay -18.9% intrest

--
Regards,
Auk Ales

* Please reply to this newsgroup only *
* I will not react on unsolicited e-mails *

 

[Harlan Grove]

If you enter from left to right in contiguous cells:
-10 -10 -10 +20
the IRR applied to this series gives -18.9%
and that's just fine.

But

10 10 10 -20
also results in -18.9% !!!
which cannot be right as this should obviously be +18.9%.

I encounter similar problems with IRR( where + should be - or vice
versa) in other series.

Does this sound familiar ?
Does anybody know how to cope with this problem?

The -18.9% in the second instance is correct. That's one of the reasons IRR just
isn't particularly useful. It's nothing more and nothing less than the interest
rate at which the NPV of the cashflows is zero.

If the NPV of cashflow CF discounted at interest rate i is x, then the NPV of
cashflow -CF discounted at the *same* rate is -x, but, to repeat, the interest
rate would be the same. When the NPV of CF is zero, obviously the NPV of -CF is
also zero.

Changing the sign of the cashflow changes the sign of the NPV but has no effect
whatsoever on the sign or magnitude of the IRR(s). If you're trying to use IRRs
to show that some possible project produces a return at or above some hurdle
rate, you've landed in the IRR trap. It's just not useful for hurdle rate
comparisons, as you've now had a chance to discover. What you need to do is
calculate the NPV of the estimated cashflows at your hurdle rate. If the result
is positive (negative), the project may be considered attractive (unattractive).

For a 12% hurdle rate, the cashflow {-10;-10;-10;+20} has NPV -12.66 (that's
=NPV(0.12,{-10;-10;-10;20})*1.12, which adjusts for the NPV function's
off-by-one discounting) while the cashflow {+10;+10;+10;-20} has NPV +12.66.