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 the 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 the current status of a particular coin batch/lot whether it is Sold, In Hand or Partially Sold. Sheet highlights the rows in relevant colors accordingly.
You can set an exchange commission for buy or sell transactions 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 the second method, you can manually fill the amount of variable commission (in fiat currency) for each transaction in column E. In that case, the value in column F will NOT be used by the 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 the value in column E always takes precedence over column F.
You can also see the quantity and buying costs of balance coins in a particular price range by filling Lower and Upper Price Ranges in cells AN4 and AO13 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 an easy way. To do so, you need to input the proposed Quantity to Sell and proposed Price to Sell in cells AV2 and AW2 and the sheet will show all saleable coins with their long-term or short-term gains or losses for the proposed Sell transaction.
The Summary sheet shows the summary of gains from each coin's sheet. In the 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 periods or any selected year and FIFO gains year-wise for 10 consecutive years.
We believe that you will find this Excel calculator very handy in analyzing and keeping records of your trades and perfect in calculating the gains with 100% accuracy for taxation purposes.