Posted 8 September 2017, 3:56 am EST

Vips,

I will try to throw some light on this. First of all I believe both FarPoint Spread and Excel's answers are both correct and incorrect. Let me try to explain. The way that Spread AND Excel calculate IRR is to take the root of the NPV function. Basically if you graph the calculation, the IRR would return where the graph crosses the x coordinate. Sometimes, this can return 2 answers. This is why we have a guess factor to help determine the correct value.

The NPV of your serries of numbers is a positive number. The graph of this equation returns a negative slope for an IRR of 416.08%. This is not the correct IRR. -345.66 has a positive slope which is why we assumed it is the correct answer, but this does not work logically. Thus, bith answers are incorrect. If you use a Financial Calculator, it returns N/A for the numbers you passed. This is what should be passed as the result. Because of this, it is reported as a bug in the FarPoint product, but since our calculation returned a true result, this is marked as a low priority bug at this time.

To see that both answers are correct, you can use the NPV function with the rate set to the IRR. Both IRR rates return 0.

If you have a way of calculating this with better precision, you can create a custom function and override the Evaluate method to perform your own calculation. If you do this and find a way to calculate the numbers you are using correctly to N/A, please share on the forums. I am sure there are others, including ourselves that would like a way to not return false positive values for this function.