## 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.

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.

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.

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

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

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.

Function FIFO(ByRef Data, ByVal Stock As Double) As Double

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)

Exit Function

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.

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.