Logic help for variable weighted average

Chris Macro

Chris Macro

Well-known Member
Joined
Nov 2, 2011
Messages
1,345
Office Version
  1. 365
Platform
  1. Windows
My main goal is to calculate a weighted average purchase price that is variable and I not really sure how to tackle this. I have made a simple example and hopefully you all will be able to follow along (let me know if you need more details)

Example: I have 4 types of stock (Financial, Banking, Industrial, Technology) that I may purchase. If I decide to buy/sell these stocks it is recorded in this table:

<b>Excel 2007</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>T</th><th>U</th><th>V</th><th>W</th><th>X</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="text-align: center;color: #FFFFFF;background-color: #1F497D;;">Age</td><td style="text-align: center;color: #FFFFFF;background-color: #1F497D;;">Buy/Sell</td><td style="text-align: center;color: #FFFFFF;background-color: #1F497D;;">Stocks</td><td style="text-align: center;color: #FFFFFF;background-color: #1F497D;;">Quantity</td><td style="text-align: center;color: #FFFFFF;background-color: #1F497D;;"> Purchase Price </td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="text-align: center;;">25</td><td style="text-align: center;;">Bought</td><td style=";">Industrial</td><td style="text-align: center;;">10</td><td style="text-align: right;;"> $ 10.00 </td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="text-align: center;;">27</td><td style="text-align: center;;">Bought</td><td style=";">industrial</td><td style="text-align: center;;">5</td><td style="text-align: right;;"> $ 6.00 </td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="text-align: center;;">27</td><td style="text-align: center;;">Bought</td><td style=";">Financial</td><td style="text-align: center;;">6</td><td style="text-align: right;;"> $ 20.00 </td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="text-align: center;;">28</td><td style="text-align: center;;">Sold</td><td style=";">Industrial</td><td style="text-align: center;;">-11</td><td style="text-align: right;;"> $ 12.00 </td></tr></tbody></table><p style="width:4.8em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">Player 1</p><br /><br />

If I purchase more of a stock in a different year I want to be able to calculate the weighted average to get a more realistic price. Logic for the first two purchases of Industrial stock would be (10/15)($10) +(5/15)($6) = $8.67. Now if I were to sell 11 shares, I need the logic to determine that my ten $10 shares are gone and that I only have four $6 shares left.

I am assuming I need to start by looking up a stock based on if its a "Bought or Sold" and its name. From there I am not quite sure how to handle coding this. Please let me know if you have any suggestions
 

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying
Sort by date Sort by votes
Does this do what you want?

Excel Workbook
ABCDE
1AGEBSSectorQtyPrice
225BoughtIndustrial1010.00
327Boughtindustrial56.00
427BoughtFinancial620.00
528SoldIndustrial-1112.00
6
7
8Weight Adjusted Cost
9Industrial-0.5
10Financial20
Sheet2
Excel 2007
Cell Formulas
RangeFormula
C9=SUMPRODUCT(--($C$2:$C$5=B9),$D$2:$D$5,$E$2:$E$5)/SUMIF($C$2:$C$5,B9,$D$2:$D$5)



Regards
Adam
 
Upvote 0
Hi Adam, I don't believe that I will be able to use a worksheet formula, as my table is going to be populated over time and will increase in rows. I also can't have a negative weighted purchase price amount. I am looking for a VBA solution because going forward I believe it would be the most effective way for solving theses equations
 
Upvote 0
I think forumla is better (although I'll be biased lol). We can set it up with dynamic named ranges to account for the changing ranges.

And if you don't want negative weighted prices, what do you want to do when you're already in profit but you still own shares?

Thanks
Adam
 
Upvote 0
Ok, so here is where the overall information with be displayed (disregard the numbers as I changed them in my example to make it a little bit easier to go through the logic):

<b>Excel 2007</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>F</th><th>G</th><th>H</th><th>I</th><th>J</th><th>K</th><th>L</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">3</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #FFFFFF;background-color: #1F497D;;">Stocks</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-left: 1px solid black;text-decoration: underline;background-color: #FFFFFF;;">Quantity</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;text-decoration: underline;background-color: #FFFFFF;;">Purchase Price</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;text-decoration: underline;background-color: #FFFFFF;;">Current Price</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;text-decoration: underline;background-color: #FFFFFF;;">Total Value</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;text-decoration: underline;background-color: #FFFFFF;;">This Year's %?</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;text-decoration: underline;background-color: #FFFFFF;;">Gain/Loss</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="font-weight: bold;text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-left: 1px solid black;background-color: #EAF1DD;;">Financial</td><td style="text-align: center;border-left: 1px solid black;background-color: #FFFFFF;;">5</td><td style="text-align: center;background-color: #FFFFFF;;"> $ 93 </td><td style="text-align: center;background-color: #FFFFFF;;"> $ 93.15 </td><td style="text-align: center;background-color: #FFFFFF;;"> $ 466 </td><td style="text-align: center;background-color: #FFFFFF;;">-10%</td><td style="text-align: center;border-right: 1px solid black;background-color: #FFFFFF;;"> $ 1 </td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="font-weight: bold;text-align: right;border-right: 1px solid black;border-left: 1px solid black;background-color: #EAF1DD;;">Banking</td><td style="text-align: center;border-left: 1px solid black;background-color: #FFFFFF;;">10</td><td style="text-align: center;background-color: #FFFFFF;;"> $ 50 </td><td style="text-align: center;background-color: #FFFFFF;;"> $ 64.69 </td><td style="text-align: center;background-color: #FFFFFF;;"> $ 647 </td><td style="text-align: center;background-color: #FFFFFF;;">25%</td><td style="text-align: center;border-right: 1px solid black;background-color: #FFFFFF;;"> $ 147 </td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style="font-weight: bold;text-align: right;border-right: 1px solid black;border-left: 1px solid black;background-color: #EAF1DD;;">Industrial</td><td style="text-align: center;border-left: 1px solid black;background-color: #FFFFFF;;">10</td><td style="text-align: center;background-color: #FFFFFF;;"> $ 52 </td><td style="text-align: center;background-color: #FFFFFF;;"> $ 52.44 </td><td style="text-align: center;background-color: #FFFFFF;;"> $ 524 </td><td style="text-align: center;background-color: #FFFFFF;;">15%</td><td style="text-align: center;border-right: 1px solid black;background-color: #FFFFFF;;"> $ 4 </td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style="font-weight: bold;text-align: right;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #EAF1DD;;">Technology</td><td style="text-align: center;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFFFF;;">0</td><td style="text-align: center;border-bottom: 1px solid black;background-color: #FFFFFF;;"> $ - </td><td style="text-align: center;border-bottom: 1px solid black;background-color: #FFFFFF;;"> $ 15.39 </td><td style="text-align: center;border-bottom: 1px solid black;background-color: #FFFFFF;;"> $ - </td><td style="text-align: center;border-bottom: 1px solid black;background-color: #FFFFFF;;">-5%</td><td style="text-align: center;border-right: 1px solid black;border-bottom: 1px solid black;background-color: #FFFFFF;;"> $ - </td></tr></tbody></table><p style="width:4.8em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">Player 1</p><br /><br />


All that I am trying to do is change the purchase price in this table based on the historical data I will be collecting in the first table that I had posted. Once the stock is sold, the sale amount is placed in a separated cell that tallies the total money earned (so I am not too worried about calculating profit at this point). Going with my example after the 4 transactions cell H6 should equal $6 and Cell G6 should equal 4 shares. Cell G4 = 6 shares and H4= $20. Does that make a little more sense?
 
Upvote 0
Newguy135 said:
My main goal is to calculate a weighted average purchase price that is variable and I not really sure how to tackle this. I have made a simple example and hopefully you all will be able to follow along (let me know if you need more details)

Example: I have 4 types of stock (Financial, Banking, Industrial, Technology) that I may purchase. If I decide to buy/sell these stocks it is recorded in this table:


Excel 2007
TUVWX
1AgeBuy/SellStocksQuantityPurchase Price
225BoughtIndustrial10$ 10.00
327Boughtindustrial5$ 6.00
427BoughtFinancial6$ 20.00
528SoldIndustrial-11$ 12.00
Player 1


If I purchase more of a stock in a different year I want to be able to calculate the weighted average to get a more realistic price. Logic for the first two purchases of Industrial stock would be (10/15)($10) +(5/15)($6) = $8.67. Now if I were to sell 11 shares, I need the logic to determine that my ten $10 shares are gone and that I only have four $6 shares left.

I am assuming I need to start by looking up a stock based on if its a "Bought or Sold" and its name. From there I am not quite sure how to handle coding this. Please let me know if you have any suggestions
Click to expand...

On the assumption that purchase price are price per share.. the formula will compute for moving average price every time you bought a stock..


Excel 2010
ABCDEF
1AgeBuy/SellStocksQuantityPurchase PriceWeightedAve.Price
225BoughtIndustrial10$10.0010.0000
327Boughtindustrial5$6.008.6667
427BoughtFinancial6$20.0020.0000
528SoldIndustrial-11$12.008.6667
Sheet2
Cell Formulas
RangeFormula
F2=SUMPRODUCT(--($B$2:B2="Bought"),--($C$2:C2=C2),$D$2:D2,$E$2:E2)/SUMPRODUCT(--($B$2:B2="Bought"),--($C$2:C2=C2),$D$2:D2)
 
Upvote 0
MrVillareal -- I'm loving the first 3 lines of your solution! Do you have any idea how I could handle the "Sold" stocks?? Possible on a first in (purchased) first out (sold) basis?
 
Upvote 0
Newguy135 said:
MrVillareal -- I'm loving the first 3 lines of your solution! Do you have any idea how I could handle the "Sold" stocks?? Possible on a first in (purchased) first out (sold) basis?
Click to expand...

If you are using average price for bought stocks you should also use average price as basis for your gain or loss on sale..that is why with my presentation I put the average price for sold stock so could easily determine your gain or loss on sale for a particular stock..the sold price for is 12 less average price of 8.67 as gain on sale.
 
Last edited:
Upvote 0
ok, I believe I understand you logic there. Now I think I'm getting confused with all these different numbers. Is my logic correct (see below)


Excel 2007
TUVWXYZ
1AgeBuy/SellStocksQuantityPurchase PriceTotalWght Price
225BoughtIndustrial-10$ 10.00$ (100.00)$ 10.00
327Boughtindustrial-5$ 6.00$ (30.00)$ 8.67
427BoughtFinancial-6$ 20.00
528SoldIndustrial11$ 12.00$ 132.00$ 8.67
630Boughtindustrial-20$ 50.00$ (1,000.00)$ 32.29
732Soldindustrial20$ 60.00$ 1,200.00$ 32.29
8Profit?$ 202.00
9
10
11Sold118.67$ 95.37
12Sold2032.29$ 645.80
13Current432.29$ 129.16
14Total Value$ 870.33
Player 1
 
Upvote 0
to accurately measure the sales price, cost, and profit:


Excel 2010
IJKLMNOPQRSTU
1StockBuySoldBalanceSold Price
2QuantityPrice/ShareTotal CostQuantityPrice/ShareTotal CostQuantityPrice/ShareTotal CostPrice/ShareTotal PriceGain (Loss)
3Industrial10$ 10.00$ 100.00  10$ 10.00$ 100.00  
4Industrial5$ 6.00$ 30.00  15$ 8.67$ 130.00  
5Financial6$ 20.00$ 120.00  6$ 20.00$ 120.00  
6Industrial 11$ 8.67$ 95.33   $ 12.00$ 132.00$ 36.67
7Industrial20$ 50.00$ 1,000.00  24$ 43.11$ 1,034.67  
8Financial 20$ 20.00$ 400.00   $ 60.00$ 1,200.00$ 800.00
Sheet4
Cell Formulas
RangeFormula
O3=IF(M3="","",PRODUCT(M3:N3))
O4=IF(M4="","",PRODUCT(M4:N4))
O5=IF(M5="","",PRODUCT(M5:N5))
O6=IF(M6="","",PRODUCT(M6:N6))
O7=IF(M7="","",PRODUCT(M7:N7))
O8=IF(M8="","",PRODUCT(M8:N8))
P3=IF(J3<>"",SUMIF($I$3:I3,$I3,J$3:J3)-SUMIF($I$3:I3,$I3,M$3:M3),"")
P4=IF(J4<>"",SUMIF($I$3:I4,$I4,J$3:J4)-SUMIF($I$3:I4,$I4,M$3:M4),"")
P5=IF(J5<>"",SUMIF($I$3:I5,$I5,J$3:J5)-SUMIF($I$3:I5,$I5,M$3:M5),"")
P6=IF(J6<>"",SUMIF($I$3:I6,$I6,J$3:J6)-SUMIF($I$3:I6,$I6,M$3:M6),"")
P7=IF(J7<>"",SUMIF($I$3:I7,$I7,J$3:J7)-SUMIF($I$3:I7,$I7,M$3:M7),"")
P8=IF(J8<>"",SUMIF($I$3:I8,$I8,J$3:J8)-SUMIF($I$3:I8,$I8,M$3:M8),"")
Q3=IFERROR(R3/P3,"")
Q4=IFERROR(R4/P4,"")
Q5=IFERROR(R5/P5,"")
Q6=IFERROR(R6/P6,"")
Q7=IFERROR(R7/P7,"")
Q8=IFERROR(R8/P8,"")
R3=IF(J3<>"",SUMIF($I$3:I3,I3,$L$3:L3)-SUMIF($I$3:I3,I3,$O$3:O3),"")
R4=IF(J4<>"",SUMIF($I$3:I4,I4,$L$3:L4)-SUMIF($I$3:I4,I4,$O$3:O4),"")
R5=IF(J5<>"",SUMIF($I$3:I5,I5,$L$3:L5)-SUMIF($I$3:I5,I5,$O$3:O5),"")
R6=IF(J6<>"",SUMIF($I$3:I6,I6,$L$3:L6)-SUMIF($I$3:I6,I6,$O$3:O6),"")
R7=IF(J7<>"",SUMIF($I$3:I7,I7,$L$3:L7)-SUMIF($I$3:I7,I7,$O$3:O7),"")
R8=IF(J8<>"",SUMIF($I$3:I8,I8,$L$3:L8)-SUMIF($I$3:I8,I8,$O$3:O8),"")
T3=IF(S3="","",PRODUCT(M3,S3))
T4=IF(S4="","",PRODUCT(M4,S4))
T5=IF(S5="","",PRODUCT(M5,S5))
T6=IF(S6="","",PRODUCT(M6,S6))
T7=IF(S7="","",PRODUCT(M7,S7))
T8=IF(S8="","",PRODUCT(M8,S8))
U3=IF(T3="","",T3-O3)
U4=IF(T4="","",T4-O4)
U5=IF(T5="","",T5-O5)
U6=IF(T6="","",T6-O6)
U7=IF(T7="","",T7-O7)
U8=IF(T8="","",T8-O8)
L3=IF(J3="","",PRODUCT(J3:K3))
L4=IF(J4="","",PRODUCT(J4:K4))
L5=IF(J5="","",PRODUCT(J5:K5))
L6=IF(J6="","",PRODUCT(J6:K6))
L7=IF(J7="","",PRODUCT(J7:K7))
L8=IF(J8="","",PRODUCT(J8:K8))
N3{=IF(M3="","",INDEX($Q$3:Q3,LARGE(IF($I$3:I3=I3,IF($Q$3:Q3<>"",ROW($I$3:I3)-ROW($I$3)+1)),1)))}
N4{=IF(M4="","",INDEX($Q$3:Q4,LARGE(IF($I$3:I4=I4,IF($Q$3:Q4<>"",ROW($I$3:I4)-ROW($I$3)+1)),1)))}
N5{=IF(M5="","",INDEX($Q$3:Q5,LARGE(IF($I$3:I5=I5,IF($Q$3:Q5<>"",ROW($I$3:I5)-ROW($I$3)+1)),1)))}
N6{=IF(M6="","",INDEX($Q$3:Q6,LARGE(IF($I$3:I6=I6,IF($Q$3:Q6<>"",ROW($I$3:I6)-ROW($I$3)+1)),1)))}
N7{=IF(M7="","",INDEX($Q$3:Q7,LARGE(IF($I$3:I7=I7,IF($Q$3:Q7<>"",ROW($I$3:I7)-ROW($I$3)+1)),1)))}
N8{=IF(M8="","",INDEX($Q$3:Q8,LARGE(IF($I$3:I8=I8,IF($Q$3:Q8<>"",ROW($I$3:I8)-ROW($I$3)+1)),1)))}
Press CTRL+SHIFT+ENTER to enter array formulas.
:cool:
 
Last edited:
Upvote 0
You must log in or register to reply here.

Similar threads

E
  • Question Question
VBA code clean up
Replies
9
Views
482
mumps
mumps
D
  • Solved
VBA Function to Paste Table into Outlook Email Resulting in HTML language in Email
Replies
8
Views
537
smozgur
smozgur
E
  • Question Question
Adding a custom message.
Replies
1
Views
633
Micron
M
J
  • Question Question
to to calculated the weighted average if the inventory above zero
Replies
1
Views
321
dermie_72
D
C
  • Solved
Excel 2016 32bit VBA Pulling info from Website to Sheet
Replies
2
Views
1K
CrashOD
C

Forum statistics

Threads
1,223,227
Messages
6,170,848
Members
452,361
Latest member
d3ad3y3

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