The Last In, First Out (LIFO) method is an accounting method used to calculate the cost basis of a stock portfolio. It is the opposite of the First In, First Out (FIFO) method, where the oldest shares are sold first. In the LIFO method, the most recent shares are sold first.
In the previous post Compute cost basis of stocks with FIFO method in Google Sheets, I explained how to implement FIFO method in Google Sheets to compute cost basis in stocks investing.
In this post, I explain how to implement LIFO method in Google Sheets to compute cost basis in stocks investing.
Table of Contents
LIFO example
Firstly, I present an LIFO example to clarify the concept of this method and to use it later as test case for my LIFO 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 |
If I apply the LIFO accounting method, the shares I bought latest 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 LIFO accounting method after each transaction.
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 | 5039.11 | 0 | |
19/08/2021 | BUY | EPA:TTE | -404.09 | 11 | -36.74 | 129 | 4544.85 | 0 | |
30/04/2021 | BUY | EPA:TTE | -479.55 | 13 | -36.89 | 118 | 4140.76 | 0 | |
23/03/2021 | BUY | EPA:TTE | -477.17 | 12 | -39.76 | 105 | 3661.21 | 0 | |
05/02/2021 | BUY | EPA:TTE | -499.99 | 14 | -35.71 | 93 | 3184.04 | 0 | |
07/10/2020 | BUY | EPA:TTE | -496.47 | 17 | -29.2 | 79 | 2684.05 | 0 | |
18/09/2020 | BUY | EPA:TTE | -474.85 | 15 | -31.66 | 62 | 2187.58 | 0 | |
06/07/2020 | BUY | EPA:TTE | -57.6 | 2 | -28.8 | 47 | 1712.73 | 0 | |
04/05/2020 | BUY | EPA:TTE | -489.85 | 16 | -30.62 | 45 | 1655.13 | 0 | |
02/04/2020 | SELL | EPA:TTE | 483.76 | -13 | -37.21 | 29 | 1165.28 | 352.12 | 131.64 |
24/03/2020 | SELL | EPA:TTE | 482.51 | -17 | -28.38 | 42 | 1517.4 | 407.51 | 75 |
18/03/2020 | BUY | EPA:TTE | -479.42 | 20 | -23.97 | 59 | 1924.91 | 0 | |
12/03/2020 | BUY | EPA:TTE | -476.35 | 17 | -28.02 | 39 | 1445.49 | 0 | |
24/02/2020 | BUY | EPA:TTE | -471.44 | 11 | -42.86 | 22 | 969.14 | 0 | |
03/10/2019 | BUY | EPA:TTE | -497.7 | 11 | -45.25 | 11 | 497.7 | 0 | |
09/05/2018 | SELL | EPA:TTE | 468.35 | -9 | -52.04 | 0 | 0 | 421.8 | 46.55 |
02/02/2018 | BUY | EPA:TTE | -421.8 | 9 | -46.87 | 9 | 421.8 | 0 |
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 LIFO 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 LIFO method, those 17 shares all belong to the to the BUY transaction on the 18/03/2019, therefore, it costs me 479.42 / 20 * 17 = 407.51€. After this sale, I still had 59 - 17 = 42 shares, of which:
- 3 shares were bought on 18/03/2020 for total of 479.42 / 20 * 3 = 71.913€
- 17 shares were bought on 12/03/2020 for total of 476.35€
- 11 shares were bought on 24/02/2020 for total of 471.44€
- 11 shares were bought on 03/10/2019 for total of 497.70€
- Totally, the 42 shares remaining costs me 1517.40€
By following the same analysis with LIFO method, I can compute all the remaining quantities, cost of remaining, cost of sold, etc. after every transaction.
How to do LIFO in Google Sheets
I write an Apps Script function named LIFO 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 LIFO 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 LIFO, we think of a queue. In my LIFO 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 end 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.
Notes
- Google Apps Script is actually JavaScript
- In JavaScript, we can use array to present a queue
- In case of LIFO, to remove an element from the end of an array, we use the pop() method
How to use LIFO formula in Google Sheets
This LIFO 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 LIFO formula in Google Sheets for the first 2 transactions 02/02/2018 and 09/05/2018.
=LIFO({-421.8;468.35},{9;-9})
Here is how I use the LIFO formula in Google Sheets for the first 7 transactions from 02/02/2018 until 24/03/2020.
=LIFO({-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 LIFO formula in Google Sheets as:
=LIFO(D2:D10, E2:E10)
Use LIFO with QUERY formula
As I have a dedicated Transactions sheet for registering my transactions, I can apply the LIFO 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 LIFO formula with the QUERY formula in Google Sheets:
=LIFO(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
Conclusion and comparison between FIFO and LIFO
In this post, I have explained how to apply LIFO(Last In, First Out) formula in Google Sheets to compute cost basis of a stock portfolio investment. The process involves writing the LIFO function with Apps Script and then use it as a normal formula in Google Sheets. The LIFO formula returns the remaining quantities, the cost of goods sold and the cost of remaining after the last transaction.
To compare the differences between FIFO and LIFO, please read also to my previous post about Compute cost basis of stocks with FIFO method in Google Sheets. The picture below shows an example of difference between these two accounting methods.
Disclaimer
The post is only for informational purposes and not for trading purposes or financial advice.
Feedback
If you have any feedback, question, or request please:
- leave a comment in the comment section
- write me an email to allstacksdeveloper@gmail.com
Support this blog
If you value my work, please support me with as little as a cup of coffee! I appreciate it. Thank you!
Share with your friends
If you read it this far, I hope you have enjoyed the content of this post. If you like it, share it with your friends!
Comments
Post a Comment