Forum Discussion
XIRR not working
XIRR has trouble with daily changes, presumably because when we annualize a daily change, it can result in an astronomical value. (I can investigate further later.)
XNPV does not support non-positive discount rates. A design flaw, IMHO.
And Goal Seek will not proceed if the value of the "set cell" (=XNPV...) is an Excel error (#NUM) initially.
Use SUMPRODUCT instead. For example, with J61 initial empty or zero, and the following in J62:
=SUMPRODUCT($G$54:$G$93/(1+J61)^(($F$54:$F$93-$F$54)/365))
Goal Seek derives a discount rate of 20.4577715104322% with the following set-up:
Set cell: J62
To value: 0
By changing cell: J61
Bob_H45 wrote: ``Still not sure I follow why XIRR is not working``
Neither do I. No time to investigate now. Hopefully later.
PS.... You might note that even with a close "guess", XIRR fails. That is not uncommon for XIRR (sigh). I believe there is something wrong with its internal implementation. Often, my own Newton-Raphson algorithm succeeds where XIRR fails. Be that as it may, the problem might be related to a steep rate of change near the solution. That's a wild guess. TBD.
- Yes. I had noticed that. I appreciate your continued assistance. Very helpful.
I would also point out that XNPV function does not work on that list of data even when using a positive discount rate. It also gives a #NUM! error. I have tried setting the discount rate to many different positive inputs and it never works. Maybe the source of the problem with the XIRR function is also creating a problem with the XNPV function. As such, using goal seek on the SUMPRODUCT approach you provided appears to be the only workaround for this glitch.
Bob_H45 wrote: ``Maybe the source of the problem with the XIRR function is also creating a problem with the XNPV function``
Found it! You have a typo in the date between 12/2/2022 and 12/19/2022. It should be 12/12/2022. But you wrote 12/12/2012.
XIRR and XNPV allow for out-of-order dates. But all dates must be on or after the first date (10/31/2022).
Of course, my SUMPRODUCT formula does not "discriminate". But the result is GIGO.
With that correction, =XIRR(G54:G93,F54:F93) returns 14.3656998872757% without a "guess".
And with that discount rate, XNPV and my SUMPRODUCT return 0.000141385215101764.
-----
BTW, Goal Seek determines a discount rate of 14.3656995188635%. (YMMV)
And with that discount rate, XNPV and SUMPRODUCT return 0.000937376098590903.
So the initial GS result is not as good. We can improve the GS result. But that is not relevant.