Average Cost of stock from buy/sell transactions

Status
Not open for further replies.
Toadstool

Toadstool

Well-known Member
Joined
Mar 5, 2018
Messages
2,549
Office Version
  1. 2016
Platform
  1. Windows
I have stock buy/sell transactions and want to calculate my position.
Here is some sample data:
2qv5fu0.png


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!
 
Sort by date Sort by votes
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.
 
Upvote 0

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)
xenou said:
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.
Click to expand...

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...
 
Upvote 0
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.
 
Last edited:
Upvote 0
Intriguing problem, and boy,did I try to get this into a single-celled formula! But I came up with something that may work.


ABCDEFGHIJKL
StockQtyPrice/ShareDateTotal AmtCost per share (FIFO)Helper1Helper2RowAvg Price/share
zzz
zzz
zzz
zzz
zzz
zzz
zzz
yyy
zzz
yyy

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: center"]2[/TD]

[TD="align: right"]100[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1-Jan[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"][/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]1.210526[/TD]

[TD="align: center"]3[/TD]

[TD="align: right"]100[/TD]
[TD="align: right"]1.2[/TD]
[TD="align: right"]2-Jan[/TD]
[TD="align: right"]120[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]85[/TD]
[TD="align: right"]85[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]

[TD="align: right"]-75[/TD]
[TD="align: right"]1.3[/TD]
[TD="align: right"]3-Jan[/TD]
[TD="align: right"]-97.5[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]0[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]

[TD="align: right"]-10[/TD]
[TD="align: right"]1.4[/TD]
[TD="align: right"]4-Jan[/TD]
[TD="align: right"]-14[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]0[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]

[TD="align: right"]-30[/TD]
[TD="align: right"]1.5[/TD]
[TD="align: right"]5-Jan[/TD]
[TD="align: right"]-45[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]0[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]

[TD="align: right"]50[/TD]
[TD="align: right"]1.3[/TD]
[TD="align: right"]6-Jan[/TD]
[TD="align: right"]65[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]50[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]8[/TD]

[TD="align: right"]-95[/TD]
[TD="align: right"]1.6[/TD]
[TD="align: right"]7-Jan[/TD]
[TD="align: right"]-152[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]0[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]9[/TD]

[TD="align: right"]100[/TD]
[TD="align: right"]1.7[/TD]
[TD="align: right"]8-Jan[/TD]
[TD="align: right"]170[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]0[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]10[/TD]

[TD="align: right"]-25[/TD]
[TD="align: right"]1.8[/TD]
[TD="align: right"]9-Jan[/TD]
[TD="align: right"]-45[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]0[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]11[/TD]

[TD="align: right"]-75[/TD]
[TD="align: right"]0.15[/TD]
[TD="align: right"]10-Jan[/TD]
[TD="align: right"]-11.25[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]0[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]12[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]0[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]13[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]0[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]14[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>
Sheet1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]E2[/TH]
[TD="align: left"]=B2*C2[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]H2[/TH]
[TD="align: left"]=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")),"")[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]I2[/TH]
[TD="align: left"]=IF(H2="",0,MEDIAN(0,H2,-INDEX(B:B,$K$2)-SUM($H$1:$H1)))[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]L2[/TH]
[TD="align: left"]=SUMPRODUCT($C$2:INDEX(C:C,$K$2-1),$I$2:INDEX(I:I,$K$2-1))/-INDEX(B:B,$K$2)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]



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!
 
Upvote 0
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:

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.
 
Upvote 0
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
ABCDE
1TransactionStockQtyPriceCumulativeBuys
21zzz1001.00250
32zzz1001.20150
43zzz-751.3050
54zzz-101.4050
65zzz-301.5050
76zzz501.3050
87zzz-951.600
98yyy1001.700
109zzz-251.800
1110yyy-750.150
12
13Calculate
14TransactionRequired7
15StockRequiredzzz
16TotalQuantity40
17Average Cost1.30
18Total Cost52.00
Sheet1
 
Last edited:
Upvote 0
@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?
 
Upvote 0
Marcelo Branco said:
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.
Click to expand...
Hi, Marcelo,

Great stuff, but formulas don't work for me. Could you share the original xlsx file? It would be very useful.
 
Last edited by a moderator:
Upvote 0
Status
Not open for further replies.

Similar threads

E
  • Question Question
Compute average weighted price using FIFO
Replies
1
Views
2K
Fluff
Fluff
M
  • Question Question
Book Cost Query
Replies
1
Views
757
M10Ant
M
O
  • Solved
The average price for the shares you hold
Replies
4
Views
1K
old ged
O
A
  • Solved
Track movement stock in listbox on userform by copy amounts from previous row to next row
Replies
8
Views
93
abdo meghari
A
E
  • Question Question
Weighted Average Cost based on transactions
Replies
0
Views
1K
ehsichaudhry
E

Forum statistics

Threads
1,223,903
Messages
6,175,289
Members
452,631
Latest member
a_potato

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top