Excel XIRR Function returning invalid calculation

Question

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 

Answer 1

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:

• Negative 10% for negative values: 0.1*sign(sum(values))

Answer 2

The answer submitted by Ron Rosenfeld is accurate but there are more, subtle details I think are worth pointing out.

1. The 0.000000298023 value the asker identified as "definitely not correct" is a well known erroneous value produced by the Excel XIRR function. However, it is more commonly referred to as 2.98023E-09.
2. With reference to the official documentation, Jon Coleman commented that the first value is treated specially. This is a property of the concept more than Excel's implementation of the XIRR function; the first value usually represents something like a starting balance, which is also why the documentation says that value must be negative.
3. The last value usually represents a final balance and will have the oppposite sign of the first value. This is also a property of the concept, not the implementation.
4. Technically, the actual signs used don't matter but they must be used consistently throughout: one sign for inflows, the other sign for outflows. By convention, inflows are negative and outflows are positive.
5. The XIRR function sometimes has trouble with out-of-order values, though I don't have details on this.
6. Besides the first and last values, the XIRR function seems to handle multiple cash flows on the same date. Summing by date may still be beneficial for execution time (I don't know) but it will definitely be correct according to the equation.

In this instance, Excel can apparently be pointed in the right direction with a guess value of -10%. However, it follows from point #2 that splitting the "first" cash flow into multiple cash flows on the same day hinder's Excel's XIRR function. Indeed, with the dataset

2019-09-13    -55,102,253.40
2020-09-30    45,757,426.80

Excel's XIRR function determines the expected -16.23% without supplying a manual guess.

See also: Microsoft Community support question.