First in First out (FIFO) accounting for equity trade's book cost

**computeruser08** · 04-23-2009, 12:30 AM

Hi,

I have a number of equity trades (both purchase and sales). I need to know the book cost of those sale trades to figure out the realized gain/loss according to First-in-first-out method.

Since the unit sold may included units bought at different time and different price, so I have to first exhaust the first lot that I purchase before moving into second lot, and so on and so for.

Column G - J are for illustration.
I highlighted K10, K11 and K12 are book cost that I want to calculate. Instead of manually separating out units in each lot previously bought. Is there an automatic way? I would not mind adding new columns to ease calculation. Thanks.

**vandanavai** · 04-25-2009, 09:46 AM

Hello

see formulas in green coloured cells in attached file.
I have changed columns in file and added UDF (User Defined Function ) called 'FIFOVal' in file.
Hope this helps u.

**computeruser08** · 04-26-2009, 03:12 AM

thanks vandanavai.

it works great. Could you also tell me how can i copy your function to apply in another excel file? i have an excel holding all the trades, so i have to move your UDF to that excel.

another question, i press alt+f11 to see your module. u have three functions per below, if i am moving around columns in excel such as the sales quantity column, which function should i change to reflect that move of column?

Public Function FIFOVal(rPurchase As Range, _
rSales As Range, rUnits As Range) As Double

Private Function FIFOBal(rPurchase As Range, _
rSales As Range, rPrice As Range) As Double

Private Function FillBuckets(rPurchase As Range, _
rSales As Range) As Variant

thanks. really appreciate the help.

**vandanavai** · 04-26-2009, 03:20 AM

Hello

U can just copy UDF code by inserting module in any excel file u want. I have not developed this UDF. I found the same on internet. So I can not give much explanation on how it works.

**CJD** · 09-11-2009, 01:28 PM

I've tried to incorporate your methodology to what I'm trying to do and have been getting errors. What I'm looking to do is FIFo for equity trades in order to calculate the correct realized P&L on sells. The buys get added to unrelaized.

See attached spreadsheet.

CJD