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

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.

This Best Practice includes
1 PDF file with with Google Sheet URL and How to Use text

Acquire business license for $22.00

Add to cart

Add to bookmarks

Discuss

Further information

To calculate trading profit using FIFO method

To calculate stock valuation using FIFO and then calculate profit or loss

Reviews

  • Rate this Downloadable Best Practice

    Write a review

  • James Montgomery(last updated: 07/05/2021 00:06)

keyboard_arrow_leftkeyboard_arrow_right

More Best Practices from Soni Excel Solutions

keyboard_arrow_leftkeyboard_arrow_right

Discussion feed for Google Sheet Version Of Bitcoin Cryptocurrency FIFO Gain Calculator

The user community and author are here to help. Go ahead!

  • Patricia Fenkell
    Can this products be used for tax purposes?
    arrow_drop_uparrow_drop_downReply reply
    • Soni Excel Solutions
      This tool calculates trading profit/loss using FIFO method, which further can be used for tax purposes as per the law of your country and further advise by a tax consultant.

      Some people use LIFO or Average cost methods also for calculating profit/loss.

      Regards
      Soni
      arrow_drop_uparrow_drop_downReply reply
    • Patricia Fenkell
      Can this be used for tax purposes?
      arrow_drop_uparrow_drop_downReply reply
      • Christopher Guynup
        Is there a way you could make a open "comment cell" somewhere - this would help the user document the purchase as a long term/short term gain for tax purposes, or some other note about the sale or purchase. Also, is there a way to relabel the sheet and have that auto update the fields with the symbol of the crypo purchased?
        arrow_drop_uparrow_drop_downReply reply
        • makaroniip pasta
          Hi I have downloaded the pdf and clicked on the link but could not access the file.
          arrow_drop_uparrow_drop_downReply reply
          • Soni Excel Solutions
            Hi, thanks for your purchase. Please let me know your email ID, so I can share the Google sheet with you.
            arrow_drop_uparrow_drop_downReply reply
            • makaroniip pasta
              Hi, can I reply to your email instead. Really not want to display my email in public
              arrow_drop_uparrow_drop_downReply reply
          • James Montgomery
            google wont give me permission to use Google Sheet Version Of Bitcoin Cryptocurrency FIFO Gain Calculator
            arrow_drop_uparrow_drop_downReply reply


            5.0 / 5 (1 votes)

            please wait...