Donate to Remove ads

Got a credit card? use our Credit Card & Finance Calculators

Thanks to johnstevens77,Bhoddhisatva,scotia,Anonymous,Cornytiv34, for Donating to support the site

Excel entries and Section 104 Holding

Discussions regarding financial software
Cookie
2 Lemon pips
Posts: 120
Joined: January 27th, 2018, 10:19 pm
Has thanked: 63 times
Been thanked: 6 times

Excel entries and Section 104 Holding

#270971

Postby Cookie » December 13th, 2019, 6:58 pm

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

Alaric
Lemon Half
Posts: 6032
Joined: November 5th, 2016, 9:05 am
Has thanked: 20 times
Been thanked: 1398 times

Re: Excel entries and Section 104 Holding

#270985

Postby Alaric » December 13th, 2019, 8:42 pm

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


You need for each share you hold, the number of shares and the total paid for them including stamp duty and commission ("book cost"). When you sell part of a holding, you work out the total paid for what you have left by apportionment.

What you disposed is the difference between the book cost you started with and the book cost you have after the apportionment. The gain is than the proceeds of sale , allowing for commission and the disposed book cost.

PinkDalek
Lemon Half
Posts: 6139
Joined: November 4th, 2016, 1:12 pm
Has thanked: 1589 times
Been thanked: 1801 times

Re: Excel entries and Section 104 Holding

#271713

Postby PinkDalek » December 17th, 2019, 12:54 pm

Cookie wrote:[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? ...


Yes and no!

Yes, all holdings for a particular share in taxable accounts are aggregated to form a section 104 pool which should reflect your total shareholdings and total cost of that holding.

No, in that you shouldn't really average the costs per share, at least for CGT purposes, as it is the total cost in £ terms that is relevant. You may, of course, wish to do so for your own record keeping purposes.

Can't help on Excel but a brief summary of section 104 holdings is here:

https://www.gov.uk/government/publications/shares-and-capital-gains-tax-hs284-self-assessment-helpsheet/hs284-shares-and-capital-gains-tax-2019

... The Section 104 holding consists of a single pool of expenditure usually representing the actual cost of shares. ...

pochisoldi
Lemon Slice
Posts: 939
Joined: November 4th, 2016, 11:33 am
Has thanked: 31 times
Been thanked: 459 times

Re: Excel entries and Section 104 Holding

#271890

Postby pochisoldi » December 18th, 2019, 1:26 am

My CGT Excel spreadsheet has one worksheet for each share.
Each worksheet has columns as follows:
A=transaction date
B=Transaction type (e.g. Buy/Reinvest dividend/Sell)
C=not used
D=# shares (Number of shares bought/sold in this transaction) (For a sale, enter a negative number)
E=Price (Price per share in GBP)
F=Commission+Stamp Duty (for the transaction) (aka "cost of sale" or "cost of acquisition") (always positive)
G=Transaction value (calculated =ROUND(D2*E2,2)+F2) (i.e. =(number of shares * share price)+cost of transaction)
H=not used
I=not used
J=ongoing value of pool (For row 2 calculated as =H2, for row3 calculated as =J2+H3)

For each transaction I add another row.

The spreadsheet can't handle same day or 30 day matching - not an issue for me but then again my spreadsheet represents my S104 pool, and an matched same day and 30day trades are out of scope.

Pochisoldi

pochisoldi
Lemon Slice
Posts: 939
Joined: November 4th, 2016, 11:33 am
Has thanked: 31 times
Been thanked: 459 times

Re: Excel entries and Section 104 Holding

#271933

Postby pochisoldi » December 18th, 2019, 10:05 am

My original post below has some errors...

pochisoldi wrote:My CGT Excel spreadsheet has one worksheet for each share.
Each worksheet has columns as follows:
A=transaction date
B=Transaction type (e.g. Buy/Reinvest dividend/Sell)
C=not used
D=# shares (Number of shares bought/sold in this transaction) (For a sale, enter a negative number)
E=Price (Price per share in GBP)
F=Commission+Stamp Duty (for the transaction) (aka "cost of sale" or "cost of acquisition") (always positive)
G=Transaction value (calculated =ROUND(D2*E2,2)+F2) (i.e. =(number of shares * share price)+cost of transaction)
H=not used
I=not used
J=ongoing value of pool (For row 2 calculated as =H2, for row3 calculated as =J2+H3)

For each transaction I add another row.

The spreadsheet can't handle same day or 30 day matching - not an issue for me but then again my spreadsheet represents my S104 pool, and an matched same day and 30day trades are out of scope.

Pochisoldi


I missed out the following - mainly because I haven't done any partial sales!!

When making a disposal, use the spreadsheet to work out the acquisition cost:
1) Make an entry in the spreadsheet, with the number of shares sold (as a negative number). For the price (column D) use the average acquisition price.
This is calculated from the last entry in column J divided by the last entry in column D.
Easiest way: Assuming the disposal is on row 5 of the spreadsheet, the formula would be =ROUND(J4/D4,4)
This gives an average price to 4 decimal places.
Do not make any entry for commission/stamp duty

2)For the CGT calculation: The acquisition cost is the amount shown in column G
This cost includes the averaged costs of acquisition.

Note that entering a sale at the disposal price and including the "cost of sale" as I originally stated will completely screw up the pool value.

PochiSoldi

GPhelan
Lemon Pip
Posts: 61
Joined: November 11th, 2016, 7:25 pm
Has thanked: 1 time
Been thanked: 27 times

Re: Excel entries and Section 104 Holding

#272138

Postby GPhelan » December 18th, 2019, 8:30 pm

I built my own Section 104 Excel worksheet using "Example 3" which is referenced with a link towards the end of the HS284 helpsheet mentioned earlier, or directly (2019 version) here:
https://assets.publishing.service.gov.u ... ample3.pdf

I have shares and funds where the dividends are reinvested, thus increasing the pool size and some of the funds pay equalisation, which has to be logged and summed because it forms part of the final CGT calculation. I found it well worth putting in the effort to build an annotated and documented worksheet which I could understand in future years. I have sold partial holdings in different tax years (to keep under the CGT threshold) and the spreadsheet both provides a tool for estimating how many shares/units I can sell as well as recording what I have done. The final version following a sale is also what I attach (as a PDF image) to my tax return if my sales exceed the reporting limit.

Further reasons for carefully recording all this stuff are the occasions that shares are split, or where you get capital reorganisations or scrip offers, all of which have to be built into the Section 104 pool. For funds (OEICS) other hazard/issues to be handled are mergers with another fund or fund class changes, such as the now mostly historical switch to lower cost fund 'Clean classes'.

None of this is necessary for shares/funds held in ISAs.

BobbyD
Lemon Half
Posts: 7814
Joined: January 22nd, 2017, 2:29 pm
Has thanked: 665 times
Been thanked: 1289 times

Re: Excel entries and Section 104 Holding

#272704

Postby BobbyD » December 21st, 2019, 4:51 am

pochisoldi wrote:The spreadsheet can't handle same day or 30 day matching - not an issue for me but then again my spreadsheet represents my S104 pool, and an matched same day and 30day trades are out of scope.

Pochisoldi


You can easily add a 'red flag' which will spot and warn you about same day and 30 day trades without messing with sheet itself by using conditional formatting to highlight the row if the date is within 30 days of the date in the line above, and then make any adjustments manually.

pochisoldi
Lemon Slice
Posts: 939
Joined: November 4th, 2016, 11:33 am
Has thanked: 31 times
Been thanked: 459 times

Re: Excel entries and Section 104 Holding

#272709

Postby pochisoldi » December 21st, 2019, 8:13 am

BobbyD wrote:
pochisoldi wrote:The spreadsheet can't handle same day or 30 day matching - not an issue for me but then again my spreadsheet represents my S104 pool, and an matched same day and 30day trades are out of scope.

Pochisoldi


You can easily add a 'red flag' which will spot and warn you about same day and 30 day trades without messing with sheet itself by using conditional formatting to highlight the row if the date is within 30 days of the date in the line above, and then make any adjustments manually.


It should be noted that same day and 30 day trades need special handling:
In both cases, only a net increase or decrease in holding should be recorded. (the matched transactions should be handled elsewhere)

If a S104 transaction is matched 1:1 then the "number of shares" should be set to zero.
Doing this allows you to "delete" a transaction without having to edit individual cell formulae, and/or make a "note" a non-S104 transaction without breaking the spreadsheet.

Otherwise matched transactions should be replaced with a single transaction representing the net change in the S104 pool.
(For example:
buy 100 shares, sell 150 shares on the same day = S104 transaction of -50 shares
Sell 200 shares, buy 300 shares within 30 days = S104 transaction of +100 shares)
For a sale, the share price for the share should be the pool value/number of shares held (for a sale)
For a purchase, the commission/stamp duty figure should be apportioned between the matched and unmatched shares.
So for the example above, only 100/300ths of the commission/stamp duty should be entered. (the balance being added to the CGT calculation for the 30 day match).

Cookie
2 Lemon pips
Posts: 120
Joined: January 27th, 2018, 10:19 pm
Has thanked: 63 times
Been thanked: 6 times

Re: Excel entries and Section 104 Holding

#272782

Postby Cookie » December 21st, 2019, 4:23 pm

pochisoldi wrote:My original post below has some errors...

pochisoldi wrote:My CGT Excel spreadsheet has one worksheet for each share.
Each worksheet has columns as follows:
A=transaction date
B=Transaction type (e.g. Buy/Reinvest dividend/Sell)
C=not used
D=# shares (Number of shares bought/sold in this transaction) (For a sale, enter a negative number)
E=Price (Price per share in GBP)
F=Commission+Stamp Duty (for the transaction) (aka "cost of sale" or "cost of acquisition") (always positive)
G=Transaction value (calculated =ROUND(D2*E2,2)+F2) (i.e. =(number of shares * share price)+cost of transaction)
H=not used
I=not used
J=ongoing value of pool (For row 2 calculated as =H2, for row3 calculated as =J2+H3)

For each transaction I add another row.

The spreadsheet can't handle same day or 30 day matching - not an issue for me but then again my spreadsheet represents my S104 pool, and an matched same day and 30day trades are out of scope.

Pochisoldi


I missed out the following - mainly because I haven't done any partial sales!!

When making a disposal, use the spreadsheet to work out the acquisition cost:
1) Make an entry in the spreadsheet, with the number of shares sold (as a negative number). For the price (column D) use the average acquisition price.
This is calculated from the last entry in column J divided by the last entry in column D.
Easiest way: Assuming the disposal is on row 5 of the spreadsheet, the formula would be =ROUND(J4/D4,4)
This gives an average price to 4 decimal places.
Do not make any entry for commission/stamp duty

2)For the CGT calculation: The acquisition cost is the amount shown in column G
This cost includes the averaged costs of acquisition.

Note that entering a sale at the disposal price and including the "cost of sale" as I originally stated will completely screw up the pool value.

PochiSoldi


Are you able to anonymize and copy and paste a few lines into Google Sheets as an example and share here? Just the Section 104 calculation for now, maybe a couple purchases and a sale, not the 30 day matching as that might be confusing to start


Return to “Financial Software - Discussion”

Who is online

Users browsing this forum: No registered users and 4 guests