Michael wrote...

....

Is there is an error in the formula or else?

Scenario 1

1 01/01/2006 -5,00E+08

2 01/02/2006 5,00E+07

3 01/03/2006 5,00E+07

4 01/04/2006 5,00E+07

5 01/05/2006 5,00E+07

6 01/06/2006 5,00E+07

7 01/07/2006 5,00E+07

8 01/08/2006 5,00E+07

9 01/09/2006 5,00E+07

10 01/10/2006 0,00E+00

11 01/11/2006 0,00E+00

12 01/12/2006 0,00E+00

13 01/01/2007 -1,00E+07

14 01/02/2007 0,00E+00

15 01/03/2007 0,00E+00

16 01/04/2007 0,00E+00

17 01/05/2007 0,00E+00

18 01/06/2007 0,00E+00

19 01/07/2007 0,00E+00

20 01/08/2007 0,00E+00

21 01/09/2007 0,00E+00

22 01/10/2007 0,00E+00

23 01/11/2007 8,00E+07

24 01/12/2007 0,00E+00 -9,8335% = irr function)

These are monthly cashflows, and you seem to be converting the monthly

IRR of 0.82% into an annual equivalent by multiplying by 12. Better to

convert it into the effective annual rate, (1+IRR)^12-1, -9.40%.

Scenario 2

1 01/01/2006 -5,00E+08

2 01/02/2006 5,00E+07

3 01/03/2006 5,00E+07

4 01/04/2006 5,00E+07

5 01/05/2006 5,00E+07

6 01/06/2006 5,00E+07

7 01/07/2006 5,00E+07

8 01/08/2006 5,00E+07

9 01/09/2006 5,00E+07

10 01/10/2006 0,00E+00

11 01/11/2006 0,00E+00

12 01/12/2006 0,00E+00

13 01/01/2007 -1,00E+07

14 01/02/2007 0,00E+00

15 01/03/2007 0,00E+00

16 01/04/2007 8,00E+07

17 01/05/2007 0,00E+00

18 01/06/2007 0,00E+00

19 01/07/2007 0,00E+00

20 01/08/2007 0,00E+00

21 01/09/2007 0,00E+00

22 01/10/2007 0,00E+00

23 01/11/2007 0,00E+00

24 01/12/2007 0,00E+00 -11,8678% = irr function

Presumably you're wondering why the IRR is worse (larger magnitude neg

fative number) even though the last positive cashflow comes sooner and

all other cashflows remain the same. Simple answer: IRRs almost never

make sense when they're negative. The simple fact that they are

negative should be sufficient to show the cashflows that they model are

either very bad (when the NOMINAL ending cumulative balance is

negative) or very good (when the nominal ending cumulative balance is

positive). For both your cashflows, the nominal ending cumulative

balances are negative, so both represent BAD deals. If your goal is to

determine which is worse, then common sense will be more meaningful

than IRRs.

The technical, mathematical reason scenario 2's IRR is a larger

magnitude negative number is actually because it does happen sooner AND

there are nothing but zeros afterwards. Excel uses an iterative

technique to solve for IRRs, and zeros after the last nonzero cashflow

are effectively ignored. That means that scenario 1 is effectively a 23

period cashflow and scenario 2 a 16 period cashflow. The cumulative

loss is realized earlier in scenario 2, so the underlying polynomial

Excel needs to solve is lower order, and that lead to the larger

magnitude negative number for scenario 2's IRR.

You also have multiple sign changes, and that adds to the difficulties.

But this distracts from the fundamental fact: IRR is unreliable, and

it's NEVER an ordinal measure. You can't use IRRs to rank cashflows in

any sensible way unless you're dealing with bond-equivalent cashflows:

an initial negative cashflow followed by nothing but positive

cashflows. Even then it's unreliable. Consider -1000 at time 0 and +150

at the next 11 periods vs -1000 at time 0 and +2696.72 11 periods

later. Both have the same IRR. Which is riskier?

In almost all cases in which discounted cashflow analysis is used, the

cashflows are uncertain, but those closer to the beginning tend to be

less variable than those closer to the end. In the two examples above,

the 11 periods of 150 payback are much less risky than the single

2696.72 payback 11 periods after the initial outflow. IRR fails to rank

these two cashflows by riskiness. All it says is that if you IGNORE the

underlying riskiness, they offer the same interest rate-equivalent

payback. How's that useful?