IRR Calculation issues

[Mac6668]

I would appreciate it if someone can explain why the IRR function calculation
computes erratically on a MS Excel 2003 SP1 spreadsheet.

If you reduce the numerical value in cell C4 to 77.9 % of its current value,
the worksheet will calculate. At 78% of its value it returns "#NUM! ; and
at 78.2% it calculates; and at 78.3% it returns "#DIV/0!"

 

[Ron Rosenfeld]

I would appreciate it if someone can explain why the IRR function calculation
computes erratically on a MS Excel 2003 SP1 spreadsheet.

If you reduce the numerical value in cell C4 to 77.9 % of its current value,
the worksheet will calculate. At 78% of its value it returns "#NUM! ; and
at 78.2% it calculates; and at 78.3% it returns "#DIV/0!"

Not much detail here.

On a new worksheet, C4 = 0 and IRR(C4) --> #NUM!

78% of C4 is still zero.

Perhaps if you post a bit more detail, someone might be able to give you better
insight into what is going on.


--ron

 

[joeu2004]

I would appreciate it if someone can explain why the IRR
function calculation computes erratically on a MS Excel
2003 SP1 spreadsheet. If you reduce the numerical value
in cell C4 to 77.9 % of its current value, the worksheet will
calculate. At 78% of its value it returns "#NUM! ; and
at 78.2% it calculates; and at 78.3% it returns "#DIV/0!"

Did you read the Help text for the IRR function?

It explains that one reason for #NUM! is that the IRR function
could not determine the rate within 0.00001% (1E-7) within
20 iterations of its internal algorithm.

The solution is to enter an appropriate "guess" (last IRR
argument) other than 10%. Unfortunately, there is no guidance
about what is "appropriate". In another thread, I believe
someone suggested that -10% (or was it -0.1%?) always(?)
works. I am probably wrong about those details. Search for
the thread.

I discovered that one reason for #DIV/0! is that the IRR
function reached a divide-by-zero condition internally before
reaching 20 iterations. Empirically, I learned that the solution
is the same as for #NUM!, namely: enter an appropriate
"guess".

Of course, the might be other possible explanations for the
#NUM! and #DIV/0! errors, which could be your fault. But
since your IRR() formula works some of the time, I am
inclined to suspect that you need an appropriate "guess".

 

[joeu2004]

The solution is to enter an appropriate "guess" (last IRR
argument) other than 10%. Unfortunately, there is no guidance
about what is "appropriate". In another thread, I believe
someone suggested that -10% (or was it -0.1%?) always(?)
works. I am probably wrong about those details. Search for
the thread.

I found it. In another thread in m.p.e.worksheet.functions,
Harlan Grove (11/4/2005 3:19p PST) suggests using -0.9 (-90%).