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