Forum Discussion
XIRR not working
Hi. I am having problems with the XIRR function in Excel. I have same number of dates / values all formatted properly. I have negative and positive values. I am noticing it works as long as the calculated IRR appears to be below 20%. Any time it approaches 20%+ it gives me a #NUM!. I have tried turning on iteration and increasing the iteration from 100 to 1000. It does not change it. I have also tried adding a "guess" and using other numbers but it does not change the result.
Here is the data I am using:
| 10/31/2022 | -150922.33 |
| 11/2/2022 | 0.00 |
| 11/3/2022 | -5695.84 |
| 11/14/2022 | -576.00 |
| 11/17/2022 | -1655.00 |
| 12/1/2022 | 0.00 |
| 12/2/2022 | -3325.00 |
| 12/12/2012 | 2304.16 |
| 12/19/2022 | -639.40 |
| 1/1/2023 | 0.00 |
| 1/17/2023 | 2304.16 |
| 1/31/2023 | 3972.68 |
| 2/1/2023 | 0.00 |
| 2/15/2023 | 10249.53 |
| 3/16/2023 | 2004.27 |
| 5/5/2023 | 2237.91 |
| 5/15/2023 | -62.50 |
| 6/1/2023 | -639.41 |
| 6/9/2023 | -93.75 |
| 6/9/2023 | 2237.91 |
| 8/4/2023 | 2349.81 |
| 8/4/2023 | 53.28 |
| 9/15/2023 | 2293.86 |
| 9/15/2023 | 53.28 |
| 10/13/2023 | 2131.34 |
| 10/17/2023 | 167.84 |
| 10/19/2023 | -1879.00 |
| 11/30/2023 | 2467.30 |
| 11/30/2023 | 2349.81 |
| 11/30/2023 | 2237.91 |
| 12/15/2023 | 1491.95 |
| 1/1/2024 | 1645.34 |
| 2/1/2024 | 2131.34 |
| 3/1/2024 | 2131.34 |
| 4/1/2024 | 2131.34 |
| 5/1/2024 | 2131.34 |
| 6/1/2024 | 1491.94 |
| 7/1/2024 | 2131.34 |
| 8/1/2024 | 2131.34 |
| 8/31/2024 | 146000.00 |
Here is the formula I am using - note the pasted data above sits in F54:G93:
=XIRR(G54:G93,F54:F93)
As mentioned, I have also tried it with a guess by adding a 3rd argument:
=XIRR(G54:G93,F54:F93,0.2)
Any thoughts on how to fix this calculation problem? Thank you.
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
8 Replies
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.
- To add some more info, if you were change the last cell which has 146000.00 to any number less than or equal to 145540.00 it works. The calculated IRR at 145540.00 is 0.199994827 which makes me think 20% is the break point where it delivers #NUM!. Anything below 145540.00 gives an IRR below 20%. When I try much higher numbers, it still yields #NUM!.
I also tried a Goal Seek on the XIRR by toggling the cell with 146000 and it says it found a solution but then fails to iterate to the solution for me. Hope that helps. Thank you again for any help.
