Bitcoin Cryptocurrency Long Term FIFO Capital Gains Excel Calculator

Description
This tool uses separate sheets for each coin or stock to show detailed transaction data and a summary sheet to show the combined summary of all sheets. You can easily copy COIN sheet to create new sheets as per your requirement for coins or stocks you trade. Formulas and error messages in coin sheets will precisely guide you to identify and avoid mistakes in filling the transaction data.

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 and display FIFO Long Term, Short Term and Total Gain/Loss, Cumulative Cost, Cumulative Earning, and Stock in Hand after every transaction. Column AK indicates current status of a particular coins batch/lot whether it is Sold, In Hand or Partially Sold. Sheet highlights the rows in relevant colors accordingly.

You can set exchange commission for buy or sell transaction in 2 ways. The 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 fiat currency) for each transaction in column F and remove values from column E. In second method you can manually fill the amount of variable commission (in fiat currency) 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 see the quantity and buying costs of balance coins in different price ranges between minimum and maximum traded prices (shown in AN4 and AO13 respectively) to see the Balance coins in hand with their costs in 10 different price ranges.

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 saleable coins with their long term or short term gains or losses for the proposed Sell transaction.

Summary sheet shows the summary of gains from each coin's sheet. In Summary sheet formulas are set to show you all the important data by putting just the coin’s code. You can see summary data for all period or any selected year and FIFO gains year-wise for 10 consecutive years.