I'm trying to calculate an investment's rate of return but getting very strange results. I made 6 different example scenarios to understand the logic behind XIRR calculation and just to test the results. In some cases I got the error messages and in the other cases just strange results. Can anyone explain what I'm doing wrong?
XIRR is an annualized return, meaning the return for that time period if the same return were earned for an entire year. Therefore, it needs to know the time period between transactions in order to annualize the returns.
For #2: XIRR requires transactions to happen on different days. Your transactions happen on the same day, so there is no time period for which to calculate "return". (Theoretically, the periodic return is infinite since the transactions happen at the same time).
For #3: A 10% actual return over 31 days is much better than a 10% actual return over 365 days, which is reflected in XIRR. If you annualized the 31-day 10% return (meaning earned 10% every 31 days for a year), you'd get
(1+0.1)^(365/31) - 1 = 207.16%
For #4 - there is no IRR where you gain 10$ in one month then "lose" another $300 the next month on a $100 investment. But, you didn't really "lose" $300 since you presumably still hold the stock, and it has value. One solution would be to put the "current" value of the stock as a "transaction" on the current date. That would be interpreted as "what would the return be if I sold everything today at its current value".
For #5: You made 60% in two months, which is an incredibly high return when annualized (again, XIRR assumes that you make the same return for an entire year, which is not always realistic). As a side question, how did you get dividends if you sold the shares the month before?
For #6: Like #4, I would add the current value as a final "transaction" (otherwise it looks like you have to pay an additional $110 to exit the position, which is hopefully not the case).