Help with an XIRR error - #NUM

[Ad1981]

Hello,

Can anyone identify what the issue is with the XIRR formula in the attached/embedded screenshot?

The dates are consecutive. The dates are formatted as 'Date'. The cash flows are formatted as Accounting. The initial cash flow value is negative, representing an investment or cash outflow.

Any help is much appreciated!

 

[joeu2004]

In general, XIRR returns #NUM when it needs a "guess" (3rd parameter) in order to improve its internal iterative algorithm.

But first, I suspect that your cash flow model is incorrect.

As written, it indicates an initial balance of -20,000,000 and an ending balance of 55,555.55 (!).

Ostensibly, that is a cumulative return of -99.72222225% = (55555.55/20000000) - 1.

And the annualized return is exactly -100% = (55555.55/20000000)^(365/18) - 1, for 18 days between 12/23/2019 and 1/10/2020.

That is not a valid IRR, because the (X)NPV is calculated using the denominator 1-IRR, which is zero.

I suspect that 55,555.55 represents a withdrawal Jan 10. Then for XIRR, the value on that date should be the ending balance after the withdrawal.

 

[joeu2004]

Errata, too late to edit....

the (X)NPV is calculated using the denominator 1-IRR, which is zero.

Typo: that should be 1+IRR, which is indeed zero when IRR = -100%.

 

[Ad1981]

Thanks @joeu2004 ! You're correct, I later determined that my model wasn't looking far enough into the future to include the full return of capital. Thanks for the informative explanation!

 

[joeu2004]

I later determined that my model wasn't looking far enough into the future to include the full return of capital.

You're wecome. But I think you misunderstand.

You do not need to look beyond Jan 10.

You simply need to determine the valuation of the assets ("notional value") on that date after any deposits and withdrawals, as if all assets were liquidated.

For traded securities, that is the current market value.

For capital investments, you might need to be more creative in deterimining its current valuation. But don't over-think it just to get a (misleading) positive result. The "current valuation" certainly does not include "the full return of capital".

That said, I would agree that annualizing daily and extremely short-term changes gives us a misleading result.

For example, the S&P500 index changed by +0.49% today. Annualized, that is over 495% (!). Certainly, that is not a realistic number.