Excel IRR function
Posted by John Walker on February 09, 2001 4:43 PM
I have a long string (100) of monthly finacial
investments and returns and need to calculate the IRR.
It keeps returning #DIV/0!. What is the problem? Is
there a limit to the list of numbers? Thanks.
Posted by Celia on February 09, 2001 7:48 PM
John
Somewhere in your formula you must have a divisor that is 0.
Have a look in the help file for causes of #DIV/0.
Celia
Posted by Tim Francis-Wright on February 13, 2001 8:01 AM
It is possible that you have an IRR that is
infinite--one way to diagnose it is by using
NPV(rate, cashflowrange) to see whether higher
and higher discount rates fail to bring the
NPV below zero. For example, the following
cash flow stream yields an IRR of #DIV/0:
10000;1000;10000;-100;10000;-10000;100;100;10000.
For these sorts of streams, applying a fixed
discount rate and using an NPV is a good
alternative to IRR.
[It's also possible to get #NUM! error (regardless
of the guess used) if (a) the cash flow stream
has an infinite IRR but there is a local minimum
in the NPVs; (b) there is no solution to the IRR
equation.]
Hope this helps.
