Regarding this Google Sheet -
As an outcome I need the FIFO Capital Gains/Profits for the profits on purchases and sales of items. You can either fix the existing FIFO formulas or replace the entire system, whatever you want as long as it's accurate. The yellow columns are fixed however, I cannot change these columns if you make a new Sheet.
What I need is 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 correctly find the FIFO profit and then split it into 2 components. Profit from purchases OVER a year will go in Column V, Profit from purchases UNDER a year will go in Column U.
- 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.
- 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. Please only apply if you have made a FIFO Spreadsheet before, I have already one one incomplete and would like to avoid again to not lose time.