Help calculating stock investment returns vs S&P 500

[mattmcg]

I do a bit of stock investing and am interested in comparing the individual return of a single stock vs the S&P 500. I have created a spreadsheet that works by comparing to an equivalent portfolio invested in the S&P. The spreadsheet works if you only buy currently. If you sell, you begin to collect residual shares of the equivalent S&P 500 portfolio (either too few or too many shares) which makes the calculation incorrect and does not provide for a fair comparison.

I have attached a sample to this post.

Please take a look at the example and give me your thoughts. Is there a better way to approach this? Is it possible to accommodate an equivalent portfolio based on the value of the transaction?

I greatly appreciate any assistance you could provide.

[amit.wilson]

Hello M,

the residual shares are because you aren't keeping the portfolio units fixed.

When you buy 100% of the equivalent S&P portfolio, that gives you (say) 3 equivalent S&P shares.

Then when you sell 50% of your portfolio, at that point, you should sell 50% of your equivalent portfolio, i.e. 1.5 shares of S&P equivalent shares.

The way you are doing it, the residual shares are the performance difference between the portfolio and S&P.

Cheers

[mattmcg]

Sounds like if I align the lots sold to the lots purchased, that will solve my problem.

For example.
Buy XYZ $4500 100sh - S&P 1500 3sh
Buy XYZ $3200 50sh - S&P 1600 2sh
Sell XYZ $3400 50sh - S&P 1650 -2sh <- aligned to the share lot sold and not the value of XYZ stock proceeds

I have uploaded a new spreadsheet with a real life example. This seems to capture the above correction but I am wondering if there are any improvements that can be made to automate this a bit.

Fix for Stock Return vs S&P v2.xlsx