I have a growing list of stock purchases & sales over time.
The list indicates in order: Transaction #, date, stock name, price $, quantity, total volume $.
Each divestment or investment has a different quantities and pricing, as I am doing dollar cost averaging.
Next, I have a Profit & Loss (PnL) overview section, summarising the information of the table mentioned above.
To calculate the overall PnL of the portfolio, I need to compute the weighted average purchase and average sales price of each individual stock class first. How do I do that?
The underlying idea is to follow the FIFO (accounting; first in, first out) methodology. So when I sell a stock, I want to sell the "oldest" stock first.
Here is an example, using the same stock name only.
Day 1. My first purchase is 100 stocks at $2 so my average purchase cost is $2. Easy so far.
Day 2. I sell -50 at $3 so I have 50 left and my average cost is still $2 per share, whereas my average sales price is $3 now. Still easy.
Day 3. Now I sell the remaining -50 at $4 so I have 0 stocks left, so no inventory. Any profit/loss doesn't matter as that's a different calculation. Weighted average sales price was $3.5. Important: Since the inventory went to 0, no average should be carried forward.
Day 4. I buy 100 at $1... New average purchase cost $1 USD etc.
Day 5. ... another 200 at $1.5...
Day 6. I sell -15 at $2 and buy 25 more at $1.75 and so on.. & on.. making it more complicated to keep up.
The list indicates in order: Transaction #, date, stock name, price $, quantity, total volume $.
Each divestment or investment has a different quantities and pricing, as I am doing dollar cost averaging.
Next, I have a Profit & Loss (PnL) overview section, summarising the information of the table mentioned above.
To calculate the overall PnL of the portfolio, I need to compute the weighted average purchase and average sales price of each individual stock class first. How do I do that?
The underlying idea is to follow the FIFO (accounting; first in, first out) methodology. So when I sell a stock, I want to sell the "oldest" stock first.
Here is an example, using the same stock name only.
Day 1. My first purchase is 100 stocks at $2 so my average purchase cost is $2. Easy so far.
Day 2. I sell -50 at $3 so I have 50 left and my average cost is still $2 per share, whereas my average sales price is $3 now. Still easy.
Day 3. Now I sell the remaining -50 at $4 so I have 0 stocks left, so no inventory. Any profit/loss doesn't matter as that's a different calculation. Weighted average sales price was $3.5. Important: Since the inventory went to 0, no average should be carried forward.
Day 4. I buy 100 at $1... New average purchase cost $1 USD etc.
Day 5. ... another 200 at $1.5...
Day 6. I sell -15 at $2 and buy 25 more at $1.75 and so on.. & on.. making it more complicated to keep up.
