IRR Calculation issues

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!"

On Fri, 11 Nov 2005 11:02:05 -0800, "Mac6668"
<Mac6668@discussions.microsoft.com> wrote:

>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

Mac6668 wrote:
> 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".

I wrote:
> 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%).