Share tracking spreadsheet for tax purposes

Hi All,

I use Sharesight to track my huge portfolio of two ETFs. My sister is somewhat of a tech luddite and likes the idea of an excel spreadsheet on her personal hard drive. She is particularly after something that she can use at tax time when she sells shares, so she can easily have the information at hand needed for advising the tax office which parcel of shares she sold.

I remember many people on here mentioning having such a spreadsheet and was wondering if anyone would like to share it with me. Assuming it is not a proprietary program of course.

 

It would be a very simple spreadsheet listing date buying costings, purchase price, selling costs and sale price. One line per parcel.

Record dividends in a separate sheet

 

Exactly what I do and then EOFY, I handover that to my accountant.

 

Thanks @Terry_w and @geoffw I figured it'd probably be something as simple as that.

Just for clarity, is there a specific code or number, etc that identifies specific shares that you need to use when advising the tax office you have sold them? Or do you just identify them based on purchase date, time and price?

I know that when you sell, you can nominate what parcel of shares you are selling based on the best tax outcome for you and then just tell the tax office, just trying to make sure I give her the correct information on how that parcel is identified.

 

Google search ASXcode DOT ASX
eg BHP.ASX will get the Australian listed version in google, not UK (Bad example actually as UK does use 3 charecter shortcodes like USA and Australia)

I seem many using sharesight. It does have some flaws and I will share a recent client example

2020 tax the client produced a sharesight report they gave me that was based on "CGT Minimisation". Several months later it is evident a major unrealised CGT gain arises and the client asked about when the 12mths is available. I noted that the WRONG CGT method was used as it minimised tax cost without any regard for dates as all were under 12mths holdings. The use of different parcels in the 2020 calcs would have preserved a earlier CGT discount (available now) and not impacted total CGT costs by much. The discount value is worth several hundred thousand. Waiting to take profits is a high risk.

Sometimes reports are rubbish and it takes a skill adviser to give...advice. The taxpayer is now using "specific identification" as their CGT method. Its more work but can save a LOT of tax.

DONT assume excel and reports are correct

 

Its in the records and manner the taxpayer chooses to use. The ATO may ask how you arrived at that. And in some cases may ask you to demonstrate the earlier year sales were on a consistent basis (ie you didnt sell the same parcel twice in your calcs)

 

For etfs, though arent there capital returns?

Be tactical when selling shares. Eg say you have drp and have a lot of small purchases. Check the number of shares to sell that matches exactly the transactions themselves. So you might sell 216 shares because that matches exactly to a couple of drps.

 

If you are doing manually, I also have a column with the file name of the source document (buy/sell document or DRP notice).

 

@PKFFW hows this one?

Excel

 

Thats a good point. ETFs often pay amounts that impact the costbase eg AMIT adjustmnets (+ / - and also tax deferred). Sharesight can cater for that. Its usually overlooked by investors and I cant say I have ever seen a taxpayer provide me with the costbase adjustments. I need to ask for 2017, 2018, 2019 and 2020 tax statements for VAS for example.

I wouldnt be worried about matching parcels. If you wish, split a purchase so a sale can be calculated. Under specific identification a purchase contact and sale contract need not be the exact numbers.

 

@Redwing thanks, that should get her started.

Thanks everyone else too, your advice has made it clear that I should tell her to keep track of all purchases and then let a qualified professional take care of her tax!

 

I also do things manually, but prefer to have one sheet dedicated each stock. Easier for me to find things.

Sorry for the silly question, but why do you need this? Does the end of year statements have this information? Also, don’t companies send this info directly to the ATO, so it automatically gets pre filled?

 

I’m using a fancy tax fixer this year for the first time. Previously I’ve done it myself.

My return was pre-filled by the ATO, but too mystifyingly for me.

My tax guys asked for my Vanguard statements and my HIN.

Plus they were overjoyed I use Sharesight and asked for the Distribution and Capital Gains reports.