A #DIV/0! With IRR Function

[Sh0t2bts]

Hi Guys,

I have been asked for an explaination from our Finance guys as to why
the IRR function works on one set of data but not on a second set
almost identical:-

I personaly have never used the IRR function so am unsure of how it
works, I did read the help which said that it will ignore zeros and
text cells.

I have 2 sample sets of data the first in Cells A3 to A25, with this
function in Cell A27 =IRR(A3:A25,0.15)
Data for Cells A3:A25
0
3,450
-14,991
-10,956
5,758
3,459
3,574
3,618
3,442
4,452
4,543
4,541
4,646
4,782
4,857
4,876
4,586
4,752
1,280
-146
3
6
4

I get the Result of 16% which is expected.

The second set of Data which is in Cells B3:B25 is:-
0
3450
-5696.25
-3205.64496
3875.768692
1620.176714
1715.67423
1737.986397
1538.93941
2522.570213
2585.826361
2553.744174
2626.034849
2725.876446
2762.244492
2737.946899
2402.134025
2518.01363
727.3978514
-144.3323887
3.337994196
5.722275765
4.291706824

With the Function =IRR(B3:B25,0.15) in Cell B27 With this I get the
result #DIV.0!.
I have found that if I change the value is Cell B5 from -5696.25 to a
lower figure of -10,000 the Function works.

Any Help on this would be great

Many Thanks

Mark

 

[Bernard Liengme]

IRR and NPV are related (see Excel Help) in that NVP( irr, range) should be
zero
Let start with a guess of 10% in D5
And in D6 enter =NPV(D5,B3:B25)
Now use Goal Seek or Solver to make D6 zero by adjusting D5
I get an answer of 187747% which is clearly an unrealised rate of return
(even for Canadian banks)
So it is not surprising that IRR fails to converge on this value
best wishes

 

[joeu2004]

I have been asked for an explaination from our Finance guys as to why
the IRR function works on one set of data but not on a second set
almost identical
[....]
With this I get the result #DIV.0!.

In this case, it means that the IRR cannot be computed. More about
that later. But I want to make the point that in my experience,
sometimes #DIV/0 means simply that the "guess" is not close enough, and
the internal algorithm hit a divide-by-zero error before it reached the
iteration limit.

What does it mean financially or mathematically when the IRR cannot be
computed? Honestly, I cannot say.

How do you tell the difference between the two implications of #DIV/0?
I am not sure. But the following might help.

For your cash flows, I constructed the NPV() for each partial cash flow
back to the beginning. In the case where IRR cannot be computed, as I
incremented the rate systematically, I discovered that the NPV became
increasingly asymptotic. By that, I mean: as the rate increased, more
and more of the later partial cash flows were equal instead of
converging on zero. (As the rate decreased, the partial cash flows
became increasing larger.)

In contrast, for the cash flow where IRR can be computed, as I
increased the rate systematically, the NPV converged to zero or crossed
over to negative, indicating the bounds for the IRR.

I did read the help which said that it will ignore zeros and text cells.

IRR certainly does not ignore zeros; and in fact, the help text does
not say that. It says that IRR ignores empty cells as well as text and
logical values. Zeros can be used to represent equally-spaced gaps in
the cash flow. That is, they count as a period. In contrast, a blank
cell (or one with text) does not count as a period.