This is an easy-to-use Google Sheet calculator for calculation of gains in cryptocurrency trading using Weighted Average Cost method.
It calculates and shows the useful trading data and summary for all trades combined.
How to input data:
Copy Coin sheet to create new sheets (one sheet for each coin you trade). Rename the newly created sheet with the coin code (e.g. BTC for Bitcoin) and input coin code in cell A1. Few coin sheets are already created; you can create more as per your requirement. Type coin codes for all the coin sheets in column B of Summary sheet.
Prepare raw data for your trades in similar format/layout as shown in Raw Data Tab.
For every coin you trade, copy the raw data and paste it without any formatting using the Paste Special (Values) option in columns A:D in each coin's sheet. Columns F:P in the coin's sheet contain formulas, please do not edit or delete them.
COIN sheet is a blank sheet you can copy to create new sheets (one sheet for each coin you trade).
When you rename the COIN (or newly copied) sheet, please type coin code in cell A1 also so that its column headings will automatically change to show the coin's code.
Coin sheet is ready to process up to 2000 transactions (which can be increased as per requirement).
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 Weighted Average Cost gains and other important data for all trades. You will see Total Gain/Loss for all the transactions on Weighted Average Cost basis in 4th row (A4:I4).
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 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.
Formulas to calculate Effective Price after Commission in column G will first look for commission amount in column E. If it is blank, the 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 evaluate the possible 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 K4 and L4 and the sheet will show possible gain or loss for the proposed Sell transaction. Proposed Quantity for Sell in K4 cannot be more than Quantity in Hand in H4.
For every Buy transaction, coin quantity must be a positive number. If you (by mistake) enter a negative number, it will be shown in Red color, so you can easily find and correct it. A formula in cell D1 will also show the top most cell with incorrect quantity.
For every Sell transaction, coin quantity must be a negative number. If you (by mistake) enter a positive number, it will be shown in Red color, so you can easily find and correct it. A formula in cell G1 will also show the top most cell with incorrect quantity.
Sold Qty (column C) for any Sell transaction cannot be greater than Qty in Hand (column P) in the previous row. If you fill incorrect sell quantity (i.e. more than you have), cell K1 will show the error cell and highlight it in Red.
All transaction dates must be entered in ascending order. If you fill, by mistake, the date value earlier than its previous transaction's date, then cell N1 will show the error cell and highlight in Red.
Summary sheet shows important data from all the coin sheets. From cell A1 you can select the desired currency code to be filled in column headings of all sheets.
Here you need to input coin code in column B down to 100 rows. It will fetch the coin name from the Coin Codes sheet and desired data from the relevant coin sheet.
Columns A:L show summary data from all coin sheets for all transactions.
If you fill any coin name in column B which is unavailable in the 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".
Coin Codes Sheet:
This is a simple table with several coin names and its codes. Summary sheet fetches the coin's name from this table. You can add, insert or remove any coin names and codes as per your requirement up to 1000 rows.
Alternate buy option using Cryptos:
Besides PayPal, you can also buy this calculator for any of the following cryptos. Please send the equivalent amount of crypto to any of the addresses given below and put transaction details as checkout Note. You will get the tool in your mailbox after payment confirmation.
LTC : LKFcSetKJY2xufP7UUNZdA8EbPwegKAxg3