I am trying to work out how to create the cost base for my various share parcels. I realise Sharesight does this very well, but I would like to do this in my excel sheet.
When you buy, say BHP shares, the cost base is calculated by totalling all the buys, and the cost base per share is dividing the number of shares into the cost base.
However, when you throw a few SELL transactions into the arena, the calculations get a bit(lot) more messy. I have set the method as FIFO.
As shown in the screenshot above as an example, the total on 31 Aug 2004 was $2000 and on 27 Sept 2016 was 225. Hence the total cost base as of 9 Feb 2023 is (2000+225) =$2225
When you sell 15 shares on 10 Feb 2023 for $15 each, the total value is $225.
Based on FIFO sale allocation method (by default), these 15 shares gets allocated to the parcel of share that was bought on 31 Aug 2004 at $20 each. Hence, the cost base after the sale of 15 shares will be reduced by 15 * $20 (even though shares were sold at $15 each) = $300.
Thus, your final cost base will be 2225-300= $1925 as highlighted on screenshot below (under holding information)
Please reach out to us via support@sharesight.co.nz if you require further help on this.
