0

When using XIRR to calculate the performance of a stock trading account, do you need to have a beginning cash flow equal to the starting balance of the account (seed money) ?

When I do that, it throws the formula completely for a loop and I either get errors because it can't calculate the return, or the return is very, very negative despite the account actually increasing in value over the year.

$7,000 starting balance/seed money, entered as a negative, January 1st
$6,800 invested in a trade, January 2nd
$8,000 deposited from closing the trade, December 22nd.

The ROIC on the trade would be 17.6%, and the MWRR should be about 17.1%, +/- 1% since the investment period was slightly less than a full year.

But XIRR is returning -43%?! If I tell XIRR to ignore the seed money, and only look at the amount invested in the trade and the amount received when closing the trade, I get just over 18%, which seems right.

What am I not understanding about XIRR? I thought all IRR needed to include the seed money?

Rubén
  • 34,714
  • 9
  • 70
  • 166
madmoneymike5
  • 41
  • 5
  • 1
    You are going to have to explain your verbs, "invested", and "deposited". From where I'm sitting this sounds like you invested first $7,000, then $6,800 for a total of $13,800 and then at the end of December had a balance of $8,000. That sounds exactly like a 43% loss to me. I suspect in reality you are reading a brokerage statement, where you added $7,000 to a cash account, then executed a trade for $6,800 next day, then had your balance of $8,000 at year end. If that is so, then as far as return goes, the $6,800 trade is internal money movement and totally irrelevant. Leave it out. – Chris Maurer Jan 07 '23 at 03:43
  • ...[continuing]... as far as your total account return goes, you put in $7,000 and got out $8,000 and nothing else is relevant. – Chris Maurer Jan 07 '23 at 03:47
  • @ChrisMaurer The situation is the latter. Trader deposits $7,000 into the account and then executes a trade using $6,800 of the $7,000. Are you saying that every opening trade transaction should be ignored? Or is it more accurate to say I should only add to the list of cash flows the net profit/loss of each trade? ...because the P/L of each trade is what actually effects the cash position. – madmoneymike5 Jan 07 '23 at 04:20
  • 1
    The latter. Only the initial and final account values, and all the inflows and outflows to/from the account from/to the outside world are relevant. Draw a circle around your account and only count the money that crosses that circle. Anything moving inside that circle is a wash transaction done with an expectation (or hope) of increasing the final balance. – Chris Maurer Jan 07 '23 at 05:45
  • 1
    Put another way, the $6,800 trade debited cash and credited the security. The account balance did not change, so your performance to this point is the same as if you left it in cash. Now once the market value of your security starts to move, the value of your account will reflect that security, but your performance is the final account value. – Chris Maurer Jan 07 '23 at 05:56
  • @ChrisMaurer Thanks. That makes more sense. Now I have to get my Google Sheet to work with this concept. I've got a worksheet with a column for dates and amounts for contributions and withdrawals. On a separate sheet, I've got the list of trades with columns for profit/loss and closing dates. Now I need to combine both sets of columns into a single set of columns of dates and amounts on a third sheet, creating a combined list cash flows. From that I can run the XIRR formula. The trick is getting all the dates and their respective transactions in chronological order. – madmoneymike5 Jan 07 '23 at 06:41

0 Answers0