View Single Post
  #3   Report Post  
Posted to microsoft.public.excel.newusers
Vivek[_2_] Vivek[_2_] is offline
external usenet poster
 
Posts: 14
Default Calculate profits on stock sales on FIFO basis

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?


The 'Action' column will have either 'Buy' or 'Sell'.
If it's Sell, I need the gain made by me on that particular 'Stock' sale based on FIFO.

The sheet could look like this:
Date Stock Action Qty Price Gain
01-01-09 IBM Buy 50 100
01-01-09 GE Buy 100 15
01-02-09 IBM Buy 25 105
01-03-09 IBM Buy 75 110
01-04-09 IBM Sell 100 120 1625 ( 1625 is 50*20 + 25*15 + 25*10 )
01-04-09 ORCL Buy 100 20
01-05-09 IBM Sell 25 130 500 ( 25*20 )


I think let's ignore 'Brokerage' for now as it doesn't seem to be
much of an issue as much as deriving the gain on each sale in
the first place.

I hope this clears it up. Thanks again.



"JoeU2004" wrote in message ...
"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