search

peliot / XIRR-and-XNPV

python implementation of Microsoft Excel's XNPV and XIRR
54 stars 23 forks source link

RuntimeError: Failed to converge after 50 iterations #6

Closed arasub closed 5 years ago

arasub commented 6 years ago

Team,

Below is what i have tried and the expected output

01-01-2016 | -100 |   01-02-2016 | 150 |   01-06-2016 | -100 |   01-09-2016 | 200 |     | XIRR | 63.48419

When i used Xirr the below is what i get, Stuck with the below.

Python 2.7.10 (default, Jul 15 2017, 17:16:57) [GCC 4.2.1 Compatible Apple LLVM 9.0.0 (clang-900.0.31)] on darwin Type "help", "copyright", "credits" or "license" for more information.

import financial from datetime import * tab_value = [(datetime(2016, 1, 1), -100), ... (datetime(2016, 1, 2), 150), ... (datetime(2016, 1, 6), -100), ... (datetime(2016, 1, 9), 200)] financial.xirr(tab_value) Traceback (most recent call last): File "", line 1, in File "financial.py", line 87, in xirr return optimize.newton(lambda r: xnpv(r,cashflows),guess) File "/System/Library/Frameworks/Python.framework/Versions/2.7/Extras/lib/python/scipy/optimize/zeros.py", line 161, in newton raise RuntimeError(msg) RuntimeError: Failed to converge after 50 iterations, value is 1.15665911694e+54

Thanks arasu.b

peliot commented 6 years ago

arasu.b, I tested this code to make sure it behaves the same way as MSFT Excel. Like Excel, it fails in certain cases (I won't bore you with a lengthy explanation of why they both fail in those cases). I have yet to find a case where this code fails and Excel does not (I believe the method I am using is very similar if not the same as how Excel does the calculation). Your example fits that pattern. If you put your example into Excel, you will get a #NUM! error (I just tested it). In most cases, you can get it (either my code or Excel) to converge on an answer if you feed it a guess that is in the right ballpark, but I have not been able to make that work in Excel (even knowing the answer you provided), so that approach probably won't work with this code either.

Sorry there isn't a better answer but it is the nature of numerical solvers: they cannot be guaranteed to work in all cases. Since it is impossible to provide a solution in all cases, I have tested against a quality goal of providing the correct solution for any set of cash flows for which Microsoft Excel can provide a correct solution.

Regards, Philip

On Sun, Jun 10, 2018 at 1:47 PM, arasub notifications@github.com wrote:

Team,

Below is what i have tried and the expected output

01-01-2016 | -100 | 01-02-2016 | 150 | 01-06-2016 | -100 | 01-09-2016 | 200 | | XIRR | 63.48419

When i used Xirr the below is what i get, Stuck with the below.

Python 2.7.10 (default, Jul 15 2017, 17:16:57) [GCC 4.2.1 Compatible Apple LLVM 9.0.0 (clang-900.0.31)] on darwin Type "help", "copyright", "credits" or "license" for more information.

import financial from datetime import * tab_value = [(datetime(2016, 1, 1), -100), ... (datetime(2016, 1, 2), 150), ... (datetime(2016, 1, 6), -100), ... (datetime(2016, 1, 9), 200)] financial.xirr(tab_value) Traceback (most recent call last): File "", line 1, in File "financial.py", line 87, in xirr return optimize.newton(lambda r: xnpv(r,cashflows),guess) File "/System/Library/Frameworks/Python.framework/Versions/2.7/ Extras/lib/python/scipy/optimize/zeros.py", line 161, in newton raise RuntimeError(msg) RuntimeError: Failed to converge after 50 iterations, value is 1.15665911694e+54

Thanks arasu.b

— You are receiving this because you are subscribed to this thread. Reply to this email directly, view it on GitHub https://github.com/peliot/XIRR-and-XNPV/issues/6, or mute the thread https://github.com/notifications/unsubscribe-auth/AF14sYoxypFWbNIKNZc2Nk-Ql8B_eP-8ks5t7VvDgaJpZM4Uhzpw .