Invalid result from big calculation

[dbi]

Running this calculation results in an invalid value 0.006097 (correct value is closer to 0.120581416 according to google spreadsheets).

d = [{2038, 2, 4}, {2037, 11, 2}, {2037, 8, 2}, {2037, 5, 3}, {2037, 2, 4}, {2036, 11, 2}, {2036, 8, 2}, {2036, 5, 3}, {2036, 2, 5}, {2035, 11, 2}, {2035, 8, 2}, {2035, 5, 3}, {2035, 2, 4}, {2034, 11, 2}, {2034, 8, 2}, {2034, 5, 3}, {2034, 2, 4}, {2033, 11, 2}, {2033, 8, 2}, {2033, 5, 3}, {2033, 2, 4}, {2032, 11, 2}, {2032, 8, 2}, {2032, 5, 3}, {2032, 2, 5}, {2031, 11, 2}, {2031, 8, 2}, {2031, 5, 3}, {2031, 2, 4}, {2030, 11, 2}, {2030, 8, 2}, {2030, 5, 3}, {2030, 2, 4}, {2029, 11, 2}, {2029, 8, 2}, {2029, 5, 3}, {2029, 2, 4}, {2028, 11, 2}, {2028, 8, 2}, {2028, 5, 3}, {2028, 2, 5}, {2027, 11, 2}, {2027, 8, 2}, {2027, 5, 3}, {2027, 2, 4}, {2026, 11, 2}, {2026, 8, 2}, {2026, 5, 3}, {2026, 2, 4}, {2025, 11, 2}, {2025, 8, 2}, {2025, 5, 3}, {2025, 2, 4}, {2024, 11, 2}, {2024, 8, 2}, {2024, 5, 3}, {2024, 2, 5}, {2023, 11, 2}, {2023, 8, 2}, {2023, 5, 3}, {2023, 2, 4}, {2022, 11, 2}, {2022, 8, 2}, {2022, 5, 3}, {2022, 2, 4}, {2021, 11, 2}, {2021, 8, 2}, {2021, 5, 3}, {2021, 2, 4}, {2020, 11, 2}, {2020, 8, 2}, {2020, 5, 3}, {2020, 2, 5}, {2019, 11, 2}, {2019, 8, 2}, {2019, 5, 3}, {2019, 2, 4}, {2018, 11, 2}, {2018, 8, 2}, {2018, 5, 31}]
v = [-305, -5, -5, -5, -5, -5, -5, -5, -5, -5, -5, -5, -5, -5, -5, -5, -5, -5, -5, -5, -5, -5, -5, -5, -5, -5, -5, -5, -5, -5, -5, -5, -5, -5, -5, -5, -5, -5, -5, -5, -5, -5, -5, -5, -5, -5, -5, -5, -5, -5, -5, -5, -5, -5, -5, -5, -5, -5, -5, -5, -5, -5, -5, -5, -5, -5, -5, -5, -5, -5, -5, -5, -5, -5, -5, -5, -5, -5, -5, 188.0]
iex(91)> ExXirr.xirr(d, v)
{:ok, 0.006097}

Weirdly enough, if you just change the last -5 to -4.8 the calculation will return the expected value, like this.

d = [{2038, 2, 4}, {2037, 11, 2}, {2037, 8, 2}, {2037, 5, 3}, {2037, 2, 4}, {2036, 11, 2}, {2036, 8, 2}, {2036, 5, 3}, {2036, 2, 5}, {2035, 11, 2}, {2035, 8, 2}, {2035, 5, 3}, {2035, 2, 4}, {2034, 11, 2}, {2034, 8, 2}, {2034, 5, 3}, {2034, 2, 4}, {2033, 11, 2}, {2033, 8, 2}, {2033, 5, 3}, {2033, 2, 4}, {2032, 11, 2}, {2032, 8, 2}, {2032, 5, 3}, {2032, 2, 5}, {2031, 11, 2}, {2031, 8, 2}, {2031, 5, 3}, {2031, 2, 4}, {2030, 11, 2}, {2030, 8, 2}, {2030, 5, 3}, {2030, 2, 4}, {2029, 11, 2}, {2029, 8, 2}, {2029, 5, 3}, {2029, 2, 4}, {2028, 11, 2}, {2028, 8, 2}, {2028, 5, 3}, {2028, 2, 5}, {2027, 11, 2}, {2027, 8, 2}, {2027, 5, 3}, {2027, 2, 4}, {2026, 11, 2}, {2026, 8, 2}, {2026, 5, 3}, {2026, 2, 4}, {2025, 11, 2}, {2025, 8, 2}, {2025, 5, 3}, {2025, 2, 4}, {2024, 11, 2}, {2024, 8, 2}, {2024, 5, 3}, {2024, 2, 5}, {2023, 11, 2}, {2023, 8, 2}, {2023, 5, 3}, {2023, 2, 4}, {2022, 11, 2}, {2022, 8, 2}, {2022, 5, 3}, {2022, 2, 4}, {2021, 11, 2}, {2021, 8, 2}, {2021, 5, 3}, {2021, 2, 4}, {2020, 11, 2}, {2020, 8, 2}, {2020, 5, 3}, {2020, 2, 5}, {2019, 11, 2}, {2019, 8, 2}, {2019, 5, 3}, {2019, 2, 4}, {2018, 11, 2}, {2018, 8, 2}, {2018, 5, 31}]
v = [-305, -5, -5, -5, -5, -5, -5, -5, -5, -5, -5, -5, -5, -5, -5, -5, -5, -5, -5, -5, -5, -5, -5, -5, -5, -5, -5, -5, -5, -5, -5, -5, -5, -5, -5, -5, -5, -5, -5, -5, -5, -5, -5, -5, -5, -5, -5, -5, -5, -5, -5, -5, -5, -5, -5, -5, -5, -5, -5, -5, -5, -5, -5, -5, -5, -5, -5, -5, -5, -5, -5, -5, -5, -5, -5, -5, -5, -5, -4.8, 188.0]
iex(102)> ExXirr.xirr(d, v)
{:ok, 0.120448}

[goodhamgupta]

The main conditions for computing xirr are

• Out of all the cashflows present, atleast one should be positive.
• The dates of the transactions need to be sorted in a ascending order. This is because the first date is the start date and subsequent/later dates are future dates of outgoing payments or income.

Your example does not satisfy the 2nd condition. Sorting and executing your first example gives the right output.

d = [{2038, 2, 4}, {2037, 11, 2}, {2037, 8, 2}, {2037, 5, 3}, {2037, 2, 4}, {2036, 11, 2}, {2036, 8, 2}, {2036, 5, 3}, {2036, 2, 5}, {2035, 11, 2}, {2035, 8, 2}, {2035, 5, 3}, {2035, 2, 4}, {2034, 11, 2}, {2034, 8, 2}, {2034, 5, 3}, {2034, 2, 4}, {2033, 11, 2}, {2033, 8, 2}, {2033, 5, 3}, {2033, 2, 4}, {2032, 11, 2}, {2032, 8, 2}, {2032, 5, 3}, {2032, 2, 5}, {2031, 11, 2}, {2031, 8, 2}, {2031, 5, 3}, {2031, 2, 4}, {2030, 11, 2}, {2030, 8, 2}, {2030, 5, 3}, {2030, 2, 4}, {2029, 11, 2}, {2029, 8, 2}, {2029, 5, 3}, {2029, 2, 4}, {2028, 11, 2}, {2028, 8, 2}, {2028, 5, 3}, {2028, 2, 5}, {2027, 11, 2}, {2027, 8, 2}, {2027, 5, 3}, {2027, 2, 4}, {2026, 11, 2}, {2026, 8, 2}, {2026, 5, 3}, {2026, 2, 4}, {2025, 11, 2}, {2025, 8, 2}, {2025, 5, 3}, {2025, 2, 4}, {2024, 11, 2}, {2024, 8, 2}, {2024, 5, 3}, {2024, 2, 5}, {2023, 11, 2}, {2023, 8, 2}, {2023, 5, 3}, {2023, 2, 4}, {2022, 11, 2}, {2022, 8, 2}, {2022, 5, 3}, {2022, 2, 4}, {2021, 11, 2}, {2021, 8, 2}, {2021, 5, 3}, {2021, 2, 4}, {2020, 11, 2}, {2020, 8, 2}, {2020, 5, 3}, {2020, 2, 5}, {2019, 11, 2}, {2019, 8, 2}, {2019, 5, 3}, {2019, 2, 4}, {2018, 11, 2}, {2018, 8, 2}, {2018, 5, 31}]
v = [-305, -5, -5, -5, -5, -5, -5, -5, -5, -5, -5, -5, -5, -5, -5, -5, -5, -5, -5, -5, -5, -5, -5, -5, -5, -5, -5, -5, -5, -5, -5, -5, -5, -5, -5, -5, -5, -5, -5, -5, -5, -5, -5, -5, -5, -5, -5, -5, -5, -5, -5, -5, -5, -5, -5, -5, -5, -5, -5, -5, -5, -5, -5, -5, -5, -5, -5, -5, -5, -5, -5, -5, -5, -5, -5, -5, -5, -5, -5, 188.0]
sorted_dates = Enum.sort(d)
sorted_values = Enum.reverse(v) # This is because the initial array was sorted in descending order
iex> ExXirr.xirr(sorted_dates, sorted_values)
{:ok, 120581}

Same problem with the other example.

d = [{2038, 2, 4}, {2037, 11, 2}, {2037, 8, 2}, {2037, 5, 3}, {2037, 2, 4}, {2036, 11, 2}, {2036, 8, 2}, {2036, 5, 3}, {2036, 2, 5}, {2035, 11, 2}, {2035, 8, 2}, {2035, 5, 3}, {2035, 2, 4}, {2034, 11, 2}, {2034, 8, 2}, {2034, 5, 3}, {2034, 2, 4}, {2033, 11, 2}, {2033, 8, 2}, {2033, 5, 3}, {2033, 2, 4}, {2032, 11, 2}, {2032, 8, 2}, {2032, 5, 3}, {2032, 2, 5}, {2031, 11, 2}, {2031, 8, 2}, {2031, 5, 3}, {2031, 2, 4}, {2030, 11, 2}, {2030, 8, 2}, {2030, 5, 3}, {2030, 2, 4}, {2029, 11, 2}, {2029, 8, 2}, {2029, 5, 3}, {2029, 2, 4}, {2028, 11, 2}, {2028, 8, 2}, {2028, 5, 3}, {2028, 2, 5}, {2027, 11, 2}, {2027, 8, 2}, {2027, 5, 3}, {2027, 2, 4}, {2026, 11, 2}, {2026, 8, 2}, {2026, 5, 3}, {2026, 2, 4}, {2025, 11, 2}, {2025, 8, 2}, {2025, 5, 3}, {2025, 2, 4}, {2024, 11, 2}, {2024, 8, 2}, {2024, 5, 3}, {2024, 2, 5}, {2023, 11, 2}, {2023, 8, 2}, {2023, 5, 3}, {2023, 2, 4}, {2022, 11, 2}, {2022, 8, 2}, {2022, 5, 3}, {2022, 2, 4}, {2021, 11, 2}, {2021, 8, 2}, {2021, 5, 3}, {2021, 2, 4}, {2020, 11, 2}, {2020, 8, 2}, {2020, 5, 3}, {2020, 2, 5}, {2019, 11, 2}, {2019, 8, 2}, {2019, 5, 3}, {2019, 2, 4}, {2018, 11, 2}, {2018, 8, 2}, {2018, 5, 31}]
v = [-305, -5, -5, -5, -5, -5, -5, -5, -5, -5, -5, -5, -5, -5, -5, -5, -5, -5, -5, -5, -5, -5, -5, -5, -5, -5, -5, -5, -5, -5, -5, -5, -5, -5, -5, -5, -5, -5, -5, -5, -5, -5, -5, -5, -5, -5, -5, -5, -5, -5, -5, -5, -5, -5, -5, -5, -5, -5, -5, -5, -5, -5, -5, -5, -5, -5, -5, -5, -5, -5, -5, -5, -5, -5, -5, -5, -5, -5, -4.8, 188.0]
sorted_dates = Enum.sort(d)
sorted_values = Enum.reverse(v) 
iex> ExXirr.xirr(sorted_dates, sorted_values)
{:ok, 0.120448}

You can also verify these values in the sheet here.

[dbi]

Thank you.