0

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?

enter image description here

2

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).

3
  • It is said that XIRR is good for calculating a stock portfolio performance because it accounts for the depositing/withdrawal of funds over time. But what about day-traders who often make many trades a day? If XIRR requires transactions to happen on different days, then it is useless. And even for big fund managers we cannot be sure that some trades doesn't occur on the same day. How can they prevent such cases or is the usage of XIRR meant for very limited cases? – Jane Mänd Jan 4 at 17:26
  • I would say that IRR is not good for day traders if you're looking at individual trades. You could use it at the portfolio level if you aggregate all cashflows by day. If you're looking at individual trades with just an inflow and an outflow, then IRR is too blunt of a tool, since you can easily calculate the return for that trade and annualize it if necessary. – D Stanley Jan 4 at 17:30
  • In other words, use IRR at the portfolio level but just straight (or annualized) return at the individual trade level. – D Stanley Jan 4 at 17:31

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.