Forum

Notifications
Clear all

Calculating Gain / Loss

5 Posts
2 Users
0 Reactions
74 Views
(@kiranananth)
Posts: 3
Active Member
Topic starter
 

Hello,

Thanks for sharing the Portfolio Dashboard tool - an excellent resource for amateur users like me.

I am writing to flag a minor issue I am facing while using this tool.  My current transaction of a certain stock is as under:

15-Oct-19 DIVI'S LABORATORIES LIMITED (XNSE:DIVISLAB) DIVISLAB ASK Buy 54  ₹         1,684.10 INR  ₹              90,941.38
24-Oct-19 DIVI'S LABORATORIES LIMITED (XNSE:DIVISLAB) DIVISLAB ASK Buy 17  ₹         1,764.79 INR  ₹              30,001.43
17-Jun-20 DIVI'S LABORATORIES LIMITED (XNSE:DIVISLAB) DIVISLAB ASK Sell -5  ₹         2,362.74 INR  ₹            -11,813.70
18-Jun-20 DIVI'S LABORATORIES LIMITED (XNSE:DIVISLAB) DIVISLAB ASK Sell -4  ₹         2,312.59 INR  ₹              -9,250.36
30-Sep-20 DIVI'S LABORATORIES LIMITED (XNSE:DIVISLAB) DIVISLAB ASK Sell -8  ₹         3,099.08 INR  ₹            -24,792.61
15-Jun-21 DIVI'S LABORATORIES LIMITED (XNSE:DIVISLAB) DIVISLAB ASK Sell -9  ₹         4,324.56 INR  ₹            -38,921.00

Current Stock value : 4320

Total transaction value (Net) : 36165

Total shares held (Net) : 45

Gain displayed in Portfolio : 436%

Actual gain : 155%

Reason being that the average cost of share is calculated as (total transaction value (net)/total number of shares (net).

Weighted purchase price is not taken into account.   This results in calculated stock value being lower than actual purchase cost.

I am unable to find a thread (if this was already flagged) regarding this.  Seek your support in arriving at right formulae for ascertaining gain / loss due to this transaction.

Thanks.

 
Posted : 17/06/2021 5:35 am
(@mynda)
Posts: 4761
Member Admin
 

Hi Kiran,

Welcome to our forum!

What I recommend you do in the situation where you only sell part of your portfolio is to split the original purchase transactions by the sold units and the retained units. Then add a column to the ledger where you tag the sold units as 'sold'. Then in the formulas you can exclude 'sold' units from the calculations.

I hope that points you in the right direction.

Mynda

 
Posted : 17/06/2021 6:22 am
(@kiranananth)
Posts: 3
Active Member
Topic starter
 

Thanks Mynda.  

I have several stocks in my portfolio like this where I have sold a part of units bought. 

Is there a way this can be factored in the formulae itself and reflects correctly in Portfolio?

Thanks

 
Posted : 17/06/2021 7:17 am
(@mynda)
Posts: 4761
Member Admin
 

Yes, you can factor it into the formula, but first you must add the column and split the units as I explained above. Then you can use SUMIFS to only include rows that <> "Sold".

Have a go yourself. If you get stuck, share your file showing your attempt so we can help further.

 
Posted : 17/06/2021 7:33 pm
(@kiranananth)
Posts: 3
Active Member
Topic starter
 

Thanks for this suggestion Mynda.  Portfolio correctly balanced now.

 
Posted : 18/06/2021 7:54 am
Share: