I have found a Stock/Crypto Capital Gains & Profit Tax Calculation Sheet on this website here: [login to view URL]
This is the Google Sheet in question that I need edited for a personal use-case - [login to view URL]
I need 1 particular column of this Sheet updated so that I can can calculate the Capital Gains/Profits on asset purchases on a FIFO (first-in-first-out) basis. This particular Sheet is 90% complete and can calculate the Capital Gains and correctly matches parcels to each other (such as if a sell parcel is matched to several buy parcels, see Tax Tab Column U for this output).
I need to go one step further. In my country if an asset is held for LONGER than 1 year and then sold, it has a different tax rate as compared to if it was bought and sold WITHIN 1 year. This particular spreadsheet (see blog post above for more info) has a similar system in Tax Tab column V, although it defaults the tax rate to 0%.
What I need is for Tax Tab Columns U and V to be modified so that if a sell order matches with a buy order (remember a sell order of say 40 units could match to 4 different buy orders of 10 units each for example), it will check if the gap between the buy & sell, and if it is over 1 year it will apply a different tax rate to if it was under a year. Depending on if the matched part of the buy/sell is over or under a year in different it will place the Capital Gains/Profit into 2 separate columns.
- Column U will be the Capital Gains/Profit component owed for the part under a year
- Column V will be the Capital Gains/Profit component owed for OVER a year.
- 5x Apples are purchased on 1st Jan 2020 for $100 in Total
- 5x Apples are purchased on 1st June 2020 for $120 in Total
- 10x Apples are sold on 2nd March 2021 for $250 in Total. The profit is $25 on the first 5x Apples and as the gap is OVER a year it will go in Column V. The profit is $5 on the second group of 5x Apples, and as the gap is UNDER a year it will go in Column U.
Please see this Google Sheet for some example buy/sells to use and correct answers - https://docs.google.com/spreadsheets/d/1VfjTFH7LtVI2jb1PxWEx2Kozv7jCft6FsBaY5DfCr0A/edit#gid=0
- Work from this Spreadsheet as a starting point - [login to view URL]
- Replace broken FIFO parts of the Sheet to add FIFO Capital Gains calculation system, afterwards validate and ensure it is working correctly.
- For a particular sell order, the profit needs to be split into 2 components: Profit from relevant buy orders OVER a year, and UNDER a year.
- Spreadsheet will have multiple different tickers included in Column A (Tickers) of the transaction history. The formulas must be able to account for this and only report the capital gains for the correct ticker.
- FIFO calulations must correctly take into account brokerage costs.
- Units sold/bought must have support for fractions (ie. 1.54 shares bought or sold)
- Confirm that the Average Cost base portions of the Sheet are still working correct.
- The Yellow portions of the sample Sheet are fixed, these cannot be changed or moved in location.
- The Sheet must be able to scale to infinite going down in rows. The Sheet must not scale to infinite in columns as you add more data, columns must be fixed in number once made.
If you have any further questions don't hesitate to ask.