Average Cost of stock from buy/sell transactions

[Toadstool]

I have stock buy/sell transactions and want to calculate my position.
Here is some sample data:

I can calculate most things I need but I'm struggling with Average Cost.

So XXX bought 100 at $1 then sold 50 at $2, so my average cost is $1.
Then bought 100 at $2 and sold 100 at $3 so I have a Holding of 50.
I'm OK with assuming the oldest was sold first so the Average Cost of my 50 would be $2 (50 of the 7 Jan transaction).
I'm OK with assuming the newest would be sold first so the Average Cost would be $1 being 50 of the 100 bought 1 Jan.

It gets trickier with ZZZ as the 19 Jan transaction takes my holding to 0 so I really need an Average Cost of $1 being either 22 or 25 Jan buys.

It may be that I just haven't had enough coffee yet but this is hurting my head.

Any ideas for a formula/function solution? (avoiding macros or VBA).

Thanks!

 

[Marcelo Branco]

hmm...i'm confused with your logic

You said
6. My first purchase is 200 at $2 so my holding cost is $400. Easy so far.
7. Then I sell -100 at $1 so I have 100 left but they still cost me $2 each so my holding cost is $200.

I do not think so, since up to now you have spent 300 (400 to buy 200 shares at a price of $2 minus 100 when selling 100 shares at a price of $1)
For me, the cost of the remaining 100 is 300/100 = $3, not $2

I have to leave now - maybe someone else can help.

M.

 

[Toadstool]

Just to clarify you want a LIFO method here. I.e., when you sell a stock you want to sell the "oldest" stock.
I'm not sure how that's how it would be done in practice (say at Ameritrade or ETrade), but that's what you are describing. What would make it interesting was if you bought 100 for 1, then 100 more for 1.20, then sold 75, then sold 10, then sold another 30, then sold 85. These would dip into your "LIFO" layers but wouldn't so conveniently remove entire layers in a single transaction, and could remove from two layers at once. However, you can also count backwards - if the current inventory exceeds the last layer, then you have stock from two layers, and so on until the current stock is fully accounted for. Probably software that does this would re-calculate the layers on each buy or sell transaction.

I was thinking it was FIFO but yes, oldest stock sold first. There no "Share Certificate" number on the purchases so it would make more sense to clear historical records first.

Yes, your example is more interesting but I was trying to give a simple example so as to not confuse.

It seems simple if you say it quickly but this has given me a headache all day...

 

[StephenCrump]

I was thinking it was FIFO but yes, oldest stock sold first.

... which is FIFO.

Post #15 here may be helpful?

https://www.mrexcel.com/forum/excel...-net-cost-shares-excluding-sell-orders-2.html

 

[Toadstool]

... which is FIFO.

Post #15 here may be helpful?

https://www.mrexcel.com/forum/excel...-net-cost-shares-excluding-sell-orders-2.html

It's very late here... or very early... so I'll investigate further tomorrow but yes, this may be a way forward.

Thanks Stephen!

 

[xenou]

FIFO. Why do I always get LIFO/FIFO backwards
I probably think of First In as the first thing I JUST put in lol.

Note that keeping a lot number or ID for the purchases could be another way - date bought, number of shares bought, date sold, number of shares sold, with that lot number being recorded on both sides. This would require a little more record keeping but then you would only need to sum the lots with unsold shares to get the current qty and current price.

 

[Eric W]

Intriguing problem, and boy,did I try to get this into a single-celled formula! But I came up with something that may work.

	A	B	C	D	E	F	G	H	I	J	K	L
1	Stock	Qty	Price/Share	Date	Total Amt	Cost per share (FIFO)		Helper1	Helper2		Row	Avg Price/share
2	zzz	100	1	1-Jan	100			0	0		8	1.210526
3	zzz	100	1.2	2-Jan	120			85	85
4	zzz	-75	1.3	3-Jan	-97.5				0
5	zzz	-10	1.4	4-Jan	-14				0
6	zzz	-30	1.5	5-Jan	-45				0
7	zzz	50	1.3	6-Jan	65			50	10
8	zzz	-95	1.6	7-Jan	-152				0
9	yyy	100	1.7	8-Jan	170				0
10	zzz	-25	1.8	9-Jan	-45				0
11	yyy	-75	0.15	10-Jan	-11.25				0
12									0
13									0
14

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet1​

Worksheet Formulas Cell Formula E2 =B2*C2 H2 =IF(AND(B2>0,INDEX(A:A,$K$2)=A2),MEDIAN(0,B2,SUMIFS(B$2:B2,B$2:B2,">0",A$2:A2,INDEX(A:A,$K$2))+SUMIFS(OFFSET($B$1,0,0,$K$2-1),OFFSET($A$1,0,0,$K$2-1),INDEX(A:A,$K$2),OFFSET($B$1,0,0,$K$2-1),"<0")),"") I2 =IF(H2="",0,MEDIAN(0,H2,-INDEX(B:B,$K$2)-SUM($H$1:$H1))) L2 =SUMPRODUCT($C$2:INDEX(C:C,$K$2-1),$I$2:INDEX(I:I,$K$2-1))/-INDEX(B:B,$K$2) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

Put the formula in H2, copy down to the end of your data, or to as far as you think you'll get. Put in the I2 formula and copy down. Finally put in the L2 formula. These formulas will calculate the average price per share, which you can then copy to the F column.

In this example, we want to calculate the average price/share for the transaction in row 8. Enter 8 in K2. Now the values in the H column show you how many shares are available to sell. H2 shows 0, we bought 100, but sold them all in rows 4-6. H3 shows 85, since 15 were sold in row 6. Row 7 shows 50 since none of those shares have been sold yet. Now looking at column I, we find out how many we need to take from each available row to make up the 95 we're selling. 0 from row 2, since those were all sold. 85 from row 3, and 10 from row 7. Now we know how many from row 3 and row 7 we need, and the prices on those rows, so now we just create the weighted average with the formula in L2. At this point, you'd copy the value in L2 to F8.

Whew! Tricky, complicated, and maybe VBA would be better. But this does seem to work. After you set it up, you only have 2 steps, put the row in K2, then copy the value to column F. Good luck!

 

[Eric W]

Speaking of VBA, here's a UDF that should solve it. To install, open a copy of your workbook. Press Alt-F11 to open the VBA editor. From the menu, select Insert > Module. On the sheet that opens, paste this code:

Public Function AvgCostPerShare(TblSoFar As Range)
Dim MyData As Variant, NumRows As Long, stk As String, SaleSize As Double, i As Long
Dim SoldSoFar As Double, MyCount As Double, TotAmt As Double

    MyData = TblSoFar.Value
    NumRows = UBound(MyData)
    stk = MyData(NumRows, 1)
    SaleSize = -MyData(NumRows, 2)
    If MyData(NumRows, 2) >= 0 Then
        AvgCostPerShare = ""
        Exit Function
    End If
    SoldSoFar = 0
    TotAmt = 0
    
    For i = 1 To NumRows - 1
        If MyData(i, 1) = stk And MyData(i, 2) < 0 Then SoldSoFar = SoldSoFar - MyData(i, 2)
    Next i
    
    For i = 1 To NumRows - 1
        If MyData(i, 1) = stk And MyData(i, 2) > 0 Then
            If SoldSoFar >= MyData(i, 2) Then
                SoldSoFar = SoldSoFar - MyData(i, 2)
                GoTo nexti:
            End If
            MyData(i, 2) = MyData(i, 2) - SoldSoFar
            SoldSoFar = 0
            MyCount = WorksheetFunction.Min(MyData(i, 2), SaleSize)
            TotAmt = TotAmt + MyCount * MyData(i, 3)
            SaleSize = SaleSize - MyCount
            If SaleSize = 0 Then
                AvgCostPerShare = TotAmt / -MyData(NumRows, 2)
                Exit Function
            End If
        End If
nexti:
    Next i
    
    AvgCostPerShare = "Not enough shares to cover this sale."
                
End Function

Press Alt-Q to close the editor. Now given the layout of the sheet above, enter this formula in F2:

=AvgCostPerShare(A$2:C2)

drag down as needed.

 

[StephenCrump]

For what it's worth, here are my (slightly revised) formulae from the thread referred to above:

E2: =E3+IF(AND(C2>0,B2=StockRequired,A2<=TransactionRequired),C2)
C15: =INDEX(Stock,TransactionRequired)
C16: =SUMPRODUCT(Qty,--(Stock=StockRequired),--(TransactionNo<=TransactionRequired))

C17: {=IF(TotalQuantity=0,"-",SUMPRODUCT(--(ROW(Qty)>=MAX(IF(CumulativeBuys>=TotalQuantity,ROW(Qty)))),--(Qty>0),--IF(CumulativeBuys-TotalQuantity>0,Qty-CumulativeBuys+TotalQuantity,Qty),--(TransactionNo<=TransactionRequired),--(Stock=StockRequired),Price)/TotalQuantity)}

C18: =IF(TotalQuantity=0,"-",TotalQuantity*C17)

These formula calculate the average cost on a FIFO basis of the holding after the transaction, whereas Eric W calculates the average cost of the transaction. You can easily reconcile the two.

For example, my results show:

- After Transaction 6: Qty 135 @ total cost $167
- After Transaction 7: Qty 40 @ total cost $52
--> transaction = 135 -40 = 95 shares at $167 - $52 = $115
--> average cost of transacted shares = $1.21

i.e. same as Eric W's: =AvgCostPerShare(A$2:C8)

Book1
	A	B	C	D	E
1	Transaction	Stock	Qty	Price	CumulativeBuys
2	1	zzz	100	1.00	250
3	2	zzz	100	1.20	150
4	3	zzz	-75	1.30	50
5	4	zzz	-10	1.40	50
6	5	zzz	-30	1.50	50
7	6	zzz	50	1.30	50
8	7	zzz	-95	1.60	0
9	8	yyy	100	1.70	0
10	9	zzz	-25	1.80	0
11	10	yyy	-75	0.15	0
12
13	Calculate
14	TransactionRequired		7
15	StockRequired		zzz
16	TotalQuantity		40
17	Average Cost		1.30
18	Total Cost		52.00
Sheet1

 

[dylanmeza]

@Marcelo Branco that helped me a lot thank you! Now though i have another sheet that is a simplified version of my transaction sheet, that contains my total shares, P/L etc. but how can i get the most recent average value based on the same name and date onto the second sheet?

 

[Rikum]

To calculate the real AVG price maybe something like this...

	A​	B​	C​	D​	E​	F​	G​	H​
1​	Symbol​	Buy/Sell QTY​	Price​	Date​	Transaction Value​	Current QTY​	Current Cost​	Current AVG Price​
2​	XXX​	100​	1,00​	01-jan-18​	100,00​	100​	100​	1,00​
3​	XXX​	-50​	2,00​	04-jan-18​	-100,00​	50​	0​	0,00​
4​	XXX​	100​	2,00​	07-jan-18​	200,00​	150​	200​	1,33​
5​	XXX​	-100​	3,00​	10-jan-18​	-300,00​	50​	-100​	0,00​
6​	ZZZ​	200​	2,00​	13-jan-18​	400,00​	200​	400​	2,00​
7​	ZZZ​	-100​	1,00​	16-jan-18​	-100,00​	100​	300​	3,00​
8​	ZZZ​	-100​	1,00​	19-jan-18​	-100,00​	0​	200​	0,00​
9​	ZZZ​	100​	1,00​	22-jan-18​	100,00​	100​	300​	3,00​
10​	ZZZ​	200​	1,00​	25-jan-18​	200,00​	300​	500​	1,67​
11​	ZZZ​	-100​	1,00​	28-jan-18​	-100,00​	200​	400​	2,00​

<tbody>
</tbody>


Formula in F2 copied down
=SUMIF(A$2:A2,A2,B$2:B2)

Formula in G2 copied down
=SUMIF(A$2:A2,A2,E$2:E2)

Formula in H2 copied down
=IF(F2>0,MAX(0,G2/F2),0)

M.

Hi, Marcelo,

Great stuff, but formulas don't work for me. Could you share the original xlsx file? It would be very useful.