Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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!" |
#2
|
|||
|
|||
IRR Calculation issues
On Fri, 11 Nov 2005 11:02:05 -0800, "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!" 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 |
#3
|
|||
|
|||
IRR Calculation issues
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". |
#4
|
|||
|
|||
IRR Calculation issues
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%). |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Calculation Setting in Excel | Excel Discussion (Misc queries) | |||
date calculation issues | Excel Discussion (Misc queries) | |||
date calculation issues | Excel Worksheet Functions | |||
How do I use a rounded calculation result in another calculation? | Excel Worksheet Functions | |||
How do I use a rounded calculation result in another calculation? | Excel Worksheet Functions |