Forum Discussion
Solved
Need Formula for P/L for sale of a stock
I have a Table of stocks that I buy & sell. I need a Formula that will subtract my 'Sell Price' from my 'Buy Price' showing me any P/L that occurred once it has been sold (I don't do multiple buys of...
Perhaps
=IF([@UNITS]>0, 0, IF( [@SYMB]="", "", SUMPRODUCT( ([@DATE] >= [DATE] )* ([@SYMB] = [SYMB])* -[UNITS]* [$/Share] ) ) )
Forgive me for taking so long to reply, but Formulae such as this take me a while to understand (and, I must admit that I'm still confused with it). At first glance, this appeared not to be what I was looking for. After thinking on this for a while a 'Running Total by Group' would seem to fit regardless of the number of buys on a stock before it is sold. Though I still do find it rather hard to understand and follow the Formula posted by ArviLaanemets. I tried to use it, but without a more simpler explanation of it, I don't believe that I'll be able to use it. I thank you for being so kind as to use your valuable time to reply to my post. Have a Blessed Life!
P.S.
This brings up the question of how this Formula would work if, after selling a stock, I buy it again months later. Since I use the stock's Symbol and not a unique identifier would this Formula also take into account an earlier 'Buy - Sale' group when it shouldn't? Again, thank you!
P.S.
This brings up the question of how this Formula would work if, after selling a stock, I buy it again months later. Since I use the stock's Symbol and not a unique identifier would this Formula also take into account an earlier 'Buy - Sale' group when it shouldn't? Again, thank you!
- I do not understand your request yet.
if you upload a work book file rather than .png screenshot and expected result,I guess more guys may be interested in this question.- I uploaded a copy. It's titled 'MM Acct.xlsx'. Hope this helps. Thanks to everyone!!
In addition, if you'd like to deduct possible fees
=IF([@UNITS]>0, 0, IF( [@SYMB]="", "", SUMPRODUCT( ([@DATE] >= [DATE] )* ([@SYMB] = [SYMB])* -[UNITS]* [$/Share] ) - SUMPRODUCT( ([@DATE] >= [DATE] )* ([@SYMB] = [SYMB])* [FEE(S)] ) ) )and cosmetic, it's better to apply "comma style" format to P/L column