After selling a portion of my holdings in a stock, the cost basis for the remain shares of that stock in my portfolio is not simply the sum of all transactions. When selling, I need to decide which shares I want to sell. One of the most common accounting methods is FIFO (first in, first out), meaning that the shares I bought earliest will be the shares I sell first.

As you might already know, I use Google Sheets extensively to manage my stock portfolio investment, but, at the moment of writing this post, I find that Google Sheets does not provide a built-in formula for FIFO. Luckily, with lots of effort, I succeeded in building my own FIFO solution in Google Sheets, and I want to share it on this blog.

In this post, I explain how to implement FIFO method in Google Sheets to compute cost basis in stocks investing.

## FIFO example

Firstly, I present an FIFO example to clarify the concept of this method and to use it later as test case for my FIFO formula.

Let's assume that I have bought and sold several time stocks of TotalEnergies SE (EPA:TTE). The table below presents my transactions for TotalEnergies stock.

Date | Type | Symbol | Amount | Shares |
---|---|---|---|---|

26/11/2021 | BUY | EPA:TTE | -494.26 | 12 |

19/08/2021 | BUY | EPA:TTE | -404.09 | 11 |

30/04/2021 | BUY | EPA:TTE | -479.55 | 13 |

23/03/2021 | BUY | EPA:TTE | -477.17 | 12 |

05/02/2021 | BUY | EPA:TTE | -499.99 | 14 |

07/10/2020 | BUY | EPA:TTE | -496.47 | 17 |

18/09/2020 | BUY | EPA:TTE | -474.85 | 15 |

06/07/2020 | BUY | EPA:TTE | -57.60 | 2 |

04/05/2020 | BUY | EPA:TTE | -489.85 | 16 |

02/04/2020 | SELL | EPA:TTE | 483.76 | -13 |

24/03/2020 | SELL | EPA:TTE | 482.51 | -17 |

18/03/2020 | BUY | EPA:TTE | -479.42 | 20 |

12/03/2020 | BUY | EPA:TTE | -476.35 | 17 |

24/02/2020 | BUY | EPA:TTE | -471.44 | 11 |

03/10/2019 | BUY | EPA:TTE | -497.70 | 11 |

09/05/2018 | SELL | EPA:TTE | 468.35 | -9 |

02/02/2018 | BUY | EPA:TTE | -421.80 | 9 |

I use a spreadsheet on Google Sheets to keep track of my stock portfolio's transactions. I insert a new transaction into the spreadsheet whenever I deposit money, withdraw money, use deposited money to buy stocks, receive money by selling stocks, and receive dividends by holding stocks. Each transaction is a row containing information about Date, Type, Symbol, Amount and Shares of that transaction. For further details, you should read my post Manage stock transactions with Google Sheets.

If I use the average method, dividing the total amount (**4785.92 €**) by the total number of shares (**141**), the unit cost for TotalEnergies stock in my portfolio is: `4785.92 / 141 = 33.94 €`

. However, in this case, the shares I bought first always have an impact on my current unit cost. This might raise some issues, especially in the case of long-term investment during which a stock can have a significant price change and I make many transactions (BUY and SELL) on that stock.

If I apply the FIFO accounting method, the shares I bought earliest will be the shares I sell first and the sold shares will not have any more impact on my current unit cost price. The table below shows the result of applying FIFO accounting method after each transaction. In this case, my current unit cost is actually **4605.44 / 141 = 32.66 €** that is better than **33.94 €** with the everage method above. Why that? That's because I have made some losses on some transactions but those losses are closed up and will not show up in the current situation.

Date | Type | Symbol | Amount | Shares | Transaction Unit | Remaining Shares | Cost of remaining | Cost of sold | Realized gain |
---|---|---|---|---|---|---|---|---|---|

26/11/2021 | BUY | EPA:TTE | -494.26 | 12 | -41.19 | 141 | 4605.44 | 0.00 | |

19/08/2021 | BUY | EPA:TTE | -404.09 | 11 | -36.74 | 129 | 4111.18 | 0.00 | |

30/04/2021 | BUY | EPA:TTE | -479.55 | 13 | -36.89 | 118 | 3707.09 | 0.00 | |

23/03/2021 | BUY | EPA:TTE | -477.17 | 12 | -39.76 | 105 | 3227.54 | 0.00 | |

05/02/2021 | BUY | EPA:TTE | -499.99 | 14 | -35.71 | 93 | 2750.37 | 0.00 | |

07/10/2020 | BUY | EPA:TTE | -496.47 | 17 | -29.20 | 79 | 2250.38 | 0.00 | |

18/09/2020 | BUY | EPA:TTE | -474.85 | 15 | -31.66 | 62 | 1753.91 | 0.00 | |

06/07/2020 | BUY | EPA:TTE | -57.6 | 2 | -28.80 | 47 | 1279.06 | 0.00 | |

04/05/2020 | BUY | EPA:TTE | -489.85 | 16 | -30.62 | 45 | 1221.46 | 0.00 | |

02/04/2020 | SELL | EPA:TTE | 483.76 | -13 | -37.21 | 29 | 731.61 | 438.46 | 922.22 |

24/03/2020 | SELL | EPA:TTE | 482.51 | -17 | -28.38 | 42 | 1170.06 | 754.85 | 1237.36 |

18/03/2020 | BUY | EPA:TTE | -479.42 | 20 | -23.97 | 59 | 1924.91 | 0.00 | |

12/03/2020 | BUY | EPA:TTE | -476.35 | 17 | -28.02 | 39 | 1445.49 | 0.00 | |

24/02/2020 | BUY | EPA:TTE | -471.44 | 11 | -42.86 | 22 | 969.14 | 0.00 | |

03/10/2019 | BUY | EPA:TTE | -497.7 | 11 | -45.25 | 11 | 497.70 | 0.00 | |

09/05/2018 | SELL | EPA:TTE | 468.35 | -9 | -52.04 | 0 | 0.00 | 421.80 | 890.15 |

02/02/2018 | BUY | EPA:TTE | -421.8 | 9 | -46.87 | 9 | 421.80 | 0.00 |

On the 02/02/2018, I bought 9 shares of TotalEnergies SE (EPA:TTE) then sold them all on 09/05/2018. After these 2 transactions, it's straightforward that I didn't own any more the stock and the cost of sold for 9 shares were exactly the amount that I bought them from the first place.

From 03/10/2019 until 18/03/2020, I bought 4 times shares of TotalEnergies SE (EPA:TTE) at 4 different prices (45.25 €; 42.86 €; 28.02 €; 23.97€). The cost of remaining after each of these transactions does not involve the first 2 transactions because they were closed up with the FIFO method. After the 4 BUY transactions, I had 59 shares at the cost of 1924.91€ that are both the sums of the 4 transactions.

On the 24/03/2020, I sold a 17 shares of TotalEnergies SE (EPA:TTE) but at how much cost? By applying the FIFO method, I break down the 17 shares into 2 groups:

- The first 11 shares belong to the BUY transaction on the 03/10/2019, therefore, it costs me 497.70
- The next 6 shares belong to the BUY transaction on the 24/02/2020, therefore, it costs me
`471.44 / 11 * 6 = 257.15€`

- Totally, the 17 shares costs me
`497.70 + 257.15 = 754.85€`

- After this sale, I still had
`59 - 17 = 42`

shares, of which- 5 shares were bought on 24/02/2020 for total of
`471.44 / 11 * 5 = 214.29€`

- 17 shares were bought on 12/03/2020 for total of 476.35€
- 20 shares were bought on 18/03/2020 for total of 479.42€
- Totally, the 42 shares remaining costs me 1170.06€

- 5 shares were bought on 24/02/2020 for total of

By following the same analysis with FIFO method, I can compute all the remaining quantities, cost of remaining, cost of sold, etc. after every transaction.

## How to do FIFO in Google Sheets

At the moment of writing this post, I find that Google Sheets does not support an out-of-the-box formula for FIFO accounting. However, with Apps Script, it is possible to extend Google Sheets with new custom formulas. My rule of thumb when working with Google Sheets is that: what is not available by default, I will find a solution with Apps Script.

I write an Apps Script function named FIFO accepting 2 parameters:

- The first one is a list of amounts of transactions ordered by time ascending.
- The second is a list of quantities of transactions ordered by time ascending.

This FIFO functions returns:

- The remaining quantities after the last transaction
- The cost of remaining after the last transaction
- The cost of goods sold for the last transaction if it is a SELL transaction

In the world of programming, when we talk about FIFO, we think of a queue. In my FIFO implementation, I use a queue to store the unit costs of shares. Each element in the queue represents a share and each share is associated with its unit cost.

I iterate every transaction from the oldest to the latest:

- If it is a BUY transaction, I push shares to the back of the queue.
- If it is a SELL transaction, I remove shares at the front of the queue. The sum of all removed shares is actually the cost of goods sold for this SELL transaction.

At the end of the iteration:

- The length of the queue is actually the number of remaining shares.
- The sum of the queue is actually the cost of remaining.

## How to use FIFO formula in Google Sheets

This FIFO function written in Apps Script is available for use in Google Sheets as same as any other built-in formulas.

### Simple usage

Here is how I use the FIFO formula in Google Sheets for the first 2 transactions 02/02/2018 and 09/05/2018.

=FIFO({-421.8;468.35},{9;-9})

Here is how I use the FIFO formula in Google Sheets for the first 7 transactions from 02/02/2018 until 24/03/2020.

=FIFO({-421.8;468.35;-497.7;-471.44;-476.35;-479.42;482.51},{9;-9;11;11;17;20;-17})

If I have the amounts on column D and the quantities on column E, *both already ordered ascending by time*, I can use the FIFO formula in Google Sheets as:

=FIFO(D2:D10, E2:E10)

### Use FIFO with QUERY formula

As I have a dedicated **Transactions** sheet for registering my transactions, I can apply the FIFO method for any stock on any specific date by combining it with the QUERY formula.

The idea is to use the QUERY formula to extract transactions by stock and by date, then sort them ascending by time.

Here is an example of using the FIFO formula with the QUERY formula in Google Sheets:

=FIFO(QUERY(Transactions!A:E,"select D where C='EPA:CS' and B!='DIVIDEND' and A <= date '2020-03-24' order by A asc",0), QUERY(Transactions!A:E,"select E where C='EPA:CS' and B!='DIVIDEND' and A <= date '2020-03-24' order by A asc",0))

## Demo

You can take a look and make a copy of the spreadsheet: FIFO with Google Sheets to getting started.

## Conclusion

In this post, I have explained how to apply FIFO formula in Google Sheets to compute cost basis of a stock portfolio investment. The process involves writing the FIFO function with Apps Script and then use it as a normal formula in Google Sheets. The FIFO formula returns the remaining quantities, the cost of goods sold and the cost of remaining after the last transaction.

## Comments

## Post a Comment