What should I use to work out my CGT with share trading?

[Nicks]

Hi All

As I have decided to ditch my dodgy accountant and to my TAX next year, my main concern is how on earth im going to work out the CGT with all my share trades, as this includes some shares that I've held over 12 months (so fall back on previous years trading and statements) and many trades throughout the current year.
I honestly dont think I could work it out, but if I could pump it all into something to work out for me that would be great.
I use Etrade and it gives me an excel (csv) file that I can export all my trades out to, its just what to do from here to get the results?
Help please! Thanks all.

 

[Julia]

Hi All

As I have decided to ditch my dodgy accountant and to my TAX next year, my main concern is how on earth im going to work out the CGT with all my share trades, as this includes some shares that I've held over 12 months (so fall back on previous years trading and statements) and many trades throughout the current year.
I honestly dont think I could work it out, but if I could pump it all into something to work out for me that would be great.
I use Etrade and it gives me an excel (csv) file that I can export all my trades out to, its just what to do from here to get the results?
Help please! Thanks all.

So, Nicks, are you saying that you haven't kept a basic share register, recording each company and the buy/sell details of each transaction?

Julia

 

[Nicks]

Yep. I am saying eactly that. Etrade provides what you mention in a csv file (this is an excel type file). You see, you cant do it that easy if you are selling different amounts to what you bought and if some of those were older than 12 months and some not.
My post was actually for some software (or I guess a method if you have one) that can calculate the CG for me.

 

[Space Cadet]

Nicks

I use an Excel spreadsheet to record my investments and dividends.

The spreadsheet also calculates all the taxable capital gains according to whether the shares were held for more than 1 year or not and then adds up all the cap gains and losses at the bottom using the sumif formula.

So provided I keep it up to date, which is easy for me then tax time is a breeze. I just print out the dividends and CGT worksheets and all the information I need is at my finger tips.

I have attached an image of the CGT sheet and an actual edited cut down demo spreadsheet below.

If you know how to use Excel then it shouldn't be difficult to customise your own spreadsheet to suit your circumstances or to use the one below as a starting point to build your own.

The Time Held is calculated automatically from the Bought/Sold Dates and all the CGT related columns to the righ of the Nett Proceeds are calculated automatically after you enter or import your trade information.

 

[deftfear]

Stex is a program I have used for clients with a large number of share sales, it is pretty good when it comes to difficult things like share splits, bonus issues etc. You can also put in DRP and and dividends as you go so it will give you a nice report with total dividends paid, franking credits etc.

If you put in all of your share purchases/ sales it will give you the total taxable gain that needs to go into your tax return.

It is reasonabl easy to use, I suggest having playing around with it before you put in all your share details as it can be difficult to make some changes.

The cost is a one off fee of $297, would be cheaper to do it on excel, which could be done easy enough as well, just a bit more time consuming I would think.

 

[Space Cadet]

hi deftfear,

the only time consuming bit for me was setting up the spreadsheet in the first place. But once set up, the rest is easy.

For those that have a large number of trades to record, if they can export their trades from e-trade or wherever into a comma delimited ascii file (.csv) then it should be relatively easy to copy and paste the data from the .csv file to their own customised Excel spreadsheet which does all the number crunching for them.

Obviously for those that don't have or know how to use a spreadsheet then then an option like you suggest is definitely worth considering.

I just like to use spreadsheets to set things up exactly how I want rather than use some third party application.

 

[Nicks]

Thanks guys, this is the sort of info I was after. Its a pain isn't it, I like trading but keeping a record stinks, wish Etrade would just have a facility that would do this part for me, since they are my broker and I do all my trades with them.
Does anyone know if power etrade or etrade pro does this? it would make sense wouldnt it.

 

[It's Snake Pliskin]

I keep a hand written book. It works fine.

 

[Julia]

I keep a hand written book. It works fine.

Ditto for me, Snake. Couldn't be easier and is completely clear.

Julia

 

[Chief Wigam]

This is interesting. I was hoping to find some free internet link to software which has a simple spreadsheet. The ones I have found don't count the year to date profit nor plot an equity curve. Does anyone know where I might get one? There must be some on the internet.

 

[Casper]

Hi Chief W

If you do find one on the www, it goes without saying that you should check the formulas to make sure they are correct. But I've gone and said it anyway

Learning to use Excel is not difficult if you have the aptitude for it and so my recommendation is to create your own customized spreadsheet to do your user specific number crunching and chart displays.

If you teach yourself how to write macros as well, then there is virtually nothing that any 3rd party portfolio management or accounting software application can do that you cannot also set up in Excel. For basic portfolio management and accounting the standard built-in Excel formulas and functions should be more than enough to set up your own personalised portfolio manager.

As a side note, I find entering data into a spreadsheet much quicker and easier than using the old paper and pencil system with its increased probability of errors due to manual calculations vs the automated calculations using spreadsheet standard or customised formulas.

 

[Kauri]

You could look at this.. an Aussie product I think.. and you can get a free trial to see if it meets your needs.

http://www.stator-afm.com/

 

[HelloU]

Nicks

I use an Excel spreadsheet to record my investments and dividends.

The spreadsheet also calculates all the taxable capital gains according to whether the shares were held for more than 1 year or not and then adds up all the cap gains and losses at the bottom using the sumif formula.

So provided I keep it up to date, which is easy for me then tax time is a breeze. I just print out the dividends and CGT worksheets and all the information I need is at my finger tips.

I have attached an image of the CGT sheet and an actual edited cut down demo spreadsheet below.

If you know how to use Excel then it shouldn't be difficult to customise your own spreadsheet to suit your circumstances or to use the one below as a starting point to build your own.

The Time Held is calculated automatically from the Bought/Sold Dates and all the CGT related columns to the righ of the Nett Proceeds are calculated automatically after you enter or import your trade information.

a historical thankyou for the demo excel file.

I find it does not cope with a loss the way I need.

I have tried to write the code....failed, download working CGT excel calculator from web....also fail...(cgt loss not done well).

Does anyone have a EXCEL sheet that they are willing to post that ....... will NOT apply any 50% cgt discount until ALL losses (both current and brought forward) have been offset?

(Just the cell formula will do)

 

[HelloU]

Does anyone have a EXCEL sheet that they are willing to post that ....... will NOT apply any 50% cgt discount until ALL losses (both current and brought forward) have been offset?

(Just the cell formula will do)

bump.........anyone do their own tax on excel? (or do people not attribute CG profit/losses this way perhaps?)

 

[Sir Burr]

This isn't free but have used it and seems OK. Does CG and dividends:
https://xlautomation.com.au/excel-spreadsheets/share-trade-tracker

Well at least compared to Stator mentioned above it's miles ahead in simplicity

BUT if you have 10 or less stocks I'd go for the free version of ShareSight.

 

[HelloU]

"Hi Scott,

I would be useful in the Capital Gain's Report to be able to enter a user defined figure for capital losses from previous periods

Alternatively now that you lock the capital gains period it maybe possible for you to retain prior year(s) losses that are to be carried over. This is more complex I'd think I'd be happy with the first solution that allows the user to enter the number they require.

Effectively the capital gains report produced by Sharesight is wrong when you are in a situation that you have a capital loss that is carried over from a previous period as you don't get the 50% discount till the loss is recovered. So I think this needs a higher priority especially since the incident has been raised in a number of topics other than this.

Thanks

Mike"

Above is a copy of a forum entry on Sharesight web site......I cannot respond to it or ask for clarification as I am not a member............this is from 4 years ago but it is the only comment on this subject............can anybody tell me if this is now fixed?

What I really just want is the excel formula that does this..............

 

[Sir Burr]

What I really just want is the excel formula that does this..............

30 seconds later and it's not locked:
https://www.asx200list.com/share-portfolio-manager/

Although, if you can add/subtract I'm not sure why an excel formula can't be worked out from the ATO information?
https://www.ato.gov.au/general/capital-gains-tax/working-out-your-capital-gain-or-loss/

I am not a member

Hang on and I'll check if my free membership (seconds to setup) will allow posting......YES!

You can ask, can you let us know here please?

 

[pixel]

bump.........anyone do their own tax on excel? (or do people not attribute CG profit/losses this way perhaps?)

When the GST was introduced in Australia, there was not a single PM software package on the market that dealt with GST and Company Tax Rates properly. So I wrote my own and had it ready and working ahead of time (1 July 2000). Have amended it over the years, and never looked back or looked for a commercially available system.
It keeps track of portfolio positions, either averaging over several buys, or entering each purchase or short-sell as a separate entry. My spreadsheet also maintains first and last buy date. When selling a position, I select the line (code and when bought) which takes care of another ATO rule.
Should I really run into a situation where one position is in deficit while another older one would attract the CGT Discount, it doesn't take an Einstein Moment to identify the situation and mark the CGT as 100% or 50% in the Trade P/L sheet.
No need for a separate Excel formula; all calculations are made "behind the scenes" when I use the macro-driven Portfolio Manager to record each individual transaction.

 

[HelloU]

thanks

 

[HelloU]

capitals gains calculations for excel the same as ATO calculator does them.......does CG discount automatically when losses gone.
Formula for cell C4 shown at top of screen shot.