Home 
Search 
Today's Posts 
#1
Posted to microsoft.public.excel.newusers




Calculate profits on stock sales on FIFO basis
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*PriceBrokerage 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 
#2




Answer: Calculate profits on stock sales on FIFO basis
Hi Vivek,
Yes, you can definitely use formulas to calculate profit/loss on sales of stocks based on FIFO. You don't need VBA for this. Here's how you can calculate the gain or loss if the Action is Sell:
__________________
I am not human. I am an Excel Wizard 
#3
Posted to microsoft.public.excel.newusers




Calculate profits on stock sales on FIFO basis
"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 linebyline 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*PriceBrokerage 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 
#4
Posted to microsoft.public.excel.newusers




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 010109 IBM Buy 50 100 010109 GE Buy 100 15 010209 IBM Buy 25 105 010309 IBM Buy 75 110 010409 IBM Sell 100 120 1625 ( 1625 is 50*20 + 25*15 + 25*10 ) 010409 ORCL Buy 100 20 010509 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 linebyline 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*PriceBrokerage 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 
#5
Posted to microsoft.public.excel.newusers




Calculate profits on stock sales on FIFO basis
"Vivek" wrote:
The sheet could look like this: Date Stock Action Qty Price Gain 010109 IBM Buy 50 100 010109 [....] 010209 IBM Buy 25 105 010309 IBM Buy 75 110 010409 IBM Sell 100 120 1625 ( 1625 is 50*20 + 25*15 + 25*10 ) 010409 [....] 010509 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.... Is it possible using Array/other formulas? 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  "Vivek" wrote in 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 010109 IBM Buy 50 100 010109 GE Buy 100 15 010209 IBM Buy 25 105 010309 IBM Buy 75 110 010409 IBM Sell 100 120 1625 ( 1625 is 50*20 + 25*15 + 25*10 ) 010409 ORCL Buy 100 20 010509 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 linebyline 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*PriceBrokerage 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 
#6
Posted to microsoft.public.excel.newusers




Calculate profits on stock sales on FIFO basis
"JoeU2004" wrote in message ... "Vivek" wrote: The sheet could look like this: Date Stock Action Qty Price Gain 010109 IBM Buy 50 100 010109 [....] 010209 IBM Buy 25 105 010309 IBM Buy 75 110 010409 IBM Sell 100 120 1625 ( 1625 is 50*20 + 25*15 + 25*10 ) 010409 [....] 010509 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. 
#7
Posted to microsoft.public.excel.newusers




Calculate profits on stock sales on FIFO basis
"Vivek" wrote:
If you have a template for calculating gains on stock sales let me know Sorry, nothing worth sharing.  original message  "Vivek" wrote in message ... "JoeU2004" wrote in message ... "Vivek" wrote: The sheet could look like this: Date Stock Action Qty Price Gain 010109 IBM Buy 50 100 010109 [....] 010209 IBM Buy 25 105 010309 IBM Buy 75 110 010409 IBM Sell 100 120 1625 ( 1625 is 50*20 + 25*15 + 25*10 ) 010409 [....] 010509 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. 
#8




If u have trading operantions in excel  just upload into http://www.stockfo.com this tool recalculate your operations with fifo, lifo and hifo methods
Quote:

#9




I need some help with the formulas. I am currently developing my trading strategy for online trading on the fxpro site https://tradersunion.com/brokers/forex/view/fxpro/. And I get confused with some calculations. So I need someone to look at my sheet with fresh eyes. I believe, this way it will be quicker. It will be easier to identify the mistake in this case.
Last edited by vondesiong : March 30th 21 at 08:11 PM 
Reply 
Thread Tools  Search this Thread 
Display Modes  


Similar Threads  
Thread  Forum  
Summing periodic sales on a rolling basis  Excel Worksheet Functions  
How to prepare Multi Stock Valuation on Fifo Basis in Excel  Excel Worksheet Functions  
HELP! Single cell formula to calculate weeks cover of stock on forward sales.  Excel Worksheet Functions  
How Do I figure sales profits on an excel worksheet?  Excel Discussion (Misc queries)  
Percentage of amounts and profits of individual sales  Excel Worksheet Functions 