tarioch / xirr Goto Github PK

valuesPerDate = {
        date(2011, 7, 1): 10000,
        date(2014, 7, 1): -1
        }
print(xirr(valuesPerDate))

produces:

Traceback (most recent call last):
  File "cmath.py", line 45, in xirr
    result = scipy.optimize.newton(lambda r: xnpv(valuesPerDate, r), 0, maxiter=500)
  File "C:\Users\myken\Anaconda3\lib\site-packages\scipy\optimize\zeros.py", line 340, in newton
    raise RuntimeError(msg)
RuntimeError: Tolerance of -4.2138527602011363e+30 reached. Failed to converge after 5 iterations, value is -4.2138527602012264e+30.

During handling of the above exception, another exception occurred:

Traceback (most recent call last):
  File "cmath.py", line 80, in <module>
    print(xirr(valuesPerDate))
  File "cmath.py", line 47, in xirr
    result = scipy.optimize.brentq(lambda r: xnpv(valuesPerDate, r), -1.0, 1e20)
  File "C:\Users\myken\Anaconda3\lib\site-packages\scipy\optimize\zeros.py", line 775, in brentq
    r = _zeros._brentq(f, a, b, xtol, rtol, maxiter, args, full_output, disp)
ValueError: f(a) and f(b) must have different signs

While Google Sheets produces a result:

When I flip the signs (as in the second column of the Google Sheet), I also get the correct result with the library:

valuesPerDate = {
        date(2011, 7, 1): -10000,
        date(2014, 7, 1): 1
        }
print(xirr(valuesPerDate))
-0.9534539092750439

Any idea what is happening here? (I didn't dive into the mathematics.) Is it a safe approach to just try and flip the signs in case the lib produces a RuntimeError?

Referencing:

1. There's one ")" too many at the end.
2. Leading 0s (as in "03") produce a syntax error in Python 3.

Thanks for the lib!

Should the "dict" and "list" specifications on the type hints be replaced by "Dict" and "List" from the typing module? Python is complaining about these "list" and "dict" hints...

Here's an interesting example of numbers:

Values:

[5046.0, 5037.299999999999, 4995.25, 5795.5, -1085.6, 4998.0, 4557.8, 4815.0, 4928.0, -2197.05, 5424.0, -2565.0, -2872.8, 10085.0, 9500.0, 9976.8, 14880.000000000002, -6094.7, 19522.359999999997, 18035.0, 10477.44]

Dates:

[Timestamp('2015-08-03 00:00:00+0000', tz='UTC'), Timestamp('2015-10-20 00:00:00+0000', tz='UTC'), Timestamp('2016-01-11 00:00:00+0000', tz='UTC'), Timestamp('2016-04-06 00:00:00+0000', tz='UTC'), Timestamp('2016-04-26 00:00:00+0000', tz='UTC'), Timestamp('2016-07-19 00:00:00+0000', tz='UTC'), Timestamp('2016-10-11 00:00:00+0000', tz='UTC'), Timestamp('2017-01-11 00:00:00+0000', tz='UTC'), Timestamp('2017-04-11 00:00:00+0000', tz='UTC'), Timestamp('2017-04-25 00:00:00+0000', tz='UTC'), Timestamp('2017-10-12 00:00:00+0000', tz='UTC'), Timestamp('2018-04-24 00:00:00+0000', tz='UTC'), Timestamp('2019-04-23 00:00:00+0000', tz='UTC'), Timestamp('2020-02-25 00:00:00+0000', tz='UTC'), Timestamp('2020-03-03 00:00:00+0000', tz='UTC'), Timestamp('2020-03-09 00:00:00+0000', tz='UTC'), Timestamp('2020-04-06 00:00:00+0000', tz='UTC'), Timestamp('2020-04-23 00:00:00+0000', tz='UTC'), Timestamp('2020-06-05 00:00:00+0000', tz='UTC'), Timestamp('2020-08-05 00:00:00+0000', tz='UTC'), Timestamp('2020-08-19 00:00:00+0000', tz='UTC')]

It produces an exception in xirr: ValueError: f(a) and f(b) must have different signs

It also produces an error in Google Sheets.

Excel, however, produces a result:

Any idea what is going on here?

Does it sound like a good idea to accept a Pandas DataFrame with Datetimes as indices and values as column as input? It would save the step of converting to dictionary.

Referencing:

Should there be a check there for the extreme cases where all the values have the same sign. I.e., something like this?

    if(all(v >= 0 for _, v in valuesPerDate.items())):
        return -float("inf")
    if(all(v <= 0 for _, v in valuesPerDate.items())):
        return float("inf")

Although Excel has both xnpv and xirr functions a user might want different functionality, particularly for xirr. Two characteristics are significant:

1. date is a number
2. argument configuration: XNPV(rate, values, dates)

The following code offered for discussion could be tidied up (similar for xirr):

from datetime import date

def excel2date(excel_date):
    return date.fromordinal(date(1900, 1, 1).toordinal() + int(excel_date) - 2)

def xnpv_helper(rate, values, excel_date):
    dates = [excel2date(i) for i in excel_date]
    valuesPerDate = dict(zip(dates, values))
    return xnpv(valuesPerDate, rate)

# valuesPerDate = {date(2019, 12, 31): -100, date(2020, 12, 31): 110}
xnpv_helper(-0.10, [-100, 110], [43830.0, 44196.0])

Tried running the xirr with input

valuesPerDate = {datetime.date(2020, 7, 21): 999950.0, datetime.date(2020, 6, 22): 5000.0, datetime.date(2020, 8, 20): 2499.88, datetime.date(2020, 9, 21): 2499.88, datetime.date(2020, 7, 15): 104022.22, datetime.date(2020, 8, 3): 1999.9, datetime.date(2020, 8, 10): 9999.5, datetime.date(2020, 9, 1): 9999.5, datetime.date(2020, 9, 10): 9999.5, datetime.date(2020, 10, 1): 9999.5, datetime.date(2020, 2, 17): 2000.0, datetime.date(2020, 3, 2): 10000.0, datetime.date(2020, 3, 16): 2000.0, datetime.date(2020, 4, 1): 10000.0, datetime.date(2020, 4, 15): 2000.0, datetime.date(2020, 5, 4): 10000.0, datetime.date(2020, 5, 15): 2000.0, datetime.date(2020, 6, 1): 10000.0, datetime.date(2020, 6, 15): 2000.0, datetime.date(2020, 7, 1): 9999.5, datetime.date(2020, 7, 13): -104027.42, datetime.date(2020, 8, 17): 1999.9, datetime.date(2020, 9, 15): 1999.9, datetime.date(2020, 7, 29): 499975.0, datetime.date(2020, 7, 20): 999950.0, datetime.date(2020, 7, 22): 149992.5, datetime.date(2020, 7, 28): -500000.0, datetime.date(2020, 8, 25): 2499.88, datetime.date(2020, 9, 27): -25000.0, datetime.date(2020, 7, 8): 999950.0, datetime.date(2020, 2, 3): 2000.0, datetime.date(2020, 8, 5): 2499.88, datetime.date(2020, 9, 7): 2499.88, datetime.date(2020, 9, 25): 2499.88, datetime.date(2020, 10, 5): 2499.88, datetime.date(2018, 7, 16): 10000.0, datetime.date(2018, 9, 10): 5000.0, datetime.date(2018, 10, 10): 5000.0, datetime.date(2018, 11, 12): 10000.0, datetime.date(2018, 12, 10): 10000.0, datetime.date(2019, 1, 10): 10000.0, datetime.date(2019, 2, 11): 10000.0, datetime.date(2019, 3, 11): 10000.0, datetime.date(2019, 4, 10): 10000.0, datetime.date(2019, 5, 10): 10000.0, datetime.date(2019, 6, 10): 10000.0, datetime.date(2019, 7, 10): 10000.0, datetime.date(2019, 8, 13): 10000.0, datetime.date(2019, 9, 11): 10000.0, datetime.date(2019, 10, 10): 10000.0, datetime.date(2019, 11, 11): 10000.0, datetime.date(2019, 12, 10): 10000.0, datetime.date(2020, 1, 10): 10000.0, datetime.date(2020, 2, 10): 10000.0, datetime.date(2020, 3, 11): 10000.0, datetime.date(2020, 4, 13): 10000.0, datetime.date(2020, 5, 11): 10000.0, datetime.date(2020, 6, 10): 10000.0, datetime.date(2020, 7, 10): 9999.5, datetime.date(2018, 7, 24): 500000.0, datetime.date(2020, 4, 8): 100000.0, datetime.date(2018, 8, 10): 5000.0, datetime.date(2018, 10, 9): -18904.37, datetime.date(2018, 10, 15): -9898.46, datetime.date(2018, 7, 25): 500000.0, datetime.date(2020, 8, 6): -104221.0, datetime.date(2020, 1, 27): 10000.0, datetime.date(2020, 2, 20): 10000.0, datetime.date(2020, 3, 20): 10000.0, datetime.date(2020, 4, 20): 10000.0, datetime.date(2020, 5, 20): 10000.0}

print(xirr.xirr(valuesPerDate))

Got an error

RuntimeError: Failed to converge after 50 iterations, value is 3.029539740722745e+55.

During handling of the above exception, another exception occurred:

`print(xirr.xirr(valuesPerDate))
result = scipy.optimize.brentq(lambda r: xnpv(valuesPerDate, r), -0.999999999999999, 1e20, maxiter=10**6)

r = _zeros._brentq(f, a, b, xtol, rtol, maxiter, args, full_output, disp)
ValueError: f(a) and f(b) must have different signs`

When I install the xirr package via poetry, I get the latest version 0.1.6 but the listsXirr function is missing, even though it's here in the source code. Do you need to update the PyPI package or did something go wrong with the latest release or am I missing something?

I have used the xirr function and found that the result is inconsistent with XIRR of excel.