Fair Warning: This is a spreadsheet-based method to calculate the purchase value of shares or inventory using the FIFO, first-in-first-out method. You will NOT be able to calculate profit or loss using this.
First-in-first-out or FIFO is a method of inventory management. In this method, when you sell a unit, this sale is adjusted against the first unit purchased.
Another method is LIFO or last-in-first-out where the last unit bought is considered sold first.
The objective is to figure out the value of the unsold units. And to be able to identify which units (i.e. buying date and buying price) are still in inventory.
For this exercise, I have taken a bunch of trades on Tesla shares:
The number of shares held in April 2021 is straightforward:
What is the value of the 21 shares held in April 2021?
Using FIFO you need to adjust the sold shares against the shares bought.
The 8 shares sold on 1st October 2020 need to be adjusted against the 15 shares bought on 4th May 2020.
Start by calculating the total number of shares sold. Easy: 40 shares sold.
Now go row by row from the top and start adjusting these sold shares against the purchased shares:
The 8 shares sold on 1st October 2020 and the 7 shares sold on the 15th of October are adjusted against the 15 shares bought on 4th May 2020.
Of the 25 shares sold on 2nd November 2020,
- 12 are against the purchase on 1st June 2020
- 10 against the purchase on 1st July 2020
- and 3 against the purchase of 7 shares on 3rd August 2020. This leaves a balance of 4 shares from this purchase.
Multiply the outstanding shares against their purchase price and add it up. That gets you the purchase value of the shares held.
The formulas used in this calculation are fairly straightforward.
You can make your own copy of this spreadsheet. If you have questions let me know in the comments or on Twitter.
Note on column headers in the spreadsheet
Unadj. sold shares: Unadjusted sold shares. Sold shares that have not been adjusted against bought shares.
Adj. bought shares (total): Cumulative bought shares after adjusting for sales.
Adj. bought shares (trans.): Shares bought in a particular transaction after adjusting for sales.
