Google Sheet Version of Bitcoin Cryptocurrency FIFO Gain Calculator
This is an improved version of Google sheet for calculating the gains (year-wise) in crypto trading using FIFO method.
Original Best Practice: Bitcoin Cryptocurrency FIFO Gain Excel Calculator with Yearly Summary for Taxation by Soni Excel Solutions
google sheetsbitcoin gaincryptocurrency gaincryptocurrency taxstock valuationlifo calculatorfifo trading gainsfifo calculatorgsheet calculatoryearly fifo gain calculator
Description
You will just have to put your trading details i.e. Transaction Type (buy/sale), Date & Time of Transaction, BTC Quantity in columns A:D and sheet will automatically calculate FIFO gains and other important data for every row. You will see Total Gain/Loss after every transaction on FIFO basis. Besides this, sheet will also show you other important data e.g. Cumulative Cost of BTC Purchased, Cumulative Earning from sell of BTC, Total BTC Purchased, Total BTC Sold, BTC in Hand after every transaction. It will also show you the earliest and latest buying dates and total costs of coins which are sold in every sell transaction.
You can set exchange commission for buy or sell transaction in either of the two or both methods combined:
First method is to set a fixed percentage for all Buy and Sell transactions in cells B1 and B2. It will be used to fill the amount of commission (in USD) for each transaction in column F and remove values from column E.
In the second method you can manually fill the amount of variable commission (in USD) for each transaction in column E. In that case, value in column F will NOT be used by the sheet.
Google Sheet is ready to calculate FIFO gains up to 2000 transactions, which can easily be extended further by dragging the formulas as per your requirement.
Sheet automatically highlights the rows for Buy and Sell transactions in Green and Orange colors so user can easily identify a Buy or sell transaction.
You can see the quantity and buying costs of balance coins in a particular price range by filling Lower and Upper Price Ranges to see the Balance coins in hand with their costs in a particular price range.
You can also evaluate the possible FIFO gain or loss for a proposed sell at a preferable price in easy way. To do so, you need to input proposed Quantity to Sell and proposed Price to Sell and sheet will show all saleable coins with their long term or short term gains or losses for the proposed Sell transaction.
Sheet highlights relevant cell in RED to help user identify some possible mistakes:
A Buy transaction has negative quantity.
A Sell transaction has positive quantity.
A sell transaction has a higher value of Sell quantity than its quantity in hand.
A date value for any buy/sell transaction is earlier than the date entered in its previous row.
Summary sheet shows important data from all the coin sheets. Here you need to input coin code in column B up to 100 rows. It will fetch the coin name from Coin Codes sheet and required data from relevant coin sheet.
Columns A:L show summary from all coin sheets for all transactions.
Columns N:S show summary from all coin sheets for transactions done in any particular year. You can select the starting year in cell N1.
Tables in columns from U:Z onwards show summary data from all coin sheets for the next consecutive years. No need to select or change the years for every table.
If you fill any coin name in column B which is unavailable in Coin Codes sheet it will show an error message "Add coin in sheet : Coin Codes".
If you fill any coin name in column B without creating a sheet for it, then it will show an error message "No Sheet".
Sheet automatically checks for some common data entry mistakes and highlights those cells to help you easily identify and correct them.
I believe that you will find this Google Sheet calculator very handy in analyzing and keeping records of your trades and perfect in calculating the gains with 100% accuracy for taxation purpose.
You will just have to put your trading details i.e. Transaction Type (buy/sale), Date & Time of Transaction, BTC Quantity in columns A:D and sheet will automatically calculate FIFO gains and other important data for every row. You will see Total Gain/Loss after every transaction on FIFO basis. Besides this, sheet will also show you other important data e.g. Cumulative Cost of BTC Purchased, Cumulative Earning from sell of BTC, Total BTC Purchased, Total BTC Sold, BTC in Hand after every transaction. It will also show you the earliest and latest buying dates and total costs of coins which are sold in every sell transaction.
You can set exchange commission for buy or sell transaction in either of the two or both methods combined:
First method is to set a fixed percentage for all Buy and Sell transactions in cells B1 and B2. It will be used to fill the amount of commission (in USD) for each transaction in column F and remove values from column E.
In the second method you can manually fill the amount of variable commission (in USD) for each transaction in column E. In that case, value in column F will NOT be used by the sheet.
Google Sheet is ready to calculate FIFO gains up to 2000 transactions, which can easily be extended further by dragging the formulas as per your requirement.
Sheet automatically highlights the rows for Buy and Sell transactions in Green and Orange colors so user can easily identify a Buy or sell transaction.
You can see the quantity and buying costs of balance coins in a particular price range by filling Lower and Upper Price Ranges to see the Balance coins in hand with their costs in a particular price range.
You can also evaluate the possible FIFO gain or loss for a proposed sell at a preferable price in easy way. To do so, you need to input proposed Quantity to Sell and proposed Price to Sell and sheet will show all saleable coins with their long term or short term gains or losses for the proposed Sell transaction.
Sheet highlights relevant cell in RED to help user identify some possible mistakes:
A Buy transaction has negative quantity.
A Sell transaction has positive quantity.
A sell transaction has a higher value of Sell quantity than its quantity in hand.
A date value for any buy/sell transaction is earlier than the date entered in its previous row.
Summary sheet shows important data from all the coin sheets. Here you need to input coin code in column B up to 100 rows. It will fetch the coin name from Coin Codes sheet and required data from relevant coin sheet.
Columns A:L show summary from all coin sheets for all transactions.
Columns N:S show summary from all coin sheets for transactions done in any particular year. You can select the starting year in cell N1.
Tables in columns from U:Z onwards show summary data from all coin sheets for the next consecutive years. No need to select or change the years for every table.
If you fill any coin name in column B which is unavailable in Coin Codes sheet it will show an error message "Add coin in sheet : Coin Codes".
If you fill any coin name in column B without creating a sheet for it, then it will show an error message "No Sheet".
Sheet automatically checks for some common data entry mistakes and highlights those cells to help you easily identify and correct them.
I believe that you will find this Google Sheet calculator very handy in analyzing and keeping records of your trades and perfect in calculating the gains with 100% accuracy for taxation purpose.
This Best Practice includes
1 PDF file with with Google Sheet URL and How to Use text
Further information
To calculate trading profit using FIFO method
To calculate stock valuation using FIFO and then calculate profit or loss
