ExcelBanter - irr function

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?