Buying and Selling in Tranches Average Computation

This time out of boredom I have come up with another excel calculator that I'll be sharing with a different approach. This is actually different from how your brokers are computing your average every time you buy some shares and you disposed some and added again number of shares and so on.

You may read here for sample broker's computation how it is done, take note that the last average computed is carried over to the next if in case you sell some shares and you decided to add some shares later on which will come up with another average, it might be confusing at first but once you get the idea you will easily understand how it is computed.

 

What I am going to share is another approach check if it can work out for your trading.

So the concept is quite simple you just take note of the total amount you paid and deduct all amount received from all shares you sold and see if you were able to make some profits.

For COL Financial, Philstocks and other brokers with the same computation of fees is applicable to this excel file.

Disclaimer: Sample charts provided are for educational purposes only.

Let's see sample computation below: 

STOCK: NOW

February 14, 2018 purchased 1,600 shares of NOW @  ₱13.48 with an average of ₱13.5198

1st_purchase_NOW

February 19, 2018 purchased 800 shares of NOW @  ₱12.40 with an average now of ₱13.1587

2nd_purchase_NOW

February 28, 2018 sold 800 shares of NOW @  ₱13.26 with still an average now of ₱13.1587 if you are following your broker's method of computing averages, whereas you will notice from below under "selling shares in between purchase and in tranches" computed average is at ₱13.1674 with a negative gain of ₱21,067.84 that I need to surpass when I sell my remaining 1,600 shares.

3rd_sold_portions_of_NOW

March 01, 2018 purchased 800 shares of NOW @  ₱12.72 with an average now of ₱13.0308 (not from broker's computation which is at ₱13.0250) with a negative gain of ₱31,273.86 that I need to surpass when I sell my remaining 2,400 shares.

4th_purchase_NOW

March 01, 2018 same day sold 800 shares of NOW @  ₱13.28 with an average now of ₱12.9656 with a negative gain of ₱20,744.94 that I need to surpass when I sell my remaining 1,600 shares.

5th_sold_portions_of_NOW

March 02, 2018 sold all 1,600 shares of NOW @  ₱14.42 with ₱2,120.57 profits, so the total amount received after selling remaining shares is higher from my total payment of all shares.

6th_sold_all_position_of_NOW

Trying to input from target price is also one way to check if you will make a profit or not which is having an average of  ₱12.9656 and same results would come up with ₱2,120.57 profits.

placing_target_price_to_surpass_your_current_average

You may wonder how I know if I sold my previous shares of 13.26 and 13.28 respectively would give me gains is by filling up under "selling per shares bought" column and not from "selling by tranches"

counter_checking_if_sold_with_profits

Here's a breakdown of the computation if done manually(fees included).

 1,600 shares of NOW @  ₱13.48=₱21,631.63
    800 shares of NOW @  ₱12.40=₱  9,949.27
--------------------------------------------------------
2,400 shares                                    ₱31,580.90 total amount paid

Sold
   800 shares of NOW @  ₱13.26=₱10,513.06 amount received

Get the difference
₱31,580.90 - ₱10,513.06=₱21,067.84 remaining amount paid with 1,600 shares remaining

Bought another set of shares
   800 shares of NOW @  ₱12.72=₱10,206.02 new amount paid

Add remaining amount paid and new amount paid
₱21,067.84 + ₱10,206.02=₱31,273.86 total amount paid with new 2,400 shares

Sold
   800 shares of NOW @  ₱13.28=₱10,528.92 amount received

Get the difference
₱31,273.86 - ₱10,528.92=₱20,744.94 remaining amount paid with 1,600 shares remaining

Sold all remaining shares
1,600 shares of NOW @  ₱14.42=₱22,865.51 amount received

Sum up all amount received and deduct all total amount paid

₱10,513.06 + ₱10,528.92 + ₱22,865.51=₱43,907.49
less
₱31,580.90 + ₱10,206.02                      =₱41,786.92
-----------------------------------------------------------------
                                                                  ₱  2,120.57  profit

or simply get the difference of the last amount received with the remaining amount paid left recently
₱22,865.51 - ₱20,744.94=₱2,120.57

Not bad with less than a month with a total amount of  ₱41,786.92 gained ₱2,120.57 compared to a time deposit of ₱50K that only yield ₱200+ interest in a year less tax that I experienced before from my local bank.

Looking at Charts

➤failure to sell the following day because of best friend COL broker had some fun hanging around. 

➤after additional shares again failed to sell when around 17sh due to greed, rofl.

chart_from_investagrams

Let's make another scenario same stock but modifying the number of shares sold.

Say we purchased again the same number of shares of 1,600 and 800 with price of ₱13.48 and ₱12.40 respectively.

purchased_shares_NOW

Then we decided to sell half of our position of 1,200 shares @ ₱13.26 leaving us an average of 13.1761 or negative gain @ ₱15,811.31

sold_half_shares_NOW

Added again later on with 800 shares @ ₱12.72 with average of 13.0087

purchased_new_shares_NOW

Now selling all your positions of 2,000 shares @ ₱14.42 would give you ₱2,564.56

sold_all_positions_NOW

Let's make another sample case study on POPI (fees included)

10,000 shares of POPI @  ₱3.64=₱ 36,507.38  2/27/2018
  3,000 shares of POPI @  ₱3.57=₱ 10,741.60  2/27/2018
  4,000 shares of POPI @  ₱3.57=₱ 13,519.76  3/02/2018
20,000 shares of POPI @  ₱3.45=₱ 69,203.55  3/05/2018
20,000 shares of POPI @  ₱2.92=₱ 58,572.28  3/20/2018
57,000 shares                                ₱188,544.57 total amount paid

Just to give you again an idea how your broker computes for your average is simply dividing the total amount paid with the total number of shares:

 ₱188,544.57/57,000 shares  = ₱3.3078 per share

Now say we disposed 20,000 shares at a price of ₱3.17 = ₱62,832.57 (amount you will receive) but the current average reflected from your online account would still be at ₱3.3078 per share.

To shun away from your typical broker average computation simply get the difference from the total amount you paid with the amount you received and divide with the remaining shares to get your current average.

total paid less amount received
₱188,544.57 - ₱62,832.57 = ₱125,712.00 (amount left invested)

amount left divided by remaining shares to get average
₱125,712.00/37,000 shares remaining = ₱3.3976 per share

April 16, 2018 the price of POPI went up as high as ₱3.56 per share then dropped around ₱3.50 per share and your broker will show you a profit of ₱5,952.38 from a traded market price of  ₱3.50 with an average of ₱3.3078 per share.

POPI_portfolio_from_COL

In reality when you sell those remaining 37,000 shares at ₱3.50 you will only pocket around ₱2,628.97 not what your broker is showing you in your port.

To justify this remember you paid a total amount of ₱188,544.57 then you deduct the amount you receive after selling 20k shares amounting to ₱62,832.57 which would leave you as mentioned above  an amount of ₱125,712.00

If you sell remaining 37k shares at ₱3.50 you will receive an amount of ₱128,340.97 and try to deduct the amount left of ₱125,712.00 and you get a net amount of profit of ₱2,628.97 or adding up all the negative and positive gains will still give you the same result as shown below monitoring sheet.

₱-1,820.63 + ₱-335.58 + ₱354.94 + ₱169.95 + ₱4,260.29 = ₱2,628.97 (same as above)

POPI_monitoring_sheet

How your broker try to compute for your average you might wonder how they come up with the ₱5,952.38 profit shown in your port so let's make a quick computation how they derived that amount.

37k shares x ₱3.3078 per share = ₱122,388.60 (using your broker's computed average at ₱3.3078)
37k shares x ₱3.50     per share = ₱128,340.97 (amount received)

₱128,340.97 less ₱122,388.60 = ₱5,952.37 profit shown from port (decimal discrepancy on rounding off numbers)

Now go ahead and play around with your average and come up with a strategy on how you can make a profit minimizing some cut loss in selling all your shares one time but rather trying to dispose in tranches and try to re-balance your port then decide if you are happy with the gains.

Again your main concern here is not the total amount you will be able to pocket but trying to lessen some risks and would still come up with a gain in the end which will help you out to perfect your system for your winning trades.

It doesn't matter if you lose twice but win three times that would still cover up your loses as sample above would still a good way to be rewarded from the market.

Do this with consistency and your money will grow in time, just make sure the amount received  that returned in the end would be greater from your total invested amount, simple math and you can do it yourself as most are focused on the gains and not on the strategy that's why they lose more money.

Just an update I made some changes instead of gain/loss I renamed it to total cost instead, so you may know how much you paid for the remaining shares left and make sure when you sell it exceed this total cost amount reflected.

Basically when you input your target price it will tell you if you gain or at a loss anyway considering the current average price that you have.

Sample screenshot:

Average_price_calculator

TAYOR!

If you find this helpful, say thanks by sharing this site to others!