Hello Forum!

I hope everything is going well!

Im going through the following issue, Database attached.

I am looking for a solution of how to calculate the fifo COGS and realized P/L on this investment transactions using a fifo basis.

The calculations should take into account that these fifo cost should match each asset and the asset in each account.

For example.

Asset :"AAPL"

Account: "Daniel 1"

Sell Transaction at 28/03/2023

The sell transaction at 28/03/2023 for 400 shares should have a COGS of:

(300*100)+(50*105)+(10*103)+(40*98) = 40,200

This should go under the column "COGS" in the row of sell

Then we have a column that is called "Running Cost of Good Sold" that should be the current cost of the accumulated transaction: in these case is the shares remaining after the sell which the quantity is 60 at a price of 98 for a total of 5,880. But as the column is a running cost it should have the COGS for the asset as a running balance (each row should be filled with their respective cost). Last there is a column called "Realized P/L" That it is just the P/L (Proceed of the sell- COGS of the operation). I found a solution in another post, the problem with this (which is vba code) is that the columns should be already sorted in ASC order the vba formula didn't take into account the column date. also it matched the assets but it doesn't have a match for the accounts. as this COGS should be for multiple items and multiple accounts.

The code was the following

"Function CostOfSharesWise(PresentShares As Range, Symbol As String, DescriptionRng As Range, SharesRng As Range, DebitRng As Range, SymbolRng As Range)

Dim T As Long, Temp As Long, Amt As Double

Temp = PresentShares.Value

For T = DescriptionRng.Rows.Count To 1 Step -1
If DescriptionRng.Cells(T, 1) = "BUY" And SymbolRng(T, 1) = Symbol Then 'And SharesRng.Cells(T, 1) < Temp
If Temp >= SharesRng.Cells(T, 1) Then
Amt = Amt + DebitRng.Cells(T, 1)
Temp = Temp - SharesRng.Cells(T, 1)
Else
Amt = Amt + (Temp * (DebitRng.Cells(T, 1) / SharesRng.Cells(T, 1)))
Exit For
End If
End If

Next T
CostOfSharesWise = Amt

End Function

"

I took this solution from the following post:

1339583-help-with-fifo-u-p-l-and-r-p-l-on-multiple-stocks

Finally I attach my excel file.

It should be nice if the solution could be done through an excel formula but VBA also work I just would like a solution as i am struggling a lot with this

Thank you so muchfifo excel forum 2023 04 17.xlsx!