Calculation on Buy and Sell + Calculation of Avg. Price on Remaining Shares

[Excelanil]

Hi:

On April 14th, 2009 Sal Paradise posted a very interesting calculation to determine the Number of Remaining Shares in a Stock Portfolio which tracks "buys" and "Sell".

You can see his response to "Calculation on Buy and Sell". It is Post #1774. Doing a Forum Search, you can find it by entering, Calculation on Buy and Sell

<TABLE style="BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 2pt; PADDING-RIGHT: 2pt; FONT-FAMILY: Arial, Arial; FONT-SIZE: 11pt" border=1 cellSpacing=0 cellPadding=0><COLGROUP><COL style="WIDTH: 30px; FONT-WEIGHT: bold"><COL style="WIDTH: 76px"><COL style="WIDTH: 85px"><COL style="WIDTH: 133px"><COL style="WIDTH: 134px"><COL style="WIDTH: 73px"><COL style="WIDTH: 72px"><COL style="WIDTH: 81px"><COL style="WIDTH: 176px"><COL style="WIDTH: 60px"><COL style="WIDTH: 60px"></COLGROUP><TBODY><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt; FONT-WEIGHT: bold"><TD> </TD><TD>A</TD><TD>B</TD><TD>C</TD><TD>D</TD><TD>E</TD><TD>F</TD><TD>G</TD><TD>H</TD><TD>I</TD><TD>J</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">5</TD><TD style="TEXT-ALIGN: center">Trade No.</TD><TD style="TEXT-ALIGN: center">Stock code</TD><TD style="TEXT-ALIGN: center">Date time of trade</TD><TD style="TEXT-ALIGN: center">Number of shares</TD><TD style="TEXT-ALIGN: center">Buy price</TD><TD style="TEXT-ALIGN: center">Sell price</TD><TD style="TEXT-ALIGN: center">Trade type</TD><TD style="TEXT-ALIGN: center">No. of remaining shares</TD><TD style="TEXT-ALIGN: center">Cost</TD><TD style="TEXT-ALIGN: center">Sale</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">6</TD><TD style="TEXT-ALIGN: center">1</TD><TD style="TEXT-ALIGN: center">ABN</TD><TD> </TD><TD style="TEXT-ALIGN: center">140</TD><TD style="TEXT-ALIGN: center">200</TD><TD> </TD><TD style="TEXT-ALIGN: center">Buy</TD><TD style="TEXT-ALIGN: center">140</TD><TD style="TEXT-ALIGN: center">28000</TD><TD style="TEXT-ALIGN: center">0</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">7</TD><TD style="TEXT-ALIGN: center">3</TD><TD style="TEXT-ALIGN: center">ABN</TD><TD> </TD><TD style="TEXT-ALIGN: center">30</TD><TD> </TD><TD style="TEXT-ALIGN: center">201</TD><TD style="TEXT-ALIGN: center">Sell</TD><TD style="TEXT-ALIGN: center">110</TD><TD style="TEXT-ALIGN: center">6000</TD><TD style="TEXT-ALIGN: center">6030</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">8</TD><TD style="TEXT-ALIGN: center">6</TD><TD style="TEXT-ALIGN: center">ABN</TD><TD> </TD><TD style="TEXT-ALIGN: center">80</TD><TD> </TD><TD style="TEXT-ALIGN: center">299</TD><TD style="TEXT-ALIGN: center">Sell</TD><TD style="TEXT-ALIGN: center">30</TD><TD style="TEXT-ALIGN: center">16000</TD><TD style="TEXT-ALIGN: center">23920</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">9</TD><TD style="TEXT-ALIGN: center">11</TD><TD style="TEXT-ALIGN: center">ABN</TD><TD> </TD><TD style="TEXT-ALIGN: center">30</TD><TD> </TD><TD style="TEXT-ALIGN: center">199</TD><TD style="TEXT-ALIGN: center">Sell</TD><TD style="TEXT-ALIGN: center">0</TD><TD style="TEXT-ALIGN: center">6000</TD><TD style="TEXT-ALIGN: center">5970</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">10</TD><TD style="TEXT-ALIGN: center">13</TD><TD style="TEXT-ALIGN: center">ABN</TD><TD> </TD><TD style="TEXT-ALIGN: center">20</TD><TD style="TEXT-ALIGN: center">226</TD><TD> </TD><TD style="TEXT-ALIGN: center">Buy</TD><TD style="TEXT-ALIGN: center">20</TD><TD style="TEXT-ALIGN: center">4520</TD><TD style="TEXT-ALIGN: center">0</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">11</TD><TD style="TEXT-ALIGN: center">2</TD><TD style="TEXT-ALIGN: center">BAN.C</TD><TD> </TD><TD style="TEXT-ALIGN: center">100</TD><TD style="TEXT-ALIGN: center">1360.5</TD><TD> </TD><TD style="TEXT-ALIGN: center">Buy</TD><TD style="TEXT-ALIGN: center">100</TD><TD style="TEXT-ALIGN: center">136050</TD><TD style="TEXT-ALIGN: center">0</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">12</TD><TD style="TEXT-ALIGN: center">8</TD><TD style="TEXT-ALIGN: center">BAN.C</TD><TD> </TD><TD style="TEXT-ALIGN: center">30</TD><TD> </TD><TD style="TEXT-ALIGN: center">1207</TD><TD style="TEXT-ALIGN: center">Sell</TD><TD style="TEXT-ALIGN: center">70</TD><TD style="TEXT-ALIGN: center">40815</TD><TD style="TEXT-ALIGN: center">36210</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">13</TD><TD style="TEXT-ALIGN: center">9</TD><TD style="TEXT-ALIGN: center">BAN.C</TD><TD> </TD><TD style="TEXT-ALIGN: center">70</TD><TD> </TD><TD style="TEXT-ALIGN: center">1270</TD><TD style="TEXT-ALIGN: center">Sell</TD><TD style="TEXT-ALIGN: center">0</TD><TD style="TEXT-ALIGN: center">95235</TD><TD style="TEXT-ALIGN: center">88900</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">14</TD><TD style="TEXT-ALIGN: center">15</TD><TD style="TEXT-ALIGN: center">HAN</TD><TD> </TD><TD style="TEXT-ALIGN: center">40</TD><TD style="TEXT-ALIGN: center">33.5</TD><TD> </TD><TD style="TEXT-ALIGN: center">Buy</TD><TD style="TEXT-ALIGN: center">40</TD><TD style="TEXT-ALIGN: center">1340</TD><TD style="TEXT-ALIGN: center">0</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">15</TD><TD style="TEXT-ALIGN: center">18</TD><TD style="TEXT-ALIGN: center">HAN</TD><TD> </TD><TD style="TEXT-ALIGN: center">10</TD><TD> </TD><TD style="TEXT-ALIGN: center">34</TD><TD style="TEXT-ALIGN: center">Sell</TD><TD style="TEXT-ALIGN: center">30</TD><TD style="TEXT-ALIGN: center">335</TD><TD style="TEXT-ALIGN: center">340</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">16</TD><TD style="TEXT-ALIGN: center">20</TD><TD style="TEXT-ALIGN: center">HAN</TD><TD> </TD><TD style="TEXT-ALIGN: center">10</TD><TD style="TEXT-ALIGN: center">39</TD><TD> </TD><TD style="TEXT-ALIGN: center">Buy</TD><TD style="TEXT-ALIGN: center">40</TD><TD style="TEXT-ALIGN: center">390</TD><TD style="TEXT-ALIGN: center">0</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">17</TD><TD style="TEXT-ALIGN: center">4</TD><TD style="TEXT-ALIGN: center">RAS</TD><TD> </TD><TD style="TEXT-ALIGN: center">70</TD><TD style="TEXT-ALIGN: center">5202</TD><TD> </TD><TD style="TEXT-ALIGN: center">Buy</TD><TD style="TEXT-ALIGN: center">70</TD><TD style="TEXT-ALIGN: center">364140</TD><TD style="TEXT-ALIGN: center">0</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">18</TD><TD style="TEXT-ALIGN: center">7</TD><TD style="TEXT-ALIGN: center">RAS</TD><TD> </TD><TD style="TEXT-ALIGN: center">50</TD><TD> </TD><TD style="TEXT-ALIGN: center">4908</TD><TD style="TEXT-ALIGN: center">Sell</TD><TD style="TEXT-ALIGN: center">20</TD><TD style="TEXT-ALIGN: center">260100</TD><TD style="TEXT-ALIGN: center">245400</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">19</TD><TD style="TEXT-ALIGN: center">12</TD><TD style="TEXT-ALIGN: center">RAS</TD><TD> </TD><TD style="TEXT-ALIGN: center">20</TD><TD style="TEXT-ALIGN: center">4361</TD><TD> </TD><TD style="TEXT-ALIGN: center">Buy</TD><TD style="TEXT-ALIGN: center">40</TD><TD style="TEXT-ALIGN: center">87220</TD><TD style="TEXT-ALIGN: center">0</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">20</TD><TD style="TEXT-ALIGN: center">5</TD><TD style="TEXT-ALIGN: center">SNE</TD><TD> </TD><TD style="TEXT-ALIGN: center">100</TD><TD style="TEXT-ALIGN: center">650</TD><TD> </TD><TD style="TEXT-ALIGN: center">Buy</TD><TD style="TEXT-ALIGN: center">100</TD><TD style="TEXT-ALIGN: center">65000</TD><TD style="TEXT-ALIGN: center">0</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">21</TD><TD style="TEXT-ALIGN: center">10</TD><TD style="TEXT-ALIGN: center">SNE</TD><TD> </TD><TD style="TEXT-ALIGN: center">20</TD><TD> </TD><TD style="TEXT-ALIGN: center">500</TD><TD style="TEXT-ALIGN: center">Sell</TD><TD style="TEXT-ALIGN: center">80</TD><TD style="TEXT-ALIGN: center">13000</TD><TD style="TEXT-ALIGN: center">10000</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">22</TD><TD style="TEXT-ALIGN: center">14</TD><TD style="TEXT-ALIGN: center">SNE</TD><TD> </TD><TD style="TEXT-ALIGN: center">30</TD><TD> </TD><TD style="TEXT-ALIGN: center">490</TD><TD style="TEXT-ALIGN: center">Sell</TD><TD style="TEXT-ALIGN: center">50</TD><TD style="TEXT-ALIGN: center">19500</TD><TD style="TEXT-ALIGN: center">14700</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">23</TD><TD style="TEXT-ALIGN: center">16</TD><TD style="TEXT-ALIGN: center">SNE</TD><TD> </TD><TD style="TEXT-ALIGN: center">40</TD><TD> </TD><TD style="TEXT-ALIGN: center">500</TD><TD style="TEXT-ALIGN: center">Sell</TD><TD style="TEXT-ALIGN: center">10</TD><TD style="TEXT-ALIGN: center">26000</TD><TD style="TEXT-ALIGN: center">20000</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">24</TD><TD style="TEXT-ALIGN: center">17</TD><TD style="TEXT-ALIGN: center">SNE</TD><TD> </TD><TD style="TEXT-ALIGN: center">20</TD><TD style="TEXT-ALIGN: center">599</TD><TD> </TD><TD style="TEXT-ALIGN: center">Buy</TD><TD style="TEXT-ALIGN: center">30</TD><TD style="TEXT-ALIGN: center">11980</TD><TD style="TEXT-ALIGN: center">0</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">25</TD><TD style="TEXT-ALIGN: center">19</TD><TD style="TEXT-ALIGN: center">SNE</TD><TD> </TD><TD style="TEXT-ALIGN: center">20</TD><TD> </TD><TD style="TEXT-ALIGN: center">480</TD><TD style="TEXT-ALIGN: center">Sell</TD><TD style="TEXT-ALIGN: center">10</TD><TD style="TEXT-ALIGN: center">11980</TD><TD style="TEXT-ALIGN: center">9600</TD></TR></TBODY></TABLE>
I like the formulas he proposed and they work very well.

I need need to determine or figure out what the "Average Cost" of the No. of Remaining Shares"?

So, by his spreadsheet example, he has 20 shares of ABN remaining; however, he bought ABN shares at two different prices, $200 and $226.

What if you had paid several different prices for several different amounts (lots), what formula would you use to calculate the average cost price for the remaining shares?

Once again, I cannot past the spreadsheet here for you to take a look at it; but you can see both, the Spreadsheet and his proposed set of formulae by doing a quick Forum Search on "Calculation on Buy and Sell", or look at Post #1774

I'd be very happy and appreciative if anyone could provide me with a solution to determing the "Average Cost" of the No. of Remaining Shares"?

Many, many thanks in anticipation.

Cheers!