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
Eric W said:
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.
Click to expand...
Hi Eric.

This VBA seems interesting for my purposes but at this point it is only calculating upon selling lines.

Is it possible to change it to calculate share I am still holding? This would serve my purposes to keep tabs on what I am holding and checking avg prices before seeling them.

Here is my database:

Investimentos.xlsm
EFGHIJ
1TickerQtyPriceDateValueAvg Price
2ENBR3F26R$ 18,9026-nov-19R$ 491,40
3ITSA4F25R$ 13,3026-nov-19R$ 332,50
4WEGE3F1R$ 29,0026-nov-19R$ 29,00
5WEGE3F5R$ 29,0026-nov-19R$ 145,00
6BBDC4F20R$ 33,3328-nov-19R$ 666,60
7TIET11F26R$ 12,4728-nov-19R$ 324,22
8ENBR3F12R$ 19,9229-nov-19R$ 239,04
9ENBR3F2R$ 19,8629-nov-19R$ 39,72
10ITSA4F1R$ 13,2229-nov-19R$ 13,22
11PRIO3F22R$ 23,4129-nov-19R$ 515,02
12VULC3F3R$ 7,7629-nov-19R$ 23,28
13VULC3F24R$ 7,7529-nov-19R$ 186,00
14VULC3F1R$ 7,7529-nov-19R$ 7,75
15WEGE3F6R$ 30,3529-nov-19R$ 182,10
16TECN3F2R$ 2,0805-dez-19R$ 4,16
17BBAS3F10R$ 50,5319-dez-19R$ 505,30
18FNAM11F6R$ 0,1419-dez-19R$ 0,84
19LCAM3F24R$ 20,9319-dez-19R$ 502,32
20VULC3F3R$ 8,6919-dez-19R$ 26,07
21FNAM11F-6R$ 0,1320-dez-19-R$ 0,78R$ 0,14
22VULC3F1R$ 9,2030-dez-19R$ 9,20
23BRDT3F3R$ 30,7103-jan-20R$ 92,13
24ENBR3F10R$ 21,9303-jan-20R$ 219,30
25HABT111R$ 124,5003-jan-20R$ 124,50
26ITSA4F1R$ 14,2403-jan-20R$ 14,24
27VULC3F1R$ 8,8203-jan-20R$ 8,82
28XPCM111R$ 74,9903-jan-20R$ 74,99
29XPML112R$ 142,7703-jan-20R$ 285,54
30AALR3F7R$ 20,4310-jan-20R$ 143,01
31CCPR3F3R$ 25,4110-jan-20R$ 76,23
32CCPR3F2R$ 25,4110-jan-20R$ 50,82
33HGTX3F10R$ 31,9110-jan-20R$ 319,10
34LOGG3F2R$ 31,8910-jan-20R$ 63,78
35LOGG3F2R$ 31,8910-jan-20R$ 63,78
36LPSB3F32R$ 10,1110-jan-20R$ 323,52
37POMO4F27R$ 4,4810-jan-20R$ 120,96
38POMO4F15R$ 4,4810-jan-20R$ 67,20
39SLCE3F9R$ 25,2510-jan-20R$ 227,25
40SQIA3F8R$ 24,2810-jan-20R$ 194,24
41SQIA3F6R$ 24,3210-jan-20R$ 145,92
42SQIA3F9R$ 24,3210-jan-20R$ 218,88
43BBAS3F-7R$ 51,3010-jan-20-R$ 359,10R$ 50,53
44BBAS3F-3R$ 51,3010-jan-20-R$ 153,90R$ 50,53
45XPCM11-1R$ 74,9910-jan-20-R$ 74,99R$ 74,99
46AALR3F4R$ 21,4513-jan-20R$ 85,80
47ABEV3F12R$ 19,2913-jan-20R$ 231,48
48ABEV3F8R$ 19,2913-jan-20R$ 154,32
49BBSE3F5R$ 36,1413-jan-20R$ 180,70
50CCPR3F3R$ 25,9913-jan-20R$ 77,97
51FLRY3F3R$ 31,1613-jan-20R$ 93,48
52HYPE3F5R$ 36,9713-jan-20R$ 184,85
53LOGG3F3R$ 32,2113-jan-20R$ 96,63
54MOVI3F3R$ 20,3613-jan-20R$ 61,08
55MOVI3F6R$ 20,3813-jan-20R$ 122,28
56OFSA3F4R$ 44,5113-jan-20R$ 178,04
57POMO4F2R$ 4,5013-jan-20R$ 9,00
58QUAL3F5R$ 40,8513-jan-20R$ 204,25
59SQIA3F1R$ 24,3113-jan-20R$ 24,31
60VIVT4F5R$ 58,1713-jan-20R$ 290,85
61LCAM3F-24R$ 24,8913-jan-20-R$ 597,36R$ 20,93
62PRIO3F-35R$ 38,2513-jan-20-R$ 1.338,75 Not enough shares to cover this sale.
63TIET11F-25R$ 15,4913-jan-20-R$ 387,25R$ 12,47
64TIET11F-1R$ 15,4913-jan-20-R$ 15,49R$ 12,47
65TIET11F-1R$ 15,4913-jan-20-R$ 15,49 Not enough shares to cover this sale.
66BKBR3F30R$ 19,4516-jan-20R$ 583,50
67SHOW3F19R$ 6,2316-jan-20R$ 118,37
68SHOW3F31R$ 6,2316-jan-20R$ 193,13
69WEGE3F-10R$ 35,2816-jan-20-R$ 352,80R$ 29,54
70WEGE3F-2R$ 35,2816-jan-20-R$ 70,56R$ 30,35
71BKBR3F20R$ 18,5917-jan-20R$ 371,80
72BBDC4F-20R$ 35,3817-jan-20-R$ 707,60R$ 33,33
73ABEV3F4R$ 18,7421-jan-20R$ 74,96
74ALUP11F5R$ 28,8321-jan-20R$ 144,15
75BRSR6F5R$ 20,9421-jan-20R$ 104,70
76ITUB4F5R$ 34,1421-jan-20R$ 170,70
77OIBR4100R$ 1,5521-jan-20R$ 155,00
78ENBR3F-50R$ 23,0323-jan-20-R$ 1.151,50R$ 19,7892
79LOGG3F-7R$ 33,6423-jan-20-R$ 235,48R$ 32,02714286
80MDIA3F-3R$ 39,3123-jan-20-R$ 117,93 Not enough shares to cover this sale.
81MOVI3F-9R$ 21,4023-jan-20-R$ 192,60R$ 20,37333333
82ALUP11F-5-R$ 30,2728-jan-20R$ 151,35R$ 28,83
83ABEV3F16R$ 18,7729-jan-20R$ 300,32
84ABEV3F4R$ 18,7729-jan-20R$ 75,08
85ABEV3F2R$ 18,7729-jan-20R$ 37,54
86ABEV3F1R$ 18,7729-jan-20R$ 18,77
87HGTX3F15R$ 26,3929-jan-20R$ 395,85
88POMO4F4R$ 4,9429-jan-20R$ 19,76
89AALR3F-11-R$ 21,6505-fev-20R$ 238,15R$ 20,80090909
90CCPR3F-8R$ 26,4005-fev-20-R$ 211,20R$ 25,6275
91FLRY3F-3R$ 33,5805-fev-20-R$ 100,74R$ 31,16
92HYPE3F-5R$ 37,7905-fev-20-R$ 188,95R$ 36,97
93POMO4F-29R$ 5,2505-fev-20-R$ 152,25R$ 4,48
94POMO4F-19R$ 5,2505-fev-20-R$ 99,75R$ 4,57894737
95SQIA3F-24R$ 27,3805-fev-20-R$ 657,12R$ 24,30625
96VULC3F-35R$ 8,8205-fev-20-R$ 308,70 Not enough shares to cover this sale.
97POMO4F2R$ 5,0405-mar-20R$ 10,08
98POMO4F2R$ 3,5413-mar-20R$ 7,08
99OIBR3F1R$ 0,4724-mar-20R$ 0,47
100OIBR3F6R$ 0,5230-mar-20R$ 3,12
101OIBR3F2R$ 0,5214-abr-20R$ 1,04
102ENBR3F10R$ 16,8917-abr-20R$ 168,90
103OIBR3F4R$ 0,6517-abr-20R$ 2,60
104OIBR3F1R$ 0,6617-abr-20R$ 0,66
105OIBR3F10R$ 0,6617-abr-20R$ 6,60
106POMO4F7R$ 3,0217-abr-20R$ 21,14
107BMGB4F1R$ 4,2519-mai-20R$ 4,25
108RAPT4F1R$ 11,0926-ago-20R$ 11,09
109POMO4F2R$ 2,5328-out-20R$ 5,06
110VVAR3F1R$ 17,7328-out-20R$ 17,73
111ABEV3F-7R$ 14,6312-nov-20-R$ 102,41R$ 19,29
112ABEV3F-43R$ 14,6312-nov-20-R$ 629,09 Not enough shares to cover this sale.
113BBSE3F-5R$ 26,2812-nov-20-R$ 131,40R$ 36,14
114BMGB4F-1R$ 4,5912-nov-20-R$ 4,59R$ 4,25
115ENBR3F-10R$ 18,7512-nov-20-R$ 187,50R$ 16,89
116HABT11-1R$ 115,2512-nov-20-R$ 115,25R$ 124,50
117OIBR3F-7R$ 1,6912-nov-20-R$ 11,83R$ 0,51285714
118OIBR3F-11R$ 1,6912-nov-20-R$ 18,59R$ 0,63090909
119OIBR3F-6R$ 1,6912-nov-20-R$ 10,14R$ 0,66
120POMO4F-1R$ 2,5912-nov-20-R$ 2,59R$ 5,04
121POMO4F-12R$ 2,5912-nov-20-R$ 31,08R$ 3,19333333
122RAPT4F-1R$ 14,0612-nov-20-R$ 14,06R$ 11,09
123SLCE3F-9R$ 25,6512-nov-20-R$ 230,85R$ 25,25
124VVAR3F-1R$ 18,0912-nov-20-R$ 18,09R$ 17,73
125BKBR3F-50R$ 11,0313-nov-20-R$ 551,50R$ 19,106
126BRDT3F-3R$ 21,4113-nov-20-R$ 64,23R$ 30,71
127BRSR6F-5R$ 12,1613-nov-20-R$ 60,80R$ 20,94
128HGTX3F-25R$ 17,7013-nov-20-R$ 442,50R$ 28,598
129ITUB4F-3R$ 28,1313-nov-20-R$ 84,39R$ 34,14
130ITUB4F-2R$ 28,1313-nov-20-R$ 56,26R$ 34,14
131LPSB3F-32R$ 4,8213-nov-20-R$ 154,24R$ 10,11
132OFSA3F-4R$ 30,5113-nov-20-R$ 122,04R$ 44,51
133QUAL3F-5R$ 33,5513-nov-20-R$ 167,75R$ 40,85
134SHOW3F-11R$ 2,5313-nov-20-R$ 27,83R$ 6,23
135SHOW3F-9R$ 2,5213-nov-20-R$ 22,68R$ 6,23
136SHOW3F-20R$ 2,5213-nov-20-R$ 50,40R$ 6,23
137SHOW3F-10R$ 2,5313-nov-20-R$ 25,30R$ 6,23
138VIVT4F-1R$ 44,5013-nov-20-R$ 44,50R$ 58,17
139VIVT4F-4R$ 44,5013-nov-20-R$ 178,00R$ 58,17
140XPML11-2R$ 113,3313-nov-20-R$ 226,66R$ 142,77
141ITSA4F1R$ 10,2029-mar-21R$ 10,20
142CIEL3F1R$ 3,6220-abr-21R$ 3,62
143ITSA4F3R$ 10,1720-abr-21R$ 30,51
144CIEL3F2R$ 3,5228-abr-21R$ 7,04
145ITSA4F3R$ 10,3028-abr-21R$ 30,90
146ITSA4F13R$ 10,1303-mai-21R$ 131,69
147CIEL3F-3R$ 3,7518-mai-21-R$ 11,25R$ 3,55333333
148ITSA4F1R$ 10,7120-mai-21R$ 10,71
149SHOW3F25R$ 5,4520-mai-21R$ 136,25
150SHOW3F-1R$ 5,6904-jun-21-R$ 5,69R$ 5,45
151SHOW3F-1R$ 5,8308-jun-21-R$ 5,83R$ 5,45
152SHOW3F-23R$ 5,8208-jun-21-R$ 133,86R$ 5,45
153BMGB4F15R$ 5,0509-jun-21R$ 75,75
154GRND3F5R$ 10,0409-jun-21R$ 50,20
155MELK3F16R$ 6,8909-jun-21R$ 110,24
156MELK3F16R$ 6,9109-jun-21R$ 110,56
157GRND3F-5R$ 10,7208-jul-21-R$ 53,60R$ 10,04
158ITSA4F5R$ 11,1320-jul-21R$ 55,65
159ITSA4F1R$ 11,3903-ago-21R$ 11,39
160BOBR4F6R$ 2,0018-ago-21R$ 12,00
161ENBR3F5R$ 17,4318-ago-21R$ 87,15
162BOBR4F1R$ 1,7322-nov-21R$ 1,73
163BOBR4F4R$ 1,7503-dez-21R$ 7,00
164CIEL3F7R$ 2,3513-dez-21R$ 16,45
165ENBR3F4R$ 21,4913-dez-21R$ 85,96
166ITSA4F10R$ 9,7313-dez-21R$ 97,30
167ITSA4F1R$ 9,5413-jan-22R$ 9,54
168CIEL3F-7R$ 2,0513-jan-22-R$ 14,35R$ 2,35
169CIEL3F3R$ 2,4416-fev-22R$ 7,32
170CIEL3F-2R$ 2,8018-fev-22-R$ 5,60R$ 2,44
171CIEL3F-1R$ 2,8118-fev-22-R$ 2,81R$ 2,44
172BMGB4F5R$ 2,8523-fev-22R$ 14,25
173ABEV3F2R$ 13,6816-mar-22R$ 27,36
174CIEL3F4R$ 2,4816-mar-22R$ 9,92
Planilha8
Cell Formulas
RangeFormula
I2:I174I2=[@Qty]*[@Price]
J2:J174J2=AvgCostPerShare($E$2:H2)
 
Upvote 0

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.
Wow, you're asking me to remember a macro I wrote 4 years ago?! ?

Give this a shot:

VBA 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
Dim TotPrice As Double, TotShares As Double, TotalLeft As Double

    MyData = TblSoFar.Value
    NumRows = UBound(MyData)
    stk = MyData(NumRows, 1)
    SaleSize = -MyData(NumRows, 2)
    If MyData(NumRows, 2) >= 0 Then
        TotalLeft = 0
        For i = 1 To NumRows
            If MyData(i, 1) = MyData(NumRows, 1) Then TotalLeft = TotalLeft + MyData(i, 2)
        Next i
        If TotalLeft <= 0 Then
            AvgCostPerShare = "None left"
            Exit Function
        End If
        For i = NumRows To 1 Step -1
            If MyData(i, 2) > 0 And MyData(i, 1) = MyData(NumRows, 1) Then
                MyCount = WorksheetFunction.Min(MyData(i, 2), TotalLeft)
                TotPrice = TotPrice + MyCount * MyData(i, 3)
                TotShares = TotShares + MyCount
                TotalLeft = TotalLeft - MyCount
            End If
            If TotalLeft = 0 Then
                AvgCostPerShare = TotPrice / TotShares
                Exit Function
            End If
        Next i
'        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

Not real clean, but I THINK it works ok. Lightly tested. You can certainly use the E:G formula, but you can also use table nomenclature as follows:

Book1
EFGHIJ
1TickerQtyPriceDateValueAvg Price
2ENBR3F2618.911/26/2019491.418.9
3ITSA4F2513.311/26/2019332.513.3
4WEGE3F12911/26/20192929
5WEGE3F52911/26/201914529
6BBDC4F2033.3311/28/2019666.633.33
7TIET11F2612.4711/28/2019324.2212.47
8ENBR3F1219.9211/29/2019239.0419.2221053
9ENBR3F219.8611/29/201939.7219.254
10ITSA4F113.2211/29/201913.2213.2969231
11PRIO3F2223.4111/29/2019515.0223.41
12VULC3F37.7611/29/201923.287.76
13VULC3F247.7511/29/20191867.75111111
14VULC3F17.7511/29/20197.757.75107143
15WEGE3F630.3511/29/2019182.129.675
16TECN3F22.0812/5/20194.162.08
17BBAS3F1050.5312/19/2019505.350.53
18FNAM11F60.1412/19/20190.840.14
19LCAM3F2420.9312/19/2019502.3220.93
20VULC3F38.6912/19/201926.077.84193548
21FNAM11F-60.1312/20/2019-0.780.14
Sheet8
Cell Formulas
RangeFormula
I2:I21I2=[@Qty]*[@Price]
J2:J21J2=AvgCostPerShare(INDEX([Ticker],1):[@Price])


So using FIFO, for a sale it shows the average cost of the shares being sold, for a purchase, it shows the average price of the available shares, including the shares being purchased on that line.

Hope this helps! :biggrin:
 
Upvote 0
Eric W said:
Wow, you're asking me to remember a macro I wrote 4 years ago?! ?

Give this a shot:

VBA 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
Dim TotPrice As Double, TotShares As Double, TotalLeft As Double

    MyData = TblSoFar.Value
    NumRows = UBound(MyData)
    stk = MyData(NumRows, 1)
    SaleSize = -MyData(NumRows, 2)
    If MyData(NumRows, 2) >= 0 Then
        TotalLeft = 0
        For i = 1 To NumRows
            If MyData(i, 1) = MyData(NumRows, 1) Then TotalLeft = TotalLeft + MyData(i, 2)
        Next i
        If TotalLeft <= 0 Then
            AvgCostPerShare = "None left"
            Exit Function
        End If
        For i = NumRows To 1 Step -1
            If MyData(i, 2) > 0 And MyData(i, 1) = MyData(NumRows, 1) Then
                MyCount = WorksheetFunction.Min(MyData(i, 2), TotalLeft)
                TotPrice = TotPrice + MyCount * MyData(i, 3)
                TotShares = TotShares + MyCount
                TotalLeft = TotalLeft - MyCount
            End If
            If TotalLeft = 0 Then
                AvgCostPerShare = TotPrice / TotShares
                Exit Function
            End If
        Next i
'        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

Not real clean, but I THINK it works ok. Lightly tested. You can certainly use the E:G formula, but you can also use table nomenclature as follows:

Book1
EFGHIJ
1TickerQtyPriceDateValueAvg Price
2ENBR3F2618.911/26/2019491.418.9
3ITSA4F2513.311/26/2019332.513.3
4WEGE3F12911/26/20192929
5WEGE3F52911/26/201914529
6BBDC4F2033.3311/28/2019666.633.33
7TIET11F2612.4711/28/2019324.2212.47
8ENBR3F1219.9211/29/2019239.0419.2221053
9ENBR3F219.8611/29/201939.7219.254
10ITSA4F113.2211/29/201913.2213.2969231
11PRIO3F2223.4111/29/2019515.0223.41
12VULC3F37.7611/29/201923.287.76
13VULC3F247.7511/29/20191867.75111111
14VULC3F17.7511/29/20197.757.75107143
15WEGE3F630.3511/29/2019182.129.675
16TECN3F22.0812/5/20194.162.08
17BBAS3F1050.5312/19/2019505.350.53
18FNAM11F60.1412/19/20190.840.14
19LCAM3F2420.9312/19/2019502.3220.93
20VULC3F38.6912/19/201926.077.84193548
21FNAM11F-60.1312/20/2019-0.780.14
Sheet8
Cell Formulas
RangeFormula
I2:I21I2=[@Qty]*[@Price]
J2:J21J2=AvgCostPerShare(INDEX([Ticker],1):[@Price])


So using FIFO, for a sale it shows the average cost of the shares being sold, for a purchase, it shows the average price of the available shares, including the shares being purchased on that line.

Hope this helps! :biggrin:
Click to expand...
Thank you so much, Eric. It works like a charm! Even after 4 years you are as good as new :)

If you don´t mind, I will be working on your code to adapt to another tab where I will list my position and fetch the average price paid. I hope I will be able to do that.
 
Upvote 0
StephenCrump said:
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
Click to expand...
Hi Stephen,
I am currently dealing with a similar problem, which you solved so well a few years ago. However, what I am trying to do is to see my average cost of each stock an overview list all the time. I cannot work with the input "stock required" and would therefore need to eliminate a dynamic cumulativebuys section and instead integrate it into the average cost formula. Would that work? Do you know how to do it? Thank you very much.
 
Upvote 0
@ETH20k I would suggest that you take the time to read the rules of this site. Both your threads were cross posted without links & know you are duplicating your question.

This thread is now closed.
 
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
745
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
81
abdo meghari
A
E
  • Question Question
Weighted Average Cost based on transactions
Replies
0
Views
1K
ehsichaudhry
E

Forum statistics

Threads
1,223,264
Messages
6,171,081
Members
452,377
Latest member
bradfordsam

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