HairyTurtle

A Capital Gain Tax Calculator

Over time, a script I wrote to automatically calculate my annual share trading capital gain tax evolved into an application used by a few people. If you do your own tax returns in Australia and if you trade shares as a hobby, I’m sure you know how much time and paperwork is involved to calculate the capital gain tax at the end of the financial year. This is especially true if you want to minimize the tax paid by utilizing the 50% CGT discount if you are disposing your (post-1985) assets that has been held for more than 12 months.

You can read more about the origin, reasoning and design patterns used to create this application at my blog post here.

Below is a simple form which allows you to upload some relevant documents in order to optimize the CGT position for the financial year of choice. Due to the application initially being developed for myself and a few close friends, the support is currently limited to files downloaded from Commsec and NABTrade websites.

In order to use this tool (which I call HairyTurtle), you will have to convert your trade confirmation file to match Commsec’s format before uploading to the tool here. Future support can be added. If you wish to use this tool with a report from a different broker, please reach out through contact me form along with the report your broker generates.

Executive Summary

This tool accepts a historical list of all the share / security trades in a CSV format, and outputs the profit (or loss) during a specific financial year of choice. The analysis methods available are

• First-in First-out (FIFO)
• Optimized

FIFO

FIFO is very easily understood: the units you buy first, gets sold first. However, this does not allow for utilizing of CGT discounts if you sold at a loss first (which uses up the share allocations bought first) and then selling at a profit (which by then means you would only have a recent chunk of units left to be sold without CGT discount). These are explained in detail in the blog post mentioned above.

Optimized

Optimized method goes through all trades to determine the best lot of shares to be sold at the end of the financial year. Although, it is important to note that once the tax return has been lodged, the units that were selected to be sold will be recorded down and no longer be eligible for consideration in the future.

Confused? Check out the example in the blog post!

Input Format

There are 4 files that can be uploaded to enable the analysis of CGT, only the first one is mandatory, while the rest help improve the accuracy and completeness of the analysis.

CSV is the comma-separated values that forms a table, with the first row typically describing the columns.

The first row of your CSV must be the headers. I don’t currently have a fuzzy match, so to avoid unhandled errors, change your CSV file first row to match exactly the same as the sample files downloaded at the end of this page. Some intelligence around this will be added in the future.

Trade Confirmation CSV

The trade confirmation file contains a list of historical trades you have made in the past. From your broker’s website, you can choose to export either:

(a) ALL of your historical trades, or

(b) Only the most recent trades starting from the first day (start) of the financial year (SOFY)

If you are merely interested in the potential savings you could’ve made in the past number of years of trading, go with option (a). This is because, changing your strategy from FIFO to Optimized require re-lodging of all the previous years tax return to account for the adjustment. Once a strategy has been chosen, you have to stick with the same strategy forever. More info here.

If you only want to automate CGT calculations for the past year, be sure to also supply the SOFY portfolio file as explained later.

A sample of Commsec trade confirmation is as follows. Be sure to merge the trade confirmation files into one if you are exporting from multiple brokers.

Trade Adjustments CSV

The trade adjustments file is used to account for trades that did not occur inside your broker’s trading platform. These include participating in Share Purchase Plans (SPP), getting shares allocated through Dividend Reinvestment Plan (DRP), ticker symbol changes, exercising options before they lapse, stock split or consolidation, or even company bankruptcy.

The trade adjustments CSV has the same columns as Trade Confirmation CSV, except you may have to manually enter the trades by hand due to information not exportable from brokers. In most cases, the “Purchase Price” should be zero, and the most important fields are the “Units” involved, the “Buy/Sell” key, and the “Net Proceeds” concerned. It is also important to note that some events such as the stock splits or option exercise requires a pair of buy/sell to indicate removal of original stock and re-addition of newly provided ones.

An example can be shown here:

Start of Financial Year Portfolio CSV

This file indicates the starting point for the calculator to analyze your financial year CGT. The portfolio file merely indicate what stocks you held on 1-July of the financial year being analyzed for. The algorithm then goes through different combinations to optimize the CGT for you in a way that abides by the ATO regulations.

The portfolio files have different columns, as shown below:

The date signify when the lot of shares were bought. Notice how some shares were split into multiple rows, that is because I have been using this tool for a few years and I have been diligently tracking the lots I have disposed of (CGT discount or not) which means they are no longer eligible for future considerations. If you never tracked the lots of shares held, you can consider using the last date you purchased the stock.

End of Financial Year Portfolio CSV

The EOFY portfolio CSV has the same columns as the SOFY one, except it indicates your portfolio on the last day of the financial year (30-June). This file can be fed into the calculated to enable the “ignore” feature which is used for sanity check to match the calculated EOFY portfolio that should match the expected one, otherwise, it spits out another file that lists all “ignored” shares where you can consider populating the Trade Adjustment CSV instead due to special events.

In the EOFY portfolio CSV, the dates are not important.

Generated Files

Disclaimer

Always confirm the output manually to make sure it makes sense. The information and the HairyTurtle tool provided on this website is for general information only. It should not be taken as constituting professional advice from me, the website owner. The results should not be taken as a substitute for professional advice. All reasonable care has been taken in preparing and designing HairyTurtle; however, I provide no warranties and make no representation that the information provided by HairyTurtle is appropriate for your particular circumstances or indicate you should follow a particular course of action.

You have the obligation to double check the generated report to confirm its validity. Understand that you have the responsibility to keep track of the assets of which dates you still have. HairyTurtle does not store any data for you whatsoever, it only uses a cookie to store the session unique ID to fetch data which is removed when you refresh the page. HairyTurtle does not track the user and cannot link the data it receives for analysis back to the user.

HairyTurtle!

That was definitely information overload. However, I can’t get away providing a tool that deals with financials without detailed explanations to help generate accurate outputs. This tool is implemented based on the rules outlined by the Commonwealth Consolidated Act here.

Please do not hesitate to reach out for any questions, and I hope you enjoy using the tool to increase your productivity. Share it if you find it useful, and be sure to check out HairyTurtle’s blog post where I go through some scenarios in detail in the epilogue section!