When running the XIRR function "=XIRR(G163:G168,F163:F168)" where the cashflow is in G163:G168 and my dates are in F163:F168, excel is returning a value of .000000298023% which is definitely not correct. Any advise would be greatly appreciated!
9/13/2019 (2,137,500.00)
9/13/2019 (1,710,000.00)
9/13/2019 (35,331,814.80)
9/13/2019 (931,950.00)
9/13/2019 (14,990,988.60)
9/30/2020 45,757,426.80
The technique that Excel uses can return multiple values, depending on the initial assumption of rate. Since you left it unspecified, it assumes 10%.
If you add -10% as the optional
guess
argument, it will return 16.23%.There are a couple of ways to come up with a reasonable guess if XIRR is giving a wrong answer, but here's one:
0.1*sign(sum(values))