#NUM error in XIRR formula

Can anyone guess why I'm getting a #NUM error in this XIRR formula (cell E14). I have the exact setup for multiple similar stocks and they all work fine. But this particular one doesn't. At first I thought it was just too close to the start date which often results in this error. But there is now a nearly two-week spread from the starting value to the current value and it is still giving me this error. Thanks for any guidance!

My guess offhand is you don't have enough data points in the arrays.
If I add one more date in col B and put a number across from it in col E - like 1, it puts a value in E14. I was looking up the function on MS here
https://support.microsoft.com/en-us/...b-a303ad9adc9d
and it gives a couple reasons for a #NUM! outcome none of which are insufficient number of data points but it will work with one more date between the first and last dates and one more value in col E.

I considered that as well (not enough data points). But I have another stock with the same number of data points that shows a XIRR value. As far as I can tell, there is absolutely no difference between the two. I have also checked all formatting, data sources etc. (Example attached).

It has something to do with the starting date - but I have no idea why. I changed the start date to 3/31/24 (one day earlier) and it returned a XIRR value. That is also not listed in the #NUM errors for XIRR formulas.

or it could be the end date, if I leave B3 as 4/1/24 but change b12 to 4/13/24 it also calculates. It also calculates if I put dates in between 4/1 and 4/12. Apparently an 11 day gap is one day too short for the formula? And the second one you have starting at 1/2/24, if you change that current value date to 1/13/24 (11 days later) it also returns num but not if you make it 1/14/24.

I have very little understanding of the math behind the XIRR() function. My understanding is the XIRR() function uses a numerical algorithm to find the value for rate that causes XNPV() to be 0. I entered =XNPV(B18,$E$3:$E$12,$B$3:$B$12) into C18 (copied down to make several copies), then entered different values for rate into B18:B29. As I entered different values, I saw that the XNPV() seemed to become 0 between xirr=-0.9999 and -0.95. In the past, I have observed that there seems to be a hard restriction in the math that states that xirr must be > -1. My guess is that, in this specific case, the xirr is so close to -1 that Excel's algoriothm is unable to find the xirr using the default starting guess of 0.1 (which is what Excel uses when you omit the option guess parameter).

Using that information, I could enter =XIRR(E3:E12,B3:B12,-0.999) into E14, and LO Calc (I don't have Excel on this computer) was able to calculate an xirr (about -99.6%). In past xirr related questions, it has seemed that LO Calc has a more stable implementation of the numerical xirr algorithm, so I cannot say for sure if Excel this will work in Excel, but it is worth trying.

That appears to answer the question for this specific example. In the long run, I expect you will want to explore the behavior of the XIRR() function for your specific financial model in some detail. See what you will need to do to determine a suitable "guess" parameter for each scenario that you expect to see. You may also need a way to determine when no solution is possible (there is not xirr that will cause xnpv to return 0).

Does that help?