Forum Discussion
Calculate profit and loss and average cost for my portfolio in stock market
Hi Everyone
i have created Sample test for my portfolio and is already attached .
my question is to help me to add formula for my excel sheet to calculate current average cost for shares. i have multiple action BUY/SELL for period time and i know how calculate profit and loss and cost average but i really don’t know how i add formula for auto calculation for all action.
i already fill all needed data in table .
i appreciate to help me to solve this issue
here is the test example excel file
https://1drv.ms/x/s!Akb3JP6i-nqPhgRaIH1roG3vWbvy?e=sOpVhk
2 Replies
Hi nalnofal
if I did not get you wrong, you could use the SUMPRODUCT function for this:
=(SUMPRODUCT(Table5545[BUY QTY];Table5545[Cost])+SUMPRODUCT(Table5545[SELL QTY];Table5545[Cost]))/SUM(Table5545[[BUY QTY]:[SELL QTY]])
It multiplies all buy-quantities with the cost, then adds the sell-quantities multiplied with the cost and in the end it divides that number by the total quantities buy/sell.
Hi Martin_Weiss
very interesting from you to use sumproduct formula for my buy/sell , its work fine in my first Example but unfortunately not work with other trading company that i have .
the problem happen when i make more trading Buy/sell for same company in shares market for multiple action
for example i put tow more company in my excel sheet : (CompanyB and CompanyC) with more data i fill, so that you can look into it to understanding all trading i make and how my bank calculations loss/profit and how they calculate my shares average cost/total Shares cost
i reshare my Test Example
