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
0

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.

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.