"Vivek" wrote:
Ideally, I would like a column after the above columns
indicating the gain or loss if Action is Sell. Is it
possible using Array/other formulas?

As you described it, the gain/loss can be calculated on a line-by-line
basis, and it does not rely on FIFO. That is, if qty sold per lot is
entered manually.

I wonder if you omitted part of the problem.

Would you like to enter the total shares sold into some cell, then have a
column ("qty sold") automagically select the number of shares included in
the sale from each lot based on FIFO?

And would you like to enter the total brokerage commissions and fees into
some cell, then automagically distribute those fees into the Brokerage
column per lot sold based on qty sold per lot as a percentage of total
shares sold?

I don't know if/how that can be done with Excel formulas alone; the FIFO
requirement is the trick. I would use VBA.

By the way, you have only one Qty column and only one Price column. You
seem to use them as qty and price sold. (I assume "Brokerage" is the
commissions and fees per lot.) In order to do the FIFO selection
automatically, you also need a column for Qty Held. And in order to
calculate gain/loss, you also need a column for Basis. There is a probably
a template for all of this.


----- original message -----

"Vivek" wrote in message
...
Hi,

Want to know whether I can use formulas to calculate profit/loss on sales of
stocks based on FIFO - First In First Out - or does it need VBA.

I have my stock 'Transactions' sheet set up as below:

Date Stock Action Qty Price Brokerage Trade Value

'Stock' would be short name for the stock involved.
'Action' would be either Buy or Sell.
'Trade Value' would be calculated as Qty*Price+Brokerage if Buy,
Qty*Price-Brokerage if Sell.

Ideally, I would like a column after the above columns indicating the gain
or loss if Action is Sell. Is it possible using Array/other formulas?

Thanks,
Vivek