FIFO

• Tagged with:
• Arrays,
• Excel,
• FIFO,
• INDEX,
• Inventory,
• MATCH,
• MIN,
• OFFSET,

If you’ve ever taken a basic accounting course, managed inventory, or bought and sold shares, then you will probably know about the first-in first-out (FIFO) method of calculating cost of sales.

The concept of FIFO is easy enough, but have you ever tried to get a spreadsheet to calculate your FIFO cost of sale automatically? It is surprisingly difficult. Let’s look at a simple example. Assume that we have four share transactions: 2 buys and 2 sells. The question is how do we get the spreadsheet to automatically calculate the cost of sale figures in the yellow highlighted cells?

Essentially you have two choices here. The first method is to write a macro or a user defined function (UDF) to run through your prior purchases and sales, and figure out the cost of sale of the items in question. The second way is to write a normal spreadsheet formula to do this i.e., without any background code. The latter approach is better for people who don’t like enabling macros or who aren’t comfortable with UDFs. The downside though is that you need a couple of calculation columns. Oh, and the formula is a monster!

Let’s take a look at how you can do this.

Before writing any complex formula you need to have a picture in your mind as to how you want the formula to work. In this case, we said to ourselves–for each sale row, let’s try and figure out the costs of sale for all sales to date. We thought that if we could figure this out, then we would be able to simply subtract all the previous costs of sale, to get the actual cost of sale for the row in question. With this in mind, we realised that we’d need to keep running totals of the quantity purchased and total purchase costs, so we created two new columns to contain these calculations.

Now comes the fun part. We need to write a formula that does the following things:

1. Searches through the cumulative buy quantities to date (column E) and figures out the highest number that is LESS THAN the cumulative quantity sold to date, and return the corresponding cumulative buy cost to date (column F). (We need the full amount of this bucket of costs.)
2. Calculates the difference between cumulative quantity sold to date, and the cumulative quantity purchased to date, determined in step 1. (We only take a portion of this bucket.)
3. Multiplies this marginal quantity purchased by the cost per unit for this row.
4. Subtracts all costs of sale to date, thereby leaving you with the purchase cost of the row in question.

Here’s what we came up with. (Each of the four lines of the formula reflects the corresponding steps outlined above.)

Note that this is an array formula and needs to be entered with Ctrl Shift Enter.

Admittedly, this formula is completely ridiculous–just look at the size and complexity of the thing. It’s around 270 characters long, and uses complex functions like arrays, lookups, and offsets. However, if you want to calculate FIFO in a spreadsheet you don’t have too many choices. We have seen others attempt to do this using a matrix approach, and while this simplifies the formulas required, it also blows out the size of the cells you need to do the calculations. In our view, this is a far worse outcome, as more cells means more chance of an error creeping in.

One thing in favour of the monster-formula approach is that it is very easy to test its accuracy. All you need to do is build a very small example model–like the example above–and run different buy and sell scenarios. Use a calculator or paper and pen to check if you’re getting the right result. When you’re comfortable it is working, just copy the formula and apply it to your real data.

34 Comments so far Join the Discussion

1. Brian

1st December 2010 at about 7am

Thanks for the example, the formula is a little beyond my capability so I could never create this myself. I think though that it needs to be modified slightly to take account of a sale that exceeds the origianl purchase. e.g when the sale value in B4 is changed to (110) the result in D4 should be (\$136) (100×1.20 plus 10×1.60) but the model produces a result of (\$132) ?

2. Brian

1st December 2010 at about 10pm

You are correct I had just pressed enter, when I press ctrl,shift, enter it works perfectly. Thanks Darren

3. Maria

17th December 2010 at about 4am

Hi Darren,

Your formula has been a life saver as I’ve incorporated it with my trade blotter. I have a query which I haven’t been able to solve – I have a summary sheet which shows the aggregate shares for numerous securities and is feeding off my trade blotter which has pricing and security name information. I’m looking to obtain the value in D6 above to appear in one cell. That is, I’m looking to show the following from above:

XYX 10 \$16 150 200

I’ve created a summary sheet which has all of the data other than Total Cost (the value in D6 above). I’d like the total cost to be calculated by going through the different values in my trade blotter for XYX and calculating it by the FIFO method.

I look forward to hearing from you – let me know how I can post my spreadsheet if that would be of more assistance.

4. John Smith

22nd December 2010 at about 9am

Thank you for putting forth the effort into the formula. Can I use your spreadsheet in some way to calculate the profit and loss ? I have a FIFO spreadsheet that I could send but it is not working properly, yet. Pls let me know.

5. okili

31st May 2011 at about 5pm

wow, that was exactly what I was looking for, I was trying the whole day to create something like this before I found that, I was on the right way but could not find the final solution to generalize the formulas. Thanks a lot.

6. Martin

18th November 2011 at about 3am

Hi Darren,

thanks a lot for creating this FIFO sheet. I have found several approaches to this problem, and your solution is the most elegant one. Also thanks for explaining the formula in detail. As I needed to generalize the sheet to reflect not only one item type, but different items, I needed to understand the formula. Basically I introduced an additional “item” column and extended each term of the monster with an “IF(((item array)=(current item)) …” – making Godzilla out of the smaller monster it was before!

When doing this, I found two questions I want to ask here. Both of them are non-critical, but I am curious.

1. Excel 2002 keeps complaining about circular references, even in your small example sheet. Excel 2010 seems to be fine with this, but complains when I let it calculate my Godzilla. When switching on iterations, both Excel versions give me correct results. Could you please give me some background information on how your monster creates circular references (or not)?

2. While I see the purpose of the last line of your monster, I am curious why you used the OFFSET function. To me, a more intuitive statement would have been
+SUMIF(\$D\$2:\$D2;”<0")))
in the last line of D3 (and copied to all cells below). I know that this does not work for the first row, but the first row must be a purchase anyway. Is there a specific reason to use the OFFSET function?

7. Martin

18th November 2011 at about 7am

ad 1. I guess you are right; it might have to do with my replacing a SUMIF with an array multiplication. The sheet is on the way for you to have a quick look. Thanks for offering your help. BTW, my older Excel version is “Excel 2002 (10.6501.6626) SP 3″.

ad 2. Good point, makes perfectly sense!

8. Charlie

9th February 2012 at about 6am

Thanks for this spreadsheet! I hope to use it to determine the cost basis for stock transactions and have a question about entering stock splits. The IRS seems to request using FIFO to determine the cost basis of stock transactions. Lets say I purchased 100 shares for \$1.00 each. Next transaction is a 2:1 stock split. If I enter that as receiving another 100 shares at \$0.00 cost the line at the bottom shows a quantity of 200 with a price of \$0.50 and a cost of \$100.00 which is correct. Now if I sell 150 shares a cost of \$100.00 is shown for that sale with 50 shares remaining in the account at no cost. If I sell those 50 shares with a cost of \$0.00 my gain on this transaction would be the net proceeds of these 50 shares. Is this correct accounting for the IRS?

9. Peter

25th March 2012 at about 10pm

Might be worth noting that the OFFSET function with a negative height argument is undocumented, unsupported and in fact described by Microsoft as a bug. So it could stop working at some point.
OFFSET(D2,ROW(\$D\$1)-ROW(\$D2),0,ROW(\$D2)-ROW(\$D\$1),1) seems to produce the same result with a positive height.

10. Terence

10th July 2012 at about 7pm

I wonder if you have a solution for a list of different items having different costs and that you can tell using a generalized equation or a combination of columns to know the FIFO cost everytime you sell something from that list?

11. Terence

27th July 2012 at about 8pm

Excellent, I am looking for ways to expand your excel so that it can handle different tickers at the same time. I wonder if you have any suggestions?

12. EDB

16th October 2013 at about 5am

How do I create a Google spreadsheet that calculates the cost basis for stock sales using FIFO methodology? I have the following columns for data entry: TRANSACTION_ID, TRANSACTION (BUY/SELL), TICKER, QUANTITY, PRICE, COST BASIS, PROFIT, DATE. For each unique TRANSACTION_ID where TRANSACTION=SELL the COST BASIS would be FIFO cost based on previous buy and sell transactions (for that TICKER and QUANTITY sold).

13. eing

15th January 2014 at about 7pm

I have a workbook that contain FIFO method in it,
But no one have succeed to solve it yet..
I hope there’s someone who willing to help..

14. Shyam

17th March 2014 at about 6am

I am new to excel and have spent 4 days to reverse you formula i.e. Sell on top and buy in bottom, but unable to edit it. Could you please reverse it?

15. Rick

31st March 2014 at about 10am

I would like to add a column which would allow the entry of expense for the buy or sale of a security. For example if I am using a dividend reinvestment program, some companies still have a small charge that you incur. If I buy or sell through a brokerage house, then I have an expense for buying or selling which would be added to the amount of the basis.

16. Rick

31st March 2014 at about 10am

You should assume that I know next to nothing about spreadsheets. I have been copying your cells by dragging the small box on the right for the cell immediately above it. Hopefully, I haven’t messed up the formula.

17. PrinceofExcel

1st April 2014 at about 6pm

Hi Darren !

Thanks for the super formula. Is there a way we can shorten the formula using named formula techniques and incorporate ,multiple products in the database ?

Thanks

18. Shunmas

5th April 2015 at about 5pm

Dear Darren,

Thank you for this amazing formula, a supercool way to calculate the cost under FIFO. I have one simple question, what if we have multiple products for which we want to calculate the cost. Should we use Sumif or Sumifs ? How to go about on that ? Your kind help is highly appreciated. Thank you. Profound Regards.

19. Sandeep

10th April 2015 at about 6pm

Hello, can u load up a interactive sheet for this one or a video that might explain it better ?

Thanks

20. Aijaz Khan

24th July 2015 at about 11pm

Hi Darren,
This is excellent. I was scratching my head since last 15 days to put such formula. This is working exactly.

Thank you for the effort. Appreciated.

21. Mark

30th October 2015 at about 7pm

I’ve also want to know if its possible to incorporate multiple products.

22. Anson

10th November 2015 at about 5pm

Hi Darren,

Thank you very much for your sharing. I would like to know if i have different items, how can i set the formula?

Thanks

23. JIA

13th November 2015 at about 3pm

HI THERE,

MANY THANKS

24. Ale

3rd December 2015 at about 10pm

Hi Darren, great formula!
Are you able to write the same formula for LIFO method?

Thanks!

25. ian hurwitz

23rd March 2016 at about 1am

Today for various TAX reasons, I need to compare FIFO financial results and LIFO result for decision making.
Does anybody have a template?

26. Marcin

22nd April 2016 at about 4pm

Hi Darren!
Would you by chance figured out how to set up a similar formula for a LIFO system?

Thanks!

27. Pratap Dane

4th May 2016 at about 2am

Hello Darren,
Thank you for creating this FIFO spreadsheet which help me a lot. but I want one more thing to be added bellow the total as subtotal which should show the oldest purchase balance after deducting total sales same wise the cost.
Appreciate the help!

28. Simon

29th December 2016 at about 2pm

Hey, thank you very much for this example! It is very usefull for me! Tell me please – do you have maybe similar solve for LIFO?

29. Daniel

14th January 2017 at about 3pm

This was very helpful and your solution quite clever. After spending a few hours playing with this, if I were to do this again I’d consider instead writing a VBA macro to do the trick (I assume it would take some for-loops to quickly work the FIFO accounting out).

In case it’s useful for anyone, you can eliminate the cumulative buy and quantity columns (which are a pain if tracking a lot of different products). I used the following formula (inserted in relevant places) to replace the Cumulative Buy Quantity row (and something similar for the Cumulative Buy Cost):

=MMULT(–(ROW(\$B\$3:\$B9)>=TRANSPOSE(ROW(\$B\$3:\$B9))),(\$A\$3:\$A9=\$L\$1)*(\$B\$3:\$B9>0)*\$B\$3:\$B9)

Note that I am simultaneously tracking multiple products so the Column A condition (\$A\$3:\$A9=\$L\$1) allows me to simultaneously track multiple products. \$L\$1 is just a surrogate for the product I am tracking in the above formula.

In my tinkering, I also added some other conditions to allow for other issues to account for my situation (In my case, I’m using this formula to help reconcile a trading account). So I have had a few other issues to deal with beyond the scope here.

Popup