1

I have a spreadsheet that contains all my current stock positions in one sheet (with how much I invested and the unrealized gain for each current position) and another sheet with my past positions (with how much I invested and the gain I made when I sold that position). My question is: how do I calculate my ROI?

The formula I came up with is: (C+D)/((A+B)-(D+B)), which is (C+D)/(A-D)

Where: A = total invested in current positions, B = total invested in past positions, C = Unrealized gain in current positions, D = Realized gain in past positions

This assumes I have reinvested all my realized gains (including initial investments on past positions). Meaning that D is included in A. Does this make sense? I think I made a mistake somewhere, or overcomplicated things, or perhaps my assumption (D is included in A) will not always be true, but I am not sure.

Overall, I am asking if there is a "known" or "standard" way of calculating ROI if you keep track of all your positions (all transactions, everything you ever bought/sold, with dates).

Many thanks.

1
  • If I would have never sold anything ever, it would be much easier (unrealized gains / total invested), but I also made some profit when I sold stuff, then reinvested the amount I got from selling stuff. – Laplace's demon Dec 4 '20 at 16:31
0

Are these are longs? Assuming just longs, probably you would want to track the full basis for a more accurate ROI. So you need:

  1. Purchase amount
  2. Commission amount on the purchase (1) + (2) = cost basis, i.e., the full outlay you've made to purchase the equities

then

  1. Sale amount
  2. Commission amount on the sale

ROI does not take time into account, so any considerations like how long it took to realize the gain, short-term capital gains vs. long-term capital gains, FIFO, etc., are not needed here (though very important for figuring your taxes later).

So your ROI is then just

(3-2-1-4) / (1+2)

FIFO doesn't matter here for ROI, so if you purchase or sell the same stock on multiple occasions, just add everything up. ROI ignores time, so making $100 off some same-day trades is identical to $100 off something you held for 20 years.

Hope this helps. Please save a record of all trades for figuring taxes later. Your broker will archive old trades, don't count on them to have trades on the records forever.

2
  • What about the positions that I still hold? There is no sale amount or commission on sale, just "unrealized gains". Do I do the same for current positions and add them all up? It wouldn't seem right, because I would have much more money invested than I really did.. "1" can be really high because I reinvest 3+4 into more 1, which would not be accurate... – Laplace's demon Dec 4 '20 at 20:15
  • For unrealized gains you would use a market value of your choosing, for example last price or bid; no need to subtract out a hypothetical commission. If you are reinvesting that implies some dividends or sales, so there you would again count up the dividend/sale (a return) and the re-investment (an investment) in figuring return on investment. – C8H10N4O2 Dec 8 '20 at 16:19

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.