XIRR returns Non Numeric value Encountered #689
Comments
|
+1 Has anyone encountered a different method for XIRR in PHP? This method for XIRR has difficulty with some negative returns. It's very good at most calculations but is not always the same as Excel with negative returns. I found another package in PHP, but it looks like the same formula. Found something in C# as well, but unfortunately refactoring that code is outside my skill set. Other PHP package, same method: https://www.phpclasses.org/package/892-PHP-Financial-functions-with-the-Excel-function-names-.html C#: https://stackoverflow.com/questions/5179866/xirr-calculation |
|
This issue has been automatically marked as stale because it has not had recent activity. It will be closed if no further activity occurs. |
|
The same issue is still there for the following data [ "2022-12-12"=> -20972.36000000 , "2022-12-16"=> 20350.54500000] |
|
Reopening issue. PR #2487 fixed a lot of similar issues with XIRR. However, I agree that it fixes neither the original problem mentioned in this ticket nor the new one just added. |
|
The python package xirr tries the Newton-Raphson method and, if that doesn't converge, tries Brent's method (both these methods come from the scipy package). I have confirmed that these failing examples give the expected result in Python, but they wind up using Brent's method. The PhpSpreadsheet code currently uses only Newton-Raphson. I will have to research if it is possible to come up with Php code for Brent. It could take a while. |
|
Looking again, only the new issue goes through Brent's method in Python. The original does not, so it's a different problem (which may nevertheless be solved via Brent). |
Fix PHPOffice#689. XIRR is calculated by making guesses which are hopefully better with each iteration. It is not guaranteed to succeed for Excel, PhpSpreadsheet, or any other implementation. PhpSpreadsheet uses the Newton-Raphson method for its guesses. So does Python package xirr (https://github.com/tarioch/xirr/), but, if Newton-Raphson fails to converge, Python tries Brent's method as an alternative. Two sets of non-converging data are noted in 689. For both, a solution does converge in Excel. For the first of the problems, a solution converges in Python with Newton-Raphson; but, for the second, a solution converges which requires Brent. For the Java package https://github.com/RayDeCampo/java-xirr on which Python was based, and which uses only Newton-Raphson, a solution converges for the first, and does not converge for the second. To try to match the good results of the others, I added an alternate algorithm if Newton-Raphson fails. Brent's algorithm seems difficult to implement to me. I might have gone there regardless, but I first tried a slightly simpler alternative, bisection. This solved the problem for both of the cases in 689. Perhaps someone will one day report a problem that doesn't converge for Newton-Raphson or bisection, but does for Brent. We can review this decision then. The new code causes 3 changes in the unit test. In all 3 tests, Excel and PhpSpreadsheet had not converged, but Python and/or Java had. I now believe that Python/Java is correct in those cases, and Excel is not. The new code aligns PhpSpreadsheet with Python/Java for those tests. It is, of course, impossible to know when Excel's implementation doesn't converge, so we aren't guaranteed to match its results in those hopefully rare situations.
) Fix #689. XIRR is calculated by making guesses which are hopefully better with each iteration. It is not guaranteed to succeed for Excel, PhpSpreadsheet, or any other implementation. PhpSpreadsheet uses the Newton-Raphson method for its guesses. So does Python package xirr (https://github.com/tarioch/xirr/), but, if Newton-Raphson fails to converge, Python tries Brent's method as an alternative. Two sets of non-converging data are noted in 689. For both, a solution does converge in Excel. For the first of the problems, a solution converges in Python with Newton-Raphson; but, for the second, a solution converges which requires Brent. For the Java package https://github.com/RayDeCampo/java-xirr on which Python was based, and which uses only Newton-Raphson, a solution converges for the first, and does not converge for the second. To try to match the good results of the others, I added an alternate algorithm if Newton-Raphson fails. Brent's algorithm seems difficult to implement to me. I might have gone there regardless, but I first tried a slightly simpler alternative, bisection. This solved the problem for both of the cases in 689. Perhaps someone will one day report a problem that doesn't converge for Newton-Raphson or bisection, but does for Brent. We can review this decision then. The new code causes 3 changes in the unit test. In all 3 tests, Excel and PhpSpreadsheet had not converged, but Python and/or Java had. I now believe that Python/Java is correct in those cases, and Excel is not. The new code aligns PhpSpreadsheet with Python/Java for those tests. It is, of course, impossible to know when Excel's implementation doesn't converge, so we aren't guaranteed to match its results in those hopefully rare situations.
XIRR returning Non Numeric Value Encountered thought the inputs passed are correct. Below is the input passed.
array:2 [▼
0 => -1000000.706
1 => 947003.58
]
array:2 [▼
0 => "2018-09-05"
1 => "2018-09-26"
]
The Expected XIRR Return should be -0.6118824173
The text was updated successfully, but these errors were encountered: