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 Long Term, Short Term and Total Gain/Loss after every transaction on FIFO basis. Besides this, spreadsheet 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.

Columns P:S and W:AF contains formulas for calculating costs of coins sold in parts. These formulas are complex and doing the calculation on FIFO method. These contains intermediate results. You may show or hide them as per your choice.

You can set exchange commission for buy or sell transaction in 2 ways:

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 amount of commission (in USD) for each transaction in column F and remove values from column E.

In second method you can manually fill amount of variable commission (in USD) for each transaction in column E. In that case, value in column F will NOT be used by sheet.

Formula to calculate Effective Price after Commission in column G will first look for commission amount in column E. If it is blank, then commission amount from column F will be used (which itself is derived from %age values from cells B1 and B2).

You can adopt both the methods together, but remember that value in column E always takes precedence over column F.

You can also see the quantity and purchase cost of balance coins in a particular price range. For this you can input Lower and Upper Price Ranges in cells AN4 and AO13 to see the Balance Quantity in hand with their cost 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 in cells AV2 and AW2 and sheet will show all salable coins with their long term or short term gains or losses for the proposed Sell transaction.

Column AL indicate status of a particular coins batch/slot e.g. Sold, In Hand or Partially Sold. Sheet highlights the rows in relevant colors accordingly.

Summary sheet shows the summary of gains from all the separate coins's sheets.

Bitcoin Cryptocurrency Long Term FIFO Capital Gains Excel Calculator