I am  a small investor in Shares/ Stocks in Mumbai Share Market. I have used excel to create stock portfolio.  I have bought a few quantity of stocks of Company XYZ at various dates, in various prices and various quantities. For profit calculation for tax purpose, I am required to use “First In First Out”. Tax is 0 (zero), if i sell the stock after holding for more than 1 year.

Now my problem is that I want the maximum price of the unsold Stock/ Share. Please see following image.


Company Transaction Date Quantity Price Amount
XYZ Buy 05-01-2014 10 40.00 400.00
XYZ Buy 06-01-2015 10 50.00 500.00
XYZ Buy 07-02-2015 20 46.00 920.00
XYZ Buy 09-02-2015 10 47.00 470.00
XYZ Buy 08-02-2016 5 5.00 25.00
XYZ Sell 08-02-2017 -30 55.00 -1650.00
XYZ Buy 08-05-2017 2 45.00 90.00
XYZ Sell 10-09-2017 -5 60.00 -300.00

Today, If I want to sell the stock at profit, the current price of the stock should be more than the purchase price of the first unsold stock. Since I have purchased various quantities on various dates and at various prices, I want the maximum purchase price of the unsold stock. In the above example, the unsold stocks are 5 (purchased on 07-02-2015) and the rest 22 (purchased after 07-02-15). Is there any simple excel formula or trick to enable me to calculate the maximum price of the unsold Stock/ Shares.


By: Uday

Leave a Reply

Your email address will not be published. Required fields are marked *