Excel entries and Section 104 Holding

stphnstevey

So far I have been logging share trades in excel as

type of account/number shares/price per share/trading costs/total amount purchased/date/current share price/share sale price/total amount sold

When selling, I move the sold shares from one worksheet to a specific sold shares worksheet so that they are separate and not included in the current calculated share portfolio total

I would like to add capital gain column, but I have become unstuck when selling part of a holding, because I believe you generally work out the average price paid for the holding, rather than the actual price paid and take that average away from the sale price

Do I simply need an extra column to work out the average price paid?

I presume a holding refers to all shares of that company held across all platform trading accounts? If so, different platforms have different costs, do I average the costs as well?

It's a bit confusing and I was hoping to construct an Excel spreadsheet that would automatically calculate at least some of this if I plug in the raw data

Thanks

bowlhead99

Assuming you have the same share owned across three different taxable trading accounts (and assuming you are correctly ignoring non-taxable accounts like ISA and SIPP, which don't come into the average for taxable capital gains purposes because they're invisible to the tax man):

You need to consider them as one big pool and have a column tracking the total cost of shares in that big pool and another tracking the total quantity of shares in that big pool.

Every time you buy any shares in any one of the taxable accounts you will be increasing the total quantity of shares in the big 'section 104' pool by the quantity bought, and increasing the total cost in that big pool by the cost of the purchase (including stamp duty and transaction fee).

Each incremental purchase in one of your accounts will add to the cost and the quantity in the 'big pool', and change the average price paid per share in the big pool. E.g. You had 3000 shares bought for £3000 including transaction fees, and 2000 shares bought for £2200, total cost at this point across both accounts is £5200 for 5000 shares. You won't actually need to know the 'average cost per share' at this point if you're not selling any yet, but you could calculate a running average if you want (it would be £1.04 per share, as it happens).

Then you buy more shares in one of your existing accounts (or a new account) spending another £1300 on 1000 more shares. So your total goes up to £6500 for 6000 shares.

Then, you are going to sell some shares. So before you sell them you look at your average cost, £6500 for 6000 shares, and note that each share that you're about to sell had cost you £1.0833 each.

When you sell shares, knowing they had cost you £1.0833 each, you are going to need to take the total cost of those shares that you no longer have, away from your total cost column, at the same time as taking the quantity of shares you sold away from your total quantity column, so that you are still tracking the remaining cost and remaining quantity of what you haven't sold.

So for example you sell 600 shares. You know that at £1.0833 each, 600 shares had a cost of £650. That £650 is the figure you'd take off to your 'capital gains' worksheet to compare with your sales proceeds for those 600 shares. So in your total cost pool, when you do the sale you are going to remove 600 shares and £650 of cost.

The total cost is now £3000+£2200+£1300, -£650 = £5850. The total quantity is now 3000+2000+1000, -600 = 5400. If you did the maths at that point you'd see that the cost per share of the shares you haven't sold yet is still £1.0833 per share (£5850/5400) because simply removing shares from the pool at their average cost doesn't change the average cost per share of what's left.

If you then buy some more shares in any of your accounts, it will increase the total cost of your unsold shares by whatever you pay (including transaction costs) and increase the total quantity by however many you get. This will change the overall cost per share of the shares you now hold, so the next time you do a sale, you'll be using the new average cost which you get from looking at the total pool cost and total pool quantity columns.

bhjm

although the website is in german, this might be worth to give it a try (don`t worry, you can change the language in the app to english)

this app might fulfil exactly what you want to do.

https://www.portfolio-performance.info/portfolio/

stphnstevey

bowlhead99 said:

Assuming you have the same share owned across three different taxable trading accounts (and assuming you are correctly ignoring non-taxable accounts like ISA and SIPP, which don't come into the average for taxable capital gains purposes because they're invisible to the tax man):

You need to consider them as one big pool and have a column tracking the total cost of shares in that big pool and another tracking the total quantity of shares in that big pool.

Every time you buy any shares in any one of the taxable accounts you will be increasing the total quantity of shares in the big 'section 104' pool by the quantity bought, and increasing the total cost in that big pool by the cost of the purchase (including stamp duty and transaction fee).

Each incremental purchase in one of your accounts will add to the cost and the quantity in the 'big pool', and change the average price paid per share in the big pool. E.g. You had 3000 shares bought for £3000 including transaction fees, and 2000 shares bought for £2200, total cost at this point across both accounts is £5200 for 5000 shares. You won't actually need to know the 'average cost per share' at this point if you're not selling any yet, but you could calculate a running average if you want (it would be £1.04 per share, as it happens).

Then you buy more shares in one of your existing accounts (or a new account) spending another £1300 on 1000 more shares. So your total goes up to £6500 for 6000 shares.

Then, you are going to sell some shares. So before you sell them you look at your average cost, £6500 for 6000 shares, and note that each share that you're about to sell had cost you £1.0833 each.

When you sell shares, knowing they had cost you £1.0833 each, you are going to need to take the total cost of those shares that you no longer have, away from your total cost column, at the same time as taking the quantity of shares you sold away from your total quantity column, so that you are still tracking the remaining cost and remaining quantity of what you haven't sold.

So for example you sell 600 shares. You know that at £1.0833 each, 600 shares had a cost of £650. That £650 is the figure you'd take off to your 'capital gains' worksheet to compare with your sales proceeds for those 600 shares. So in your total cost pool, when you do the sale you are going to remove 600 shares and £650 of cost.

The total cost is now £3000+£2200+£1300, -£650 = £5850. The total quantity is now 3000+2000+1000, -600 = 5400. If you did the maths at that point you'd see that the cost per share of the shares you haven't sold yet is still £1.0833 per share (£5850/5400) because simply removing shares from the pool at their average cost doesn't change the average cost per share of what's left.

If you then buy some more shares in any of your accounts, it will increase the total cost of your unsold shares by whatever you pay (including transaction costs) and increase the total quantity by however many you get. This will change the overall cost per share of the shares you now hold, so the next time you do a sale, you'll be using the new average cost which you get from looking at the total pool cost and total pool quantity columns.

Sorry to quote/resurrect a thread from a year ago
I am now doing my tax return and did not manage to create a suitable spreadsheet to accomplish this. I understand the principles and math to calculate the necessary figures. I am struggling with putting that into a spreadsheet. Ideally I would like as much automatic calculation as possible, but looking at this, it appears there will be at least some manual calculation needed

Issues are that I list in one sheet my current portfolio of shares with the raw data of price paid and quantity etc, another sheet for for copying and pasting the raw data from the current portfolio to use on a sold sheet, so those shares are not included in my portfolio.
I'm not sure where to list the 104 holding details?
If another column on the current portfolio, which of say the 10 Amazon share lines do I use? Then when I sell the shares and move them to the sold sheet, how do I ensure the existing formula still work and it automatically calculates the new pool share price and number? When I do move them to the sold sheet, which shares do 

Or do I create a separate sheet again where I keep a tally of the section 104 holding?
However, which line of the raw data (which trading account) do I copy across to the sold sheet?

Sorry, I'm just seemingly getting circular references abound and tying myself in knots!   

talexuser

I just have date, no of shares, price per share (for reference not calculation), purchase price of those shares and dealing costs. Add up the no of shares column and then the  purchase price column plus dealing costs column and division gives you the average price per share. Multiply this by the number sold, and subtract from the proceeds and sell cost for the capital gain. Now start a new line with the number of shares left, and the same average price, if new shares are bought enter the date, no ,price, purchase price for no of shares and dealing costs and start new addition columns.