tarioch
Improve Excel Compatibility
Although Excel has both xnpv and xirr functions a user might want different functionality, particularly for xirr. Two characteristics are significant:
-
dateis a number - 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])
What's the usecase where you would need to be able to use "excel" dates for the calculation?
Usecase is making values and dates available as separate arrays. The last line shows the data from reading excel cells (in LibreOffice Calc). excel2date converts the date to the format included in the line above it. Happy to see better code.
My interest is replacing XIRR(values, dates, [guess]) with UDF XIRR(dates, values, balance, [guess]) (where value is cr/db) and return array of xirr to match dates where balance date is eofy.
Excel xirr
Date Credit xirr
31/12/2019 80.00
31/12/2020 -100.00 0.25
31/12/2020 100.00
30/06/2021 -5.00
31/12/2021 -104.50 0.10
0.17
Proposed UDF xirr
Date Credit Balance xirr
31/12/2019 80.00
31/12/2020 100.00 0.25
30/06/2021 -5.00
31/12/2021 104.50 0.10
0.17
Ok, sounds like a good usecase, so would be nice if you can create a PR for it