## LIFO Calculator

Last in First Out or LIFO is a similar method to the FIFO method described on the FIFO Calculation page. My understanding of LIFO is that goods purchased are sold bases on the price of the latest purchased product. I will use a simple example;

I will use the same example as used by the FIFO Calculator but in reverse.

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 LIFO we have a profit of:

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

or

34 - 30 + 17 - 10

=$11

So we have made a $11 profit. 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 LIFO calculation based on the attached file.

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

Sub LIFOCalc()

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

Dim n As Integer

Range("G10:G1000").ClearContents 'Clear the Sale Column

ar=Range("C10", Range("C65536").End(xlUp)) 'Purchase

Var=Range("D10", Range("D65536").End(xlUp)) 'Price

n=Cells.Find("Date").Row + 1 'start Row

sell=Range("E" & n)

j=UBound(ar, 1)

ar(j, 1)=IIf(ar(j, 1) > sell, ar(j, 1) - sell, 0)

sell=sell - (cnt - ar(j, 1))

sale=sale + (cnt - ar(j, 1)) * Var(j, 1)

j=j - 1

Range("G65536").End(xlUp)(2)=sale 'output

Next i

The following file shows a more complex example of the above