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.

Answer 1

I know this is an old question but you might still find it helpful. Generally, you'd use either the Time-Weighted or Money-Weighted Rate of Return. The former has the advantage that it's not skewed by deposits/withdrawals:

The Difference Between Money-Weighted Rate of Return and Time-Weighted Rate of Return The money-weighted rate of return is often compared to the time-weighted rate of return (TWRR), but the two calculations have distinct differences. The TWRR is a measure of the compound rate of growth in a portfolio. The TWRR measure is often used to compare the returns of investment managers because it eliminates the distorting effects on growth rates created by inflows and outflows of money.

It can be difficult to determine how much money was earned on a portfolio because deposits and withdrawals distort the value of the return on the portfolio. Investors can't simply subtract the beginning balance, after the initial deposit, from the ending balance since the ending balance reflects both the rate of return on the investments and any deposits or withdrawals during the time invested in the fund.

The TWRR breaks up the return on an investment portfolio into separate intervals based on whether money was added or withdrawn from the fund. The MWRR differs in that it takes into account investor behavior via the impact of fund inflows and outflows on performance but doesn’t separate the intervals where cash flows occurred like the TWRR. Therefore, cash outlays or inflows can impact the MWRR. If there are no cash flows, then both methods should deliver the same or similar results.

Answer 2

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

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