Calculate profits on stock sales on FIFO basis

[Vivek]

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

 

[JoeU2004]

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

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

 

[Vivek]

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]

The sheet could look like this:
Date Stock Action Qty Price Gain
01-01-09 IBM Buy 50 100
01-01-09 [....]
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 [....]
01-05-09 IBM Sell 25 130 500 ( 25*20 )

I see now. You maintain records differently than I do. I wonder how you
would handle splits and stock dividends that change basis. Well, maybe
that's your next question .

Anyway, returning to your original question....


For your method of recording, I can only imagine doing it using VBA. It may
or may not be challenging to write. That depends, in part, on whether you
would want a macro or a UDF (your original question suggests the latter),
and whether you would want to handle wash sales automagically (gulp!).

Sorry, but I cannot help you further. Maybe someone else has the time.


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

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.

 

[Vivek]

The sheet could look like this:
Date Stock Action Qty Price Gain
01-01-09 IBM Buy 50 100
01-01-09 [....]
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 [....]
01-05-09 IBM Sell 25 130 500 ( 25*20 )

I see now. You maintain records differently than I do. I wonder how you
would handle splits and stock dividends that change basis. Well, maybe
that's your next question .

I don't mind adopting a different format as long as it works.
My current sheet is a mere export from the trade book
of my trading account, not something I've designed!

If you have a template for calculating gains on stock sales
let me know because I couldn't find anything via Google.
If possible you may mail it by deleting DELTETHISNOW and NOTSO
from the address I give in these newsgroups.

Thanks again.

 

[JoeU2004]

If you have a template for calculating gains
on stock sales let me know

Sorry, nothing worth sharing.


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

The sheet could look like this:
Date Stock Action Qty Price Gain
01-01-09 IBM Buy 50 100
01-01-09 [....]
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 [....]
01-05-09 IBM Sell 25 130 500 ( 25*20 )

I see now. You maintain records differently than I do. I wonder how you
would handle splits and stock dividends that change basis. Well, maybe
that's your next question .

I don't mind adopting a different format as long as it works.
My current sheet is a mere export from the trade book
of my trading account, not something I've designed!

If you have a template for calculating gains on stock sales
let me know because I couldn't find anything via Google.
If possible you may mail it by deleting DELTETHISNOW and NOTSO
from the address I give in these newsgroups.

Thanks again.