Background
I am getting substantially large variation in XIRR vs IRR calculations.
Problem & Help requested
Can someone please explain why I get two very different outcomes, and which method is more accurate? I've provided an example of cashflows which result in such large variation.
For this series of cashflows, the standard IRR function is returning -2.91% and the XIRR function is returning 0%.
If I change certain cashflows values I can get the values to align. For example
I have no clue what is driving this delta. Any help be appreciated!
Example Data
Date Cashflows
31/12/2023 -300000
31/12/2024 -300000
31/12/2025 -300000
31/12/2026 -300000
31/12/2027 -50000
31/12/2028 15000
31/12/2029 15000
31/12/2030 15000
31/12/2031 15000
31/12/2032 15000
31/12/2033 15000
31/12/2034 15000
31/12/2035 15000
31/12/2036 15000
31/12/2037 15000
31/12/2038 15000
31/12/2039 15000
31/12/2040 15000
31/12/2041 15000
31/12/2042 15000
31/12/2043 15000
31/12/2044 15000
31/12/2045 15000
31/12/2046 15000
31/12/2047 15000
31/12/2048 15000
31/12/2049 15000
31/12/2050 15000
31/12/2051 15000
31/12/2052 15000
31/12/2053 15000
31/12/2054 15000
31/12/2055 15000
31/12/2056 15000
31/12/2057 15000
31/12/2058 15000
31/12/2059 15000
31/12/2060 15000
31/12/2061 15000
31/12/2062 15000
31/12/2063 15000
31/12/2064 15000
31/12/2065 15000
31/12/2066 15000
31/12/2067 15000
So I found this link online which says I should enter in a guess for IRR
If you format C3 as Scientific, you will see that XIRR returns about 2.98E-09, not really zero.
Although you might think that is close enough to zero, in my experience, that constant represents an error state in XIRR.
In this case, I believe it is an alternative for the #NUM error.
Thus, XIRR might require a "guess" in order to determine the IRR.
After doing this, I've resolved my problem.