mirror of
https://github.com/PHPOffice/PhpSpreadsheet.git
synced 2026-08-20 10:40:42 +00:00
oleibman
95d9cc965d
* Refinement for XIRR Fix #2469. The algorithm used for XIRR is known not to converge in some cases, some of which are because the value is legitimately unsolvable; for others, using a different guess might help. The algorithm uses continual guesses at a rate to hopefully converge on the solution. The code in Python package xirr (https://github.com/tarioch/xirr/) suggests a refinement when this rate falls below -1. Adopting this refinement solves the problem for the data in issue 2469 without any adverse effect on the existing tests. My thanks to @tarioch for that refinement. The data from 2469 is, of course, added to the test cases. The user also mentions that an initial guess equal to the actual result doesn't converge either. A test is also added to confirm that that case now works. The test cases are changed to run in the context of a spreadsheet rather than by direct calls to XIRR calculation routine. This revealed some data validation errors which are also cleaned up with this PR. This suggests that other financial tests might benefit from the same change; I will look into that. * More Unit Tests From https://github.com/RayDeCampo/java-xirr/blob/master/src/test/java/org/decampo/xirr/XirrTest.java https://github.com/tarioch/xirr/blob/master/tests/test_math.py Note that there are some cases where the PHP tests do not converge, but the non-PHP tests do. I have confirmed in each of those cases that Excel does not converge, so the PhpSpreadsheet results are good, at least for now. The discrepancies are noted in comments in the test member.
254 lines
8.1 KiB
PHP
254 lines
8.1 KiB
PHP
<?php
|
|
|
|
// result, message, values, dates, guess
|
|
|
|
return [
|
|
[
|
|
'#NUM!',
|
|
'If values and dates contain a different number of values, returns the #NUM! error value',
|
|
[4000, -46000],
|
|
['2015-01-04'],
|
|
0.1,
|
|
],
|
|
[
|
|
'#NUM!',
|
|
'Expects at least one positive cash flow and one negative cash flow; otherwise returns the #NUM! error value',
|
|
[-4000, -46000],
|
|
['2015-01-04', '2019-06-27'],
|
|
0.1,
|
|
],
|
|
[
|
|
'#NUM!',
|
|
'Expects at least one positive cash flow and one negative cash flow; otherwise returns the #NUM! error value',
|
|
[4000, 46000],
|
|
['2015-01-04', '2019-06-27'],
|
|
0.1,
|
|
],
|
|
[
|
|
'#VALUE!',
|
|
'If any number in dates is not a valid date, returns the #VALUE! error value',
|
|
[4000, -46000],
|
|
['2015-01-04', '2019X06-27'],
|
|
0.1,
|
|
],
|
|
[
|
|
'#VALUE!',
|
|
'If any entry in values is not numeric, returns the #VALUE! error value',
|
|
['y', -46000],
|
|
['2015-01-04', '2019-06-27'],
|
|
0.1,
|
|
],
|
|
[
|
|
'#NUM!',
|
|
'If values is not an array, returns the #NUM! error value',
|
|
-46000,
|
|
['2015-01-04', '2019-06-27'],
|
|
0.1,
|
|
],
|
|
[
|
|
'#NUM!',
|
|
'If dates is not an array but values is, returns the #NUM! error value',
|
|
[4000, -46000],
|
|
'2015-01-04',
|
|
0.1,
|
|
],
|
|
[
|
|
'#N/A',
|
|
'If neither dates nor values is an array, returns the #N/A error value',
|
|
4000,
|
|
'2015-01-04',
|
|
0.1,
|
|
],
|
|
[
|
|
'#VALUE!',
|
|
'Return VALUE error if guess is non-numeric',
|
|
[1893.67, 139947.43, 52573.25, 48849.74, 26369.16, -273029.18],
|
|
['2019-06-27', '2019-06-20', '2019-06-21', '2019-06-24', '2019-06-27', '2019-07-27'],
|
|
'XYZ',
|
|
],
|
|
[
|
|
0.137963527441025,
|
|
'Dates can be in any order after all',
|
|
[1893.67, 139947.43, 52573.25, 48849.74, 26369.16, -273029.18],
|
|
['2019-06-27', '2019-06-20', '2019-06-21', '2019-06-24', '2019-06-27', '2019-07-27'],
|
|
0.1,
|
|
],
|
|
[
|
|
0.77868869226873,
|
|
'XIRR calculation #0 is incorrect',
|
|
[4000, -46000],
|
|
['2015-04-01', '2019-06-27'],
|
|
0.1,
|
|
],
|
|
[
|
|
0.137963527441025,
|
|
'XIRR calculation #1 is incorrect',
|
|
[139947.43, 1893.67, 52573.25, 48849.74, 26369.16, -273029.18],
|
|
['2019-06-20', '2019-06-27', '2019-06-21', '2019-06-24', '2019-06-27', '2019-07-27'],
|
|
0.1,
|
|
],
|
|
[
|
|
0.09999999,
|
|
'XIRR calculation #2 is incorrect',
|
|
[100.0, -110.0],
|
|
['2019-06-12', '2020-06-11'],
|
|
0.1,
|
|
],
|
|
[
|
|
3235.159644,
|
|
'XIRR calculation #3 is incorrect',
|
|
[1.0, 1893.67, 52573.25, 48849.74, 26369.16, -273029.18],
|
|
['2019-06-27', '2019-06-27', '2019-06-21', '2019-06-24', '2019-06-27', '2019-07-27'],
|
|
],
|
|
[
|
|
0.15467888,
|
|
'XIRR calculation #4 is incorrect',
|
|
[1893.67, 139947.43, 52573.25, 48849.74, 26369.16, -273029.18],
|
|
['2019-06-20', '2019-06-27', '2019-06-21', '2019-06-24', '2019-06-27', '2019-07-27'],
|
|
],
|
|
[
|
|
-0.197387315,
|
|
'XIRR calculation #5 is incorrect',
|
|
[-100, 20, 40, 25],
|
|
['2010-01-01', '2010-04-01', '2010-10-01', '2011-02-01'],
|
|
],
|
|
[
|
|
3.434984565,
|
|
'XIRR calculation #6 is incorrect',
|
|
[-10000, 2750, 4250, 3250, 2750, 46000],
|
|
['2008-01-01', '2008-03-01', '2008-10-30', '2009-02-15', '2009-04-01', '2009-06-01'],
|
|
],
|
|
[
|
|
0.13796353,
|
|
'Substitute for guess=0',
|
|
[139947.43, 1893.67, 52573.25, 48849.74, 26369.16, -273029.18],
|
|
['2019-06-20', '2019-06-27', '2019-06-21', '2019-06-24', '2019-06-27', '2019-07-27'],
|
|
0.00000,
|
|
],
|
|
[
|
|
0.13796353,
|
|
'Substitute when guess is empty cell',
|
|
[139947.43, 1893.67, 52573.25, 48849.74, 26369.16, -273029.18],
|
|
['2019-06-20', '2019-06-27', '2019-06-21', '2019-06-24', '2019-06-27', '2019-07-27'],
|
|
'C1',
|
|
],
|
|
[
|
|
'#NUM!',
|
|
'Can\'t find a result2 that works after FINANCIAL_MAX_ITERATIONS tries, the #NUM! error value is returned',
|
|
[-10000, 10000, -10000, 5],
|
|
['2010-01-15', '2010-04-16', '2010-07-16', '2010-10-15'],
|
|
],
|
|
[
|
|
-0.642307613,
|
|
'See issue #2469 - non-convergence with initial guess',
|
|
[55600, -51094.83],
|
|
['2021-11-24', '2021-12-24'],
|
|
],
|
|
[
|
|
-0.642307613,
|
|
'See issue #2469 - non-convergence with initial guess equal to correct answer',
|
|
[55600, -51094.83],
|
|
['2021-11-24', '2021-12-24'],
|
|
-0.642307613,
|
|
],
|
|
[
|
|
'exception',
|
|
'Only one argument should cause exception',
|
|
['2021-11-24', '2021-12-24'],
|
|
],
|
|
[
|
|
'exception',
|
|
'No argument should cause exception',
|
|
],
|
|
[
|
|
0,
|
|
'DeCampo One year no growth',
|
|
[-1000, 1000],
|
|
['2010-01-01', '2011-01-01'],
|
|
],
|
|
[
|
|
0.1,
|
|
'DeCampo One year growth',
|
|
[-1000, 1100],
|
|
['2010-01-01', '2011-01-01'],
|
|
],
|
|
[
|
|
-0.1,
|
|
'DeCampo One year decline',
|
|
[-1000, 900],
|
|
['2010-01-01', '2011-01-01'],
|
|
],
|
|
[
|
|
0.1212676,
|
|
'DeCampo vs spreadsheet',
|
|
[-1000, -1000, -1000, -1000, 4300],
|
|
['2010-01-01', '2010-04-01', '2010-07-01', '2010-10-01', '2011-01-01'],
|
|
],
|
|
[
|
|
0.1212676,
|
|
'DeCampo vs spreadsheet reordered',
|
|
[-1000, 4300, -1000, -1000, -1000],
|
|
['2010-10-01', '2011-01-01', '2010-07-01', '2010-01-01', '2010-04-01'],
|
|
],
|
|
[
|
|
2.0,
|
|
'DeCampo Over 100% growth',
|
|
[-1000, 3000],
|
|
['2010-01-01', '2011-01-01'],
|
|
],
|
|
[
|
|
'#NUM!', // -1.0, DeCampo accounts for this case, Excel doesn't
|
|
'DeCampo Total loss one year, agree with Excel not DeCampo',
|
|
[-1000, 0],
|
|
['2010-01-01', '2011-01-01'],
|
|
],
|
|
[
|
|
'#NUM!', // -1.0, DeCampo accounts for this case, Excel doesn't
|
|
'DeCampo Total loss two years, agree with Excel not DeCampo',
|
|
[-1000, 0],
|
|
['2010-01-01', '2012-01-01'],
|
|
],
|
|
[
|
|
0.2504234710540838,
|
|
'DeCampo Readme example',
|
|
[-1000, -2500, -1000, 5050],
|
|
['2016-01-15', '2016-02-08', '2016-04-17', '2016-08-24'],
|
|
],
|
|
[
|
|
0.2126861,
|
|
'DeCampo from nodejs',
|
|
[-10000, 3027.25, 630.68, 2018.2, 1513.62, 1765.89, 4036.33, 4036.33, 1513.62, 1513.62, 2018.16, 1513.62, 1009.08, 1513.62, 1513.62, 1765.89, 1765.89, 22421.55],
|
|
['2000-05-24', '2000-06-05', '2001-04-09', '2004-02-24', '2005-03-18', '2006-02-15', '2007-01-10', '2007-11-14', '2008-12-17', '2010-01-15', '2011-01-14', '2012-02-03', '2013-01-18', '2014-01-24', '2015-01-30', '2016-01-22', '2017-01-20', '2017-06-05'],
|
|
],
|
|
[
|
|
'#NUM!', //-0.7640294,
|
|
'DeCampo issue5a, agree with Excel not DeCampo',
|
|
[-2610, -2589, -5110, -2550, -5086, -2561, -5040, -2552, -2530, 29520],
|
|
['2001-06-22', '2001-07-03', '2001-07-05', '2001-07-06', '2001-07-09', '2001-07-10', '2001-07-12', '2001-07-13', '2001-07-16', '2001-07-17'],
|
|
],
|
|
[
|
|
'#NUM!', //-0.8353404,
|
|
'DeCampo issue5b, agree with Excel not DeCampo',
|
|
[-2610, -2589, -5110, -2550, -5086, -2561, -5040, -2552, -2530, -9840, 38900],
|
|
['2001-06-22', '2001-07-03', '2001-07-05', '2001-07-06', '2001-07-09', '2001-07-10', '2001-07-12', '2001-07-13', '2001-07-16', '2001-07-17', '2001-07-18'],
|
|
],
|
|
[
|
|
412461.6383,
|
|
'Python XIRR test line 20',
|
|
[-100, 1000],
|
|
['2019-12-31', '2020-03-05'],
|
|
],
|
|
[
|
|
1.223853529e16,
|
|
'Python XIRR test line 21',
|
|
[-2236.3994659663, -47.3417585212, -46.52619316339632, 10424.74612565936, -13.077972551952],
|
|
['2017-12-16', '2017-12-26', '2017-12-29', '2017-12-31', '2017-12-20'],
|
|
],
|
|
[
|
|
'#NUM!', //-1,
|
|
'Python XIRR test line 39, agree with Excel not Python',
|
|
[18902, 83600, -5780, -4080, -56780, -2210, -2380, 33975, 23067.98, -1619.57],
|
|
['2016-04-06', '2016-05-04', '2016-05-12', '2017-05-08', '2017-07-03', '2018-05-07', '2019-05-06', '2019-10-01', '2020-03-13', '2020-05-07'],
|
|
],
|
|
];
|