Em Andamento

Update Formula for FIFO Capital Gains Spreadsheet

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.

End Goal:

- 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.

Habilidades: Google Sheets, Excel

Veja mais: formula calculating capital interest repayment, formula interest capital repayment mortgage, capital repayment spreadsheet formula, eplain excel budget spreadsheet stock symbols, free spreadsheet stock control, excel capital expenses spreadsheet examples, invested capital excel spreadsheet, mortgage interest capital repayment spreadsheet, excel spreadsheet stock cost basis, formula calculate capital repayment, excel spreadsheet stock control forms, find formula calculate capital repayment, excel spreadsheet stock details, mortgage formula interest capital repayment excel, mortgage formula excel capital interest, capital expense spreadsheet, fifo capital gains excel, capital gains fifo, excel fifo capital gains

Acerca do Empregador:
( 0 comentários ) Melbourne, Australia

ID do Projeto: #30523641

Concedido a:


Sir, I am FIFO, LIFO, AVCO, All Excel formulas master. I am Excel Advance Level Expert. This is a basic work for me. I can complete this project with in your desired time with added Quality. "I have completed similar p Mais

$88 AUD em 2 dias
(70 Comentários)

8 freelancers estão ofertando em média $106 nesse trabalho

(12 Comentários)

''FIFO EXPERT'' I'm an expert Financial Analyst and Excel master holding long stretches of involvement with using advance level instruments and elements of Excel (counting VBA and Macro) to perform Data Analysis, Data Mais

$160 AUD em 1 dia
(48 Comentários)

Senior excel , google sheet Expert. As 9+ years experiences in these field. I can give good quality work. I have read the guidelines of your work.I believe that i can provide you the best quality works you are anticip Mais

$140 AUD in 2 dias
(97 Comentários)
(21 Comentários)

-previous experiencein such topics; -eager to discuss a lot in this chat stream; -degree stats / maths;

$160 AUD em 1 dia
(1 Comentário)

Ok saurabh ko bhi nhi hai na to be a great day of school and I have a great day

$140 AUD in 7 dias
(0 Comentários)

Hello, I have been doing excel work with formulas and macros for over 5 years. I don't have any reviews as I have recently started using my skills on freelancer but i can totally assure you of high quality work if give Mais

$100 AUD in 3 dias
(0 Comentários)