Improve Excel Compatibility

[flywire]

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])

[tarioch]

What's the usecase where you would need to be able to use "excel" dates for the calculation?

[flywire]

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.

[flywire]

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

[tarioch]

Ok, sounds like a good usecase, so would be nice if you can create a PR for it

[flywire]

LibreOffice Calc implementation under development.