How to calculate ROI on my stock portfolio, from transactions logs?

Question

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.

C8H10N4O2 · Answer

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

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

then

Sale amount
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.

How to calculate ROI on my stock portfolio, from transactions logs?

One Answer

Add your own answers!

Ask a Question