0

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
  • Please provide a [mcve] which has the actual values in the cells, rather than a screenshot of the values. Just write down the 12 values in 6 rows, hightlight them, and press `ctrl+k` to format them as code. My guess is that `XIRR` correctly does what it is documented as doing, but that there is a disconnect between that documented behavior and your own expectations. – John Coleman Oct 24 '20 at 12:02
  • Thanks @JohnColeman. Repasted in the values as text. –  Oct 24 '20 at 12:06
  • What value are you expecting? – John Coleman Oct 24 '20 at 12:22
  • A possible answer to my last question is `-0.162306223`. If so, the problem is traceable to the fact that the [first value is treated differently than the other values](https://support.microsoft.com/en-us/office/xirr-function-de1242ec-6477-445b-b11b-a303ad9adc9d). If you combine the first 5 rows into a single one for 9/13/2019 with a value of -55,102,253.40 and run `XIRR` on the resulting two rows of data you get a very different value. Beginning with 5 rows for the same day seems odd. – John Coleman Oct 24 '20 at 12:40
  • 2
    Works fine for me. Are the first columns actually dates and the second column values? – Ricardo Diaz Oct 24 '20 at 13:21

2 Answers2

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))
Ron Rosenfeld
  • 53,870
  • 7
  • 28
  • 60
0

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.

mkjeldsen
  • 2,053
  • 3
  • 23
  • 28