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.

Option Explicit
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

For i=Range("A" & Rows.Count).End(xlUp).Row To n Step -1
sale=0
sell=Range("E" & n)
j=UBound(ar, 1)
Do While sell > 0
cnt=ar(j, 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
Loop
n=n + 1
Range("G65536").End(xlUp)(2)=sale 'output
Next i
End Sub

The following file shows a more complex example of the above