I have a worksheet where the IRR function doesn’t seem to get computed correctly. In my workbook, I update some cells that the IRR function is using for input. After I update the cells, some of the IRR function cells have IsErrorValue set to true instead of the correct value.
If you can open the saved output.xlsx workbook in Excel 2010’s “Protected View”, you can see that the problem cells show #NUM!, and if you open in in normal mode, those IRR values will be calculated correctly.
Attached is the test Visual Studio project / spreadsheet that replicates this problem.
The program will input some CashFlow numbers into D15:N:15, and then the IRR for each year should be calculated in E16:N16. After that, G9 will do a HLOOKUP to find the “correct” IRR to display. The correct values for Cashflow and IRR are shown in rows 21 and 22.