This is an improved version of easy-to-use Google Sheet Calculator with Yearly Summary for calculation of long term and short term profits in cryptocurrency trading using FIFO method. It calculates and shows the useful trading data for every transaction and summary for all trades combined and year-wise.
This tool uses separate sheets for each coin or stock to show detailed transaction data and a summary sheet to show 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. 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 fiat currency) for each transaction in column F and remove values from column E. In second method you can manually fill 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 m