Logic help for variable weighted average

[Chris Macro]

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

 

[Chris Macro]

Thank you so much for putting that together for me. That cleared things up for me!

 

[MrVillareal]

Thank you so much for putting that together for me. That cleared things up for me!

Your welcome.

 

[Chris Macro]

ok, I've been testing my table and found a situation that I believe is not being calculated correctly. The last weighted price should be $71.84 instead of $55.05, correct?

Excel 2007
	T	U	V	W	X	Y	Z
1	Age	Buy/Sell	Stocks	Quantity	Price	Total	Wght Price
2	22	Bought	Industrial	50	$60.00	$ 3,000.00	$ 60.00
3	24	Bought	Industrial	20	$43.20	$ 864.00	$ 55.20
4	24	Bought	Banking	30	$52.25	$ 1,567.50	$ 52.25
5	25	Sold	Banking	15	$57.48	$ 862.20	$ 52.25
6	26	Sold	Banking	15	$71.84	$ 1,077.60	$ 52.25
7	26	Bought	Banking	5	$71.84	$ 359.20	$ 55.05
Player 1

Formula for weighted average is:

=SUMPRODUCT(--($U$2:U2="Bought"),--($V$2:V2=V2),$W$2:W2,$X$2:X2)/SUMPRODUCT(--($U$2:U2="Bought"),--($V$2:V2=V2),$W$2:W2)

Excel 2007
	Z
7	$ 55.05
Player 1

Cell Formulas
Range	Formula
Z7	=SUMPRODUCT(--($U$2:U7="Bought"),--($V$2:V7=V7),$W$2:W7,$X$2:X7)/SUMPRODUCT(--($U$2:U7="Bought"),--($V$2:V7=V7),$W$2:W7)

 

[MrVillareal]

Try to use the table with the formulas I suggested to come up with correct pricing and profit. I did not used the previous formula I suggested because its not giving the correct moving average, that is why I created a table that yields correct value.

 

[MrVillareal]

=SUMPRODUCT(--($U$2:U2="Bought"),--($V$2:V2=V2),$W$2:W2,$X$2:X2)/SUMPRODUCT(--($U$2:U2="Bought"),--($V$2:V2=V2),$W$2:W2)

This formula is giving the simple average for all bought stock thought it was already sold. Its not giving the moving weighted average. That is why I provided you with a new formulas in a different presentation.

 

[Chris Macro]

in your example, how did you only buy 6 financial stocks and sell 20?

 

[Chris Macro]

Also, I am not getting quantities for Financial stocks. Using your solution do I need to have a specific table for each stock type? See below:

Excel 2007
	I	J	K	L	M	N	O	P	Q	R	S	T	U
1	Stock	Buy			Sold			Balance			Sold Price
2		Quantity	Price/Share	Total Cost	Quantity	Price/Share	Total Cost	Quantity	Price/Share	Total Cost	Price/Share	Total Cost	Gain/Loss
3	Industrial	10	$ 10.00	$ 100.00				10	$ 10.00	$ 100.00
4	Industrial	5	$ 6.00	$ 30.00				15	$ 8.67	$ 130.00
5	Financial	6	$ 20.00	$ 120.00				6	$ 20.00	$ 120.00
6	Industrial				11	$ 8.67	$ 95.33				$ 12.00	$ 132.00	$ 36.67
7	Industrial	20	$ 50.00	$1,000.00				24	$ 43.11	$1,034.67
8	Industrial				24	$ 43.11	$1,034.67				$ 60.00	$1,440.00	$405.33
9	Industrial	5	$ 5.00	$ 25.00				5	$ 5.00	$ 25.00
10	Financial				2	$ 20.00	$ 40.00				$ 20.00	$ 40.00	$ -
Sheet1

 

[MrVillareal]

The table works and provide only stock balance after you purchase a stock and not after sale because the formulas in the stock balance is dependent on stock purchase and stock sale. If we put the stock balance after stock sales it will result to circular reference. Buy less sell more is the limitation of my table.