irr function

[Michael]

Guys, I really need some help. I am stuck with this problem that should not
happened and in my understanding should happened the other way around. Could
somebody help me. I include some scenario that I can't solved. Thanks

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)

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

 

[Harlan Grove]

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?

 

[joeu2004]

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

Personally, I agree with you. But the fact is, many people (academia
and practitioners alike) simply multiply by 12.

 

[Harlan Grove]

(e-mail address removed) wrote...

Personally, I agree with you. But the fact is, many people (academia
and practitioners alike) simply multiply by 12.

And multiplying by 12 is the first order power series approximation to
the correct answer. When the interest rates are 'small' +/-, it's not
too inaccurate. In this particular case, the difference is between
-9.83% and -9.40%. For a 2-year term, maybe that 0.43% difference is
immaterial, but 5% relative error seems a bit high to me.

Anyway, it doesn't matter because whether the monthly IRR is converted
to a nominal or effective annual rate the result is still unreliable if
not completely worthless.

 

[Jay Petrulis]

Michael wrote...
...

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.

I agree with you regarding the pitfalls of IRR. There is a quick
non-mathematical explanation that might serve better for what is
occurring. Although traditionally used in forward looking capital
investment projects, IRR can provide a backwards look (ex-post)
evaluation of investment performance (a "what happened" analysis vs. a
projected cashflow expectation). In that context, scenario 2 produces
a larger negative because the investment (portfolio/account/asset
class/security, etc.) lost money faster than in scenario one -- it took
the money manager or investor less time to lose money. In that regard,
the results make sense to me.

To extend this a little, put a non-zero amount on the last date so that
the period lengths match. If the cumulative, non-discounted sum is
negative, scenario two's IRR is lower; if positive, scenario two's IRR
is higher. If zero, they are equal at 0 IRR.

However, since it appears that the OP has cashflow data and not interim
valuation data, this investment angle may not be appropriate.