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
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
- Thanks Joe. Still not sure I follow why XIRR is not working but I like your elegant solution to get me an answer. Thank you!
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.
