Find maximum price of unsold stock in stock portfolio?

[UDAEY]

I am a small investor in Shares/ Stocks in Indian Share Market. I have used excel to create stock portfolio. I have bought a few quantity of stocks of Company XYZ at various dates, at various prices and in 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.

[TABLE="width: 412"]
<tbody>[TR]
[TD]Company[/TD]
[TD]Transaction[/TD]
[TD]Date[/TD]
[TD]Quantity[/TD]
[TD]Price[/TD]
[TD]Amount[/TD]
[/TR]
[TR]
[TD]XYZ[/TD]
[TD]Buy[/TD]
[TD="align: right"]05-01-2014[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]40.00[/TD]
[TD="align: right"]400.00[/TD]
[/TR]
[TR]
[TD]XYZ[/TD]
[TD]Buy[/TD]
[TD="align: right"]06-01-2015[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]50.00[/TD]
[TD="align: right"]500.00[/TD]
[/TR]
[TR]
[TD]XYZ[/TD]
[TD]Buy[/TD]
[TD="align: right"]07-02-2015[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]46.00[/TD]
[TD="align: right"]920.00[/TD]
[/TR]
[TR]
[TD]XYZ[/TD]
[TD]Buy[/TD]
[TD="align: right"]09-02-2015[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]47.00[/TD]
[TD="align: right"]470.00[/TD]
[/TR]
[TR]
[TD]XYZ[/TD]
[TD]Buy[/TD]
[TD="align: right"]08-02-2016[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]5.00[/TD]
[TD="align: right"]25.00[/TD]
[/TR]
[TR]
[TD]XYZ[/TD]
[TD]Sell[/TD]
[TD="align: right"]08-02-2017[/TD]
[TD="align: right"]-30[/TD]
[TD="align: right"]55.00[/TD]
[TD="align: right"]-1650.00[/TD]
[/TR]
[TR]
[TD]XYZ[/TD]
[TD]Buy[/TD]
[TD="align: right"]08-05-2017[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]45.00[/TD]
[TD="align: right"]90.00[/TD]
[/TR]
[TR]
[TD]XYZ[/TD]
[TD]Sell[/TD]
[TD="align: right"]10-09-2017[/TD]
[TD="align: right"]-5[/TD]
[TD="align: right"]60.00[/TD]
[TD="align: right"]-300.00[/TD]
[/TR]
</tbody>[/TABLE]

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.

 

[Gerald Higgins]

I can only see 17 purchased after 07-02-15, not 22.

Anyway, one way might be to record sales against the original purchase lines somehow.
You can then track how many of each purchase are still owned, and show their price IF they are still owned.

 

[UDAEY]

I can only see 17 purchased after 07-02-15, not 22.

Anyway, one way might be to record sales against the original purchase lines somehow.
You can then track how many of each purchase are still owned, and show their price IF they are still owned.

You are right. My mistake. It should be 17 and not 22.