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.
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.