what's wrong with my XIRR formula?

It's the on the right. I did that one manually, the one on the left I got from page as an example.

What result do you expect? I'm not knowledgeable about financial functions, but I understand that the XIRR() function attempts to find an interest rate that makes the XNPV() function equal to 0. By entering =XNPV(M5,M8:M12,L8:L12) and entering different interest rates in M5, I cannot readily find an interest rate that makes XNPV() return 0. Are you certain there is a solution for this particular problem?

All I know is it shouldn't be 0. I can change the dates and the values and for some reason it returns zero. Can you see the difference in the two examples I have?

I believe the zero result is due to the second negative cash flow. This cash flow item is greater than all the positive cash flows. If you sum the cash flows and it is negative then the xirr returns zero.

I'm opening this in LO Calc. From past efforts, it seems that LO Calc has a more stable implementation of the XIRR() function. In LO Calc, the left example returns 37.34%, and the right example returns an error. I know Excel's implementation often times returns 2.xxxE-9 when it errors (which looks a lot like 0). The right example looks to me like it has no solution. Are you expecting this example to error rather than return 0?

Thanks. What's "LO Calc"?

I don't know exactly what I'm doing yet nor what I should expect. But I suppose I was expecting to get either positive or negative numbers.

I need to keep reviewing my data as it still doesn't make sense to me. But it looks like the function works, but I guess not when going below zero.

LO Calc=Libre Office Calc, Libre Office's spreadsheet application.

I'm not sure what you mean that "the function works, but no when going below zero". Other than some minor numeric instabilities, Excel's XIRR() function works pretty well when there is a solution.

For someone who is unfamiliar with the concepts behind XIRR(), One excercise that helps me is to build a table and chart for XNPV(). As I noted, XIRR() returns the interest rate that makes XNPV() zero. Build a table with a range of interest rates and the resulting XNPV(), then chart the results in a scatter plot. Then you can see the behavior of the XNPV() function and see where (if at all) XNPV() crosses 0:

1) In H17:H38, enter -1 to +1.1 (or other range of expected interest rates).
2) In I17, enter an XNPV() function: =XNPV($H17,I$8:I$12,H$8:H$12) [note the mix of relative and absolute references for easy copying] Copy/paste/fill into G17:G38 and M17:M38.
3) Select H17:M38 and insert a scatter chart. Format the chart so you can see how the XNPV() function behaves.
4) Continue adjusting the column of interest rates and the axis limits of the chart as you explore the behavior of the XNPV() function.

What I see with these two examples is that the left function has a clear place (just below rate=40%) where it crosses y=0. The right example increases, but appears to have an asymptote below the x axis so that it never crosses the y=0 line. Hence why the left example returns an expected interest rate and the right example returns an error (or a meaningless value that is equivalent to an error result).

It isn't actually 0:

AliGW on MS365 Insider (Windows) 64 bit

	L
6	0.00000029802%

Sheet: Sheet1 (2)

aha!

....

Thanks. I'll have to study all that some time later as it went completely over my head. I'll have to play around with xnpv after I figure a few things out with my data first

so what's going on with it here? Even after putting in a substantially high value to push a positive IRR I just get this default "0.0000003%" number. So I suspect this number isn't really a calculation. Anyone know what's going on?

You might want to remove the SOLVED tag ...

You are correct that the 3E-7% number is the same as a #NUM error or no solution. As before, plotting XNPV() against interest rate, and I cannot see any rate that makes XNPV() become 0, so it looks to me like the answer is the same as before -- there are no solutions for these problems.

Again, I will recommend that you get used to plotting XNPV() against interest rates when you need to understand problems with the XIRR() function. I'm not enough of a financial analyst to explain the financial concepts behind XIRR() and XNPV(), but I'm fairly confident that the final answer to all of the problematic examples you have shown here is that there are no solutions for these examples.

Thanks again. Unfortunately I have no idea what any of what you said. That's the level of my experience here. As far as my shallow understanding of rate of returns it's a math calculation that I assumed would calculate a return either positive or even negative, which is pretty straight forward to me, but apparently there's rules to it, either in math or in Excel which doesn't make sense to me. To me it sounds like you're saying I can add 2+2 to get 4 but if I add 2+ an odd number the gods will strike me down lol. But it sounds like you're saying I should experiment with XNPV instead, so I'll look into that.

I don't think I can really help you understand the relationship between rate of return (IRR() and/or XIRR() and/or RATE()) and present value (NPV() and/or XNPV() and/or PV()). I put "financial relationship rate of return and net present value" into my favorite search engine and came up with several pages attempting to describe how present value and interest rates are related and how they are useful. Perhaps one of these pages might help?

https://www.investopedia.com/terms/n/npv.asp
https://www.investopedia.com/ask/answers/05/npv-irr.asp
https://medium.com/magnimetrics/usin...sis-68bfe71bc2

Or any of the many other pages that a similar internet search should find.

Thank you. I appreciate the effort though. If it can't be shown in a drawing or a parable my scatter brain won't get it. Unless there's a will, and there may be, all depends on how bad I want it. We'll see. I'll probably compile a youtube list of the shortest videos on the subject to get it in several nutshells from different angles which usually works pretty good for me.

Did you try plotting XNPV() against different discount rates as I outlined in post #7? For this mathematician, that visualization is the most instructive.