I am creating a portfolio tracking model in Excel and have run into difficulty on how to track the overall performance of a single asset, given that over time more and less capital (shares) has been allocated to that asset. I want to determine the most fundamentally sound way to calculate this.
I think an example is best to illustrate this.
At t=0, 100 shares of ABC stock are purchased for \$100 per share.
At t=1, 50 shares of ABC stock, which now trades at (costs) \$200, are purchased.
At t=2, 50 shares of ABC stock, which now trades at (costs) \$300, are purchased.
At t=3, the portfolio holds 200 shares of ABC stock and ABC's current price is \$400 per share.
So clearly the price change is (400/100–1) = 300%, but this needs to be adjusted for the fact that additional shares were added at a higher price, and so the total return for the portfolio's ownership of ABC stock is lower.
How is this figure calculated in practice? Is it sound to split up the asset into three (original purchase, add 1, and add 2), calculated each's return since purchase, and weight those returns by the number of shares?
Finally, how would I handle selling shares? Say at t=3 I sold 50 shares at \$ 400 each and at t=5 each share (150 in total now) is worth \$ 500.