FIFO Calculator in Excel

Many years ago on a friend's father asked me to create a First in First Out FIFO calculator for stocks which he held.  It appears the taxation on stock purchases becomes increasingly difficult when you are purchasing the same stock multiple times.  Tracking the P&L on a stock becomes difficult based on a first in first out (FIFO) basis. This article will focus on some solutions to the FIFO conundrum.

My understanding of FIFO is that goods purchased are sold bases on the price of the earliest purchased product.  I will use a simple example;

We buy 2 stocks for a price of $10 each.  On Tuesday we purchase 2 more stocks for $15 each.  Now on Wednesday we sell 3 of the stocks for $17 each.  We need to work out the profit or loss for taxation purposes.  If we use FIFO we have a profit of:

17 * 2 - 10 * 2 + 1 * 17 - 1 * 15

or

34 - 20 + 17 - 15

=$16

So we have made a $16 profit according to FIFO.  This becomes more and more difficult to track the more items which are purchased and sold for different prices.

 The following is a more complex FIFO calculation based on the attached file.

FIFO Excel calculator

This is where Excel VBA can help.  The following is a very handy FIFO procedure which will sort the closing price for a FIFO calculation.

Option Explicit
Sub FIFOCalc() 'Excel VBA to calculate FIFO on goods bought and sold.
Dim sell As Long
Dim i As Integer
Dim j As Integer
Dim cnt As Long
Dim sale As Double
Dim ar As Variant
Dim Var As Variant

Range("G10:G1000").ClearContents
ar= Range("C10", Range("C65536").End(xlUp)) 'Purchase
Var=Range("D10", Range("D65536").End(xlUp)) 'Price
For i=10 To Range("A" & Rows.Count).End(xlUp).Row
sale=0 sell=Range("E" & i) j=1
Do While sell > 0
cnt=ar(j, 1)
ar(j, 1)=IIf(ar(j, 1) > sell, ar(j, 1) - sell, 0) 'iif
sell=sell - (cnt - ar(j, 1))
sale=sale + (cnt - ar(j, 1)) * Var(j, 1)
j=j + 1
Loop
Range("G65536").End(xlUp)(2)=sale 'output
Next i
End Sub

The following Excel file shows a more complex example of the above FIFO method using VBA.

FIFO Function in Excel

While working with the CPA for a webinar I extended the FIFO calculation into a custom function.  It is quite straight forward.  Here is some raw data, we can use this data to calculate FIFO.  The custom function will do the work for us.

Now let's say we need to calculate the FIFO on the following:

Oct   USB Stick    420    = ??????

SO we need to work out what the cost of goods sold is for 420 items at 4 different price points.

80 x 1.10 = $88
200 x 8 1.05 = $210
120 x 1.10 = $132
20 x 1.03 = $20.60

Total  = $450.60

Once again performing the FIFO calculation manually is a laborious task, especially if you have to do this for multiple items or multiple dates.

Here is a solution using a custom FIFO function.

Option Explicit 'FIFO Calculation using a custom function

Function FIFO(ByRef Data, ByVal Stock As Double) As Double
Dim ar As Variant
Dim i As Long
Const QtyCol As Long = 1 'COl number of quantity column for the FIFO calc
Const CostCol As Long = 2 'COl number of cost column for the FIFO calc

ar = Data

For i = LBound(ar, 1) To UBound(ar, 1)
If Stock < ar(i, QtyCol) Then
FIFO = FIFO + Stock * ar(i, CostCol)
Exit Function
Else
FIFO = FIFO + (ar(i, QtyCol) * ar(i, CostCol)) Stock = Stock - ar(i, QtyCol) If Stock <= 0 Then Exit Function
End If
Next i

End Function

The following is the FIFO formula:

=FIFO(ExA!$C$2:$D$6,C2)

Where C2:D6 is the quantity and the cost (in that order).  And C2 contains the FIFO quantity sold.

The attached file shows basic and more advanced FIFO calculation.  It takes the simple example above a step further.  It is very useful and can be extended a lot further if need be.  The FIFO function provided above is a small glimpse into the world of the First in First Out inventory calculation.

fifo in Excel

The above is an example of the results from the file. It takes the concept a step further to calculate the value of the remaining inventory. It shows how the FIFO Function can be extended to multiple criteria and products.