We’ve run into a case where XIRR returns significantly different values from Excel and it effecting our ability to search for a target result using a goal seek like algorithm.
When Excel returns a very large (> 1000000 in my test cases but I’m sure it happens in less extreme cases) or very small (2.98023E-09) value, Cells will return a negative value usually in the -0.2 to -0.5 range. It seems like this is happening when a feasible solution is not forthcoming in Cells and it’s giving up. The behavior of Excel is preferable as it trends toward or away from zero in a consistent manner allowing GoalSeek (and therefor our algorithm) to quickly work with our model to find a solution for our target output.
Cells behavior is causing our GoalSeek algorithm to get confused about which way it should search for our output target.
Attached is a spread sheet that demonstrates the problem. Here is the code I used to run it. The problem values are test3test4 and test5.
using Aspose.Cells;
namespace xirr_test;
class Program
{
static void Main(string[] args)
{
var wb = new Workbook("Book1.xlsx");
wb.CalculateFormula();
string[] tests = { "test1", "test2", "test3", "test4", "test5", "test6" };
foreach (string test in tests)
{
var value = wb.Worksheets.Names[test].GetRange().Value;
Console.WriteLine($"{test}: {value}");
}
}
}
@kmonson
By using sample files and code for testing, we can reproduce the issue. It was found that test3, test4, and test5 always result in negative values after calculation.
We have opened the following new ticket(s) in our internal issue tracking system and will deliver their fixes according to the terms mentioned in Free Support Policies.
Issue ID(s): CELLSNET-54231
You can obtain Paid Support Services if you need support on a priority basis, along with the direct access to our Paid Support management team.
@John.He
It looks like your implementation is based off the the XIRR implementation in Open/Libre Office. I managed to reimplement it as an AbstractCalculationEngine and replicate (with the exception of some exotic edge cases) the results, including negative results, that I’d get with Cells.
I was able to deduce that Cells is (probably) failing to replicate the large results of Excel because it’s checking an absolute change of rate between iterations of Newton’s method instead of relative changes to the rate. As the solution gets large it becomes impossible to achieve a rate change less than an absolute Epsilon between iterations. Checking for a relative changes to the rate when the rate is large resolves this issue.
I used the following code based on pytest.approx to resolve the issue in my version of the solver:
private static bool CompareDoubles(double expected, double actual, double rel =
DEFAULT_RELATIVE_TOLERANCE, double abs = DEFAULT_ABSOLUTE_TOLERANCE)
{
if (expected==actual)
{
return true;
}
// Short circut NaN stuff. Never compares true.
// An infinite expected value will cause the later comparisons to always pass because of the relative tolerance.
// If both values were infinite this was handled by the previous comparison.
if(Double.IsNaN(expected) || Double.IsNaN(actual) || Double.IsInfinity(expected))
{
return false;
}
double actual_tolerance = Math.Max(abs, rel * Math.Abs(expected));
return Math.Abs(expected - actual) <= actual_tolerance;
}
@John.He
With regards to the small results, it appears that if Excel cannot find a positive result it seems like it’s just giving up and effectively returning zero. Others have pointed out that Excel may return a near zero value when a solution does not exist.
@kmonson
Thank you for sharing your findings and the suggestion. For the large results, we have improved our calculation engine by checking relative changes to give similar results in your template file. For small results, unfortunately we cannot find a way to get the similar results with ms excel and we have no idea about the rule of ms excel to create such kind of values.
The fix for large results will be included into next official version 23.11 which may be released in this week or early next week. We are afraid we cannot fix the issue of small results currently.
The issues you have found earlier (filed as CELLSNET-54231) have been fixed in this update. This message was posted using Bugs notification tool by johnson.shi