Investment performance report by account is wrong

scottgallimore

There was an error rendering this rich post.

Tom Young

You are correct.  With the IRR calculation if cash flows switch signs, as yours does, you can get two different answers.  Neither Excel nor Quicken got your performance results correctly stated.  I simply went with the math, even though my common sense was saying "that does seem awfully high."

Jim_Harman

The image you posted does not show the withdrawal, but 46% looks reasonable as an IRR because the full $7300 was not in your account for the full year.

Remember the IRR is the answer to "What would the annual interest rate on a savings account (compounded daily) have to be to have the same closing balance, based on my opening balance, deposits, withdrawals, and closing balance?" 

If the money was not in your account for the full year, the return would have to be higher.

If you pulled all the money out of the account before the end of the year, the IRR would be even higher.

IRR is a helpful measure for holding periods of over 1 year, but may produce unexpected (not incorrect) results for shorter periods.

Tom Young

I duplicated your numbers for the 1st Account shown in Excel and got basically the same answer as Quicken, so the math is correct.  Over the course of less than half a year you cumulatively invested $7.3K and then a few days later pulled out $10.3K.  That's a fantastic return. 

Of course your return actually is overstated, I'd expect, because you're contributing only 85% of the stock's market value, (the 15% discount is a guess, but that's commonly used with ESPPs), and taking out stock that's fully valued.  If you combine buying stock at less than FMV with an appreciating stock, which might also be the case here, you can get some impressive "return" numbers.

scottgallimore

I only made ~$3000 on $7300, so the total return should be ~40% IMO. And Quicken reported 46%, until I transferred $10.3k cash out of the account. That's what I don't understand. The return should be the same whether I leave the money in the account or transfer it out.

Tom Young

You are correct.  With the IRR calculation if cash flows switch signs, as yours does, you can get two different answers.  Neither Excel nor Quicken got your performance results correctly stated.  I simply went with the math, even though my common sense was saying "that does seem awfully high."

q_lurker

Tom Young said:

You are correct.  With the IRR calculation if cash flows switch signs, as yours does, you can get two different answers.  Neither Excel nor Quicken got your performance results correctly stated.  I simply went with the math, even though my common sense was saying "that does seem awfully high."

@Tom Young
Can you clarify what you meant about "two different answers", and neither Excel or Quicken being correct?  Treating the beginning and contributions with one sign and and the ending values with the opposite sign seems to be the only way to do it.  For this IRR calculation, what do you think the right answer is.  I get 46.07% out of Excel's XIRR function which seems to validate Quicken's 46.03%.  What am I missing?

(Note I agree with @scottgallimore 's choice to compute that value for the full year rather than the YTD.).   

Jim_Harman

The image you posted does not show the withdrawal, but 46% looks reasonable as an IRR because the full $7300 was not in your account for the full year.

Remember the IRR is the answer to "What would the annual interest rate on a savings account (compounded daily) have to be to have the same closing balance, based on my opening balance, deposits, withdrawals, and closing balance?" 

If the money was not in your account for the full year, the return would have to be higher.

If you pulled all the money out of the account before the end of the year, the IRR would be even higher.

IRR is a helpful measure for holding periods of over 1 year, but may produce unexpected (not incorrect) results for shorter periods.

scottgallimore

> @Jim_Harman said:
> The image you posted does not show the withdrawal, but 46% looks reasonable as an IRR because the full $7300 was not in your account for the full year.
>
> Remember the IRR is the answer to "What would the annual interest rate on a savings account (compounded daily) have to be to have the same closing balance, based on my opening balance, deposits, withdrawals, and closing balance?" 
>
> If the money was not in your account for the full year, the return would have to be higher.
>
> If you pulled all the money out of the account before the end of the year, the IRR would be even higher.
>
> IRR is a helpful measure for holding periods of over 1 year, but may produce unexpected (not incorrect) results for shorter periods.

Thanks Jim! That explains why the % is way higher when I transferred most of the cash out of the account recently.

Tom Young

@scottgallimore

"Can you clarify what you meant about "two different answers", and neither Excel or Quicken being correct? "

For obscure mathematical reasons, having a sign change in a cash flow such as you had in your first post can result in the math producing two possible answers, one of which is wrong..  My reference to neither Excel nor Quicken being correct pertains to your original posts where you had that situation and the IRR was 200+ percent.  Both Quicken and XIRR gave, more or less, the same (wrong) answer.

I don't agree with @Jim_Harman s assessment that pulling the money out before the end of the year affects anything and it's not an explanation of why when you transferred the cash out the % rate is way higher. 

It's true that "less than 1 year" calculations can produce surprising results because the built-in assumption of the math is that the "short term" performance continues for the rest of the year.  If you have great short-term performance, e.g., a stock is worth 100% more one month after you bought it, the annualization of that can produce IRRs in the thousands of percent!

But in your case I believe the math bit you in the butt, more than anything else.  Try doing the IRR with "paired" Accounts - from and to - where the transfer should cancel out - and see what rates you get. Maybe that will make the numbers more reasonable, or maybe not.

q_lurker

@Tom Young  Actually, Tom, it was me who asked those questions.  I only got into this discussion after Scott edited the original, So I was commenting/asking more in the context of his 10:57 CT post and image where the vau=lues seemed to make sense.  Sounds like whatever you were commenting on got edited away from my eyes.