Originally published: 24/09/2021 09:36
Publication number: ELQ-32514-1
View all versions & Certificate
Publication number: ELQ-32514-1
View all versions & Certificate
Bitcoin Cryptocurrency Trading Weighted Average Cost Based Gain Excel Calculator
This is an easy-to-use Excel calculator for calculation of profits in cryptocurrency trading using Weighted Average Cost method.
cogs calculatorweighted average cost basisbtc gains calculatortrading profit calculatorcrypto gain calculatorbtc excel sheettrading gains excel sheetbtc average cost gains
Description
It calculates and shows the useful trading data for every transaction and summary for all trades combined.
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). 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 Paste Special (Values) option in columns A:D in each coin's sheet. Columns F:AR in 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, its column headings will automatically change to show coin's name.
Coin sheet is ready to process up tp 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 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 evaluate the possible 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 K4 and L4 and 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 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 transactions dates must be entered in ascending order. If you fill, by mistake, date value earlier than it's 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 Coin Codes sheet and required data from 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 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".
This is a simple table with several coin names and its codes. Summary sheet fetches 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.
It calculates and shows the useful trading data for every transaction and summary for all trades combined.
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). 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 Paste Special (Values) option in columns A:D in each coin's sheet. Columns F:AR in 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, its column headings will automatically change to show coin's name.
Coin sheet is ready to process up tp 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 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 evaluate the possible 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 K4 and L4 and 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 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 transactions dates must be entered in ascending order. If you fill, by mistake, date value earlier than it's 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 Coin Codes sheet and required data from 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 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".
This is a simple table with several coin names and its codes. Summary sheet fetches 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.
This Best Practice includes
1 Excel spreadsheet,
Further information
Calculate profit or loss in cryptocurrency trading using Weighted Average Cost method