How To Create Crypto Tax Calculator on Excel

Some cryptocurrency tax software is already in existence. However, many crypto enthusiasts prefer Excel to other platforms. 

Let’s create a crypto tax calculator for our crypto transactions using Excel. We will also look into the Coingecko API and see how it can help us perform specific functions.

Using Excel and Coingecko API for Crypto Tax Calculation

This Excel calculator makes it easier to manage your transactions. It calculates prospective capital gains or losses when filing taxes. Also, it is crucial to stress that this guidance is for informative reasons, not tax advice. These are the steps:

  • Gathering Your Data

The first step is to collect your cryptocurrency-recorded transactions. You can get these records from the crypto exchanges or wallets you use. You need to export your entire transaction history from any crypto exchange and wallet you’ve used. The transaction history includes the date, buy/sell/trade, asset, amount, fees, and wallet addresses.

  • Cost Basis Method

Here, you must determine the cost-based approach you want to use to calculate gains and losses. We always use FIFO (first in, first out) and LIFO (Last In, First Out).

Source: X
  • Tax Rules

Every country that approves crypto transactions adheres to specific rules. Check the crypto tax legislation of the country you are in. This will give you an idea of how to calculate and report tax.

Set up the Excel Worksheet

These are the steps you need to follow to setup the Excel worksheet:

  • The first step is creating an Excel workbook.
  • Go ahead and give it a title; let’s use “Transactions’ for a start.
  • Now, in the sheet you’ve created, create Column headers.
  • The headers are for dates, Asset type, price, fees, and wallet address, including cost basis and your profit and losses.
  • The next step is importing your transaction history.
  • Transfer it from your wallet to the sheet you created.

There is another route to get to this exact location. Let’s see it.

  • The first step is to open Microsoft Excel.
  • Now, create a spreadsheet.
  • You’ll see the first row of the sheet.
  • Create column headers for easy labeling.
  • Arrange your Column header this way: Date, Transaction Type, Cryptocurrency, Amount.
  • You should also add Price per Unit, Fees, and Total Cost.

Now, let’s continue with the steps:

  • Inputting Your Data

Enter your transaction data into the corresponding columns of your spreadsheet. Ensure you abide by using dd-mm-yy for correct results,

  • How to calculate gains or losses

There are specific procedures for calculating gains and losses. Use these formulas to calculate your wins and losses.

  • Total Cost

In a separate column, create a formula to calculate the total cost for each transaction. Multiply the amount by the price per unit. When you get the value from the multiplication, add the fees, and you will get your total cost.

  • Current Value

In a separate column, calculate the current value of your crypto holdings. Here’s how to go about it. The first approach is ‘Manual Input.’ In this method, you enter the price for each cryptocurrency you hold.

  • Automatic Price Retrieval

Leverage an API (Application Programming Interface) like CoinGecko for a more automated approach. It also requires using VBA (Visual Basic for Applications) scripting within Excel. We’ll explore this option in a later section.

  • Capital Gains/Losses

Calculate each transaction’s gains or losses when you get the total cost and current value. Here’s how to do it. Subtract the total cost from the current value of your holdings.

  • Summarizing Your Results

Excel has a SUM function for this purpose. You can calculate your wins and losses using Excel’s SUM function. Where can you get the SUM function? It is in a separate place near the bottom of your ‘Capital Gains/Losses’ column.

Source: Coingecko

You can get all the details about this method in this article.

Tips and Considerations

Use conditional formatting to highlight cells that include capital gains or losses. Conditional formatting may provide a fast visual summary of your tax implications.

Please establish a separate sheet within your spreadsheet to track your assets. Also, keep an eye on your spreadsheet and ensure you update your most recent transactions.

This article should serve as a guide to calculating crypto tax using Excel. To optimize its points, consult a tax specialist.



The information discussed by Altcoin Buzz is not financial advice. This is for educational, entertainment, and informational purposes only. Any information or strategies are thoughts and opinions relevant to the accepted levels of risk tolerance of the writer/reviewers and their risk tolerance may be different than yours. We are not responsible for any losses that you may incur as a result of any investments directly or indirectly related to the information provided. Bitcoin and other cryptocurrencies are high-risk investments so please do your due diligence. Copyright Altcoin Buzz Pte Ltd.


Please enter your comment!
Please enter your name here

This site uses Akismet to reduce spam. Learn how your comment data is processed.