Keeping track of your crypto transactions can be challenging: you may have accounts across various L1’s, in different liquidity pools, or throughout multiple exchanges. With the possibility for so many accounts and positions, consolidating your investments to find out your overall return is difficult. Additionally, crypto tax regulation can vary across countries, adding to the complexity of determining your taxable income.

In today’s article we will cover the basics of crypto taxes, before building our own basic template using a specific accounting method and CoinGecko API. Let’s dive in!

Crypto Taxes: How Much Tax Will I Pay on Crypto?

If your job pays you in cryptocurrency, you will pay ordinary income tax which varies depending on your region and tax bracket. However, if you dispose of your crypto holdings, this can be considered a short-term or long-term capital gains tax, contingent on the time you have held the asset for.

If you have held this cryptocurrency for more than a year, you will be subject to long-term capital gains tax (0%-20% in the US).

Alternatively, if you have held your cryptocurrency for less than a year, you will be subject to short-term capital gains tax, which is treated the same as ordinary income, taxed between 10% and 37% depending on your bracket.

How to Calculate your Capital Gains and Losses

To calculate your capital gains, you would first need to determine your cost basis. Put simply, this is the original price you paid for an asset. Your cost basis is pertinent to calculate the profit you achieved from conducting any transaction: selling, swapping, or being airdropped any crypto assets, for example, fall within taxable transactions. Therefore, the cost basis is the original price plus any related transaction fees.

For example, if you bought ETH for $2,000, and you paid 0.25% in gas fees ($5), your cost basis would be $2,005. If you later sell this ETH for $3,000, paying a 0.05% gas fee ($15), your cost basis is now $2,020.

To calculate your capital gains or losses you simply subtract your cost basis from the sale price, and, in this example, your capital gain would be $3,000 – $2,020 = $980. You made a capital gain of $980, which you will need to pay Capital Gains Tax on.

There are a number of different cost basis methods used in accounting. Depending on the country in which you pay taxes you may even be entitled to select your chosen method, as in the United States. Provided you can identify the specific asset you are disposing of, the IRS states you can select your chosen cost basis method, such as HIFO, FIFO, or LIFO. Whatever method you choose, you must use it consistently when calculating your gains or losses. If you are uncertain as to which cost basis method you can use, contact the IRS to confirm.

If we return to our earlier example, we can see how our capital gains or losses vary depending on the chosen method. Lets assume our transaction history includes 1 ETH bought  in 2021 for $2100, 1 ETH bought later in 2021 for $4100, and 1 ETH bought in 2023 for $1800, and 1 ETH sold in 2023 for $2200. If we went by the First in, First out (FIFO) method our capital gains would be $2200-2100 = $100. If we used Last in, First out (LIFO) our capital gains would be $2200-$1800 = $400. Lastly, if we use Highest in, First out (HIFO) our capital losses would be $2200-$4100 = -$1300. Every transaction fee involved in moving, swapping, or sending out cryptocurrency can also be added to our cost basis, lowering the capital gains.

FIFO is the most common accounting method used by investors to calculate their capital gains. If the price of crypto has dropped since you first bought it, this method can lower your capital gains tax. Additionally, as FIFO disposes of your longest-held cryptocurrency, depending on your situation, you can take advantage of a lower long-term capital gains tax.

Disclaimer: Ensure the template is compatible with the tax laws in each country where you have a tax obligation. This may require some customization or modification of the template. Our template has been made specifically to cater to US tax laws.

How to Calculate Your Adjusted Cost Basis (ACB)

The adjusted cost basis is the consolidation of the cost of any asset and any related fees. This method is applicable to the Canadian Revenue Agency.  You can either use the fair market value (FMV) of the cryptocurrency at the end of the year, or when you acquired it, depending on which is lower. Additionally, they require the following to be recorded:

• Date and time of each transaction
• Value of the crypto-asset in CAD
• Number of units and type of cryptocurrency
• The addresses associated with each digital wallet used
• The beginning and end wallet balance for each crypto-asset for each year 

If you hold an array of assets, you can choose to use the FMV for your entire portfolio at the end of the year. Alternatively, you could choose the exchange rate shown by the same exchange broker you used or an average of their high/low/open/close across a number of brokers. Preferably, you could use an aggregated price across all exchanges for your cryptocurrencies – as offered by CoinGecko API.

Prerequisites

To build our Tax Calculator, we will use Microsoft Excel 365 and the Virtual Basic for Applications (VBA) language.

Ensure you also have access to the CoinGecko Demo API. Scroll down to the “Create Demo Account” button found underneath the price options on the right hand side of the screen. Once you’ve created your account, you’ll be directed to the Developer Dashboard where you can generate your API key.

We will be accessing the /coins/{id}/history endpoint to obtain CoinGecko’s price for a certain cryptocurrency on a specific date. This provides an aggregated price for your selected cryptocurrency on the date it was involved in a transaction. As we will be using VBA for this spreadsheet, ensure you save the document as an Excel macro-enabled workbook.

Build Your Own Crypto Tax Calculator in Excel

Step 1: Creating a CoinGecko Historical Price Data Function

Ensure you have the ‘Developer’ tab on your Microsoft Excel. If you do not, go to ‘File’ > ‘Options’ > ‘Customize Ribbon’, and tick ‘Developer’. Once selected, the ‘Developer’ tab should appear at the top of your document, next to ‘Help’. Then, click the tab, select Visual Basic and the Microsoft Visual Basic for Applications tab will appear, known as the Virtual Basic Editor. 

From here, we will select ‘Insert’ and ‘Module’ which is used to store any VBA code that we write. This is shown below. Within ‘Module 1’ we can begin writing our VBA code.

Our code consists of the following components: 

Function GetCryptoPrice(cryptocurrency As String, dateInput As Date) As Variant

1. Function Declaration: We will name our function quite literally‘GetCryptoPrice’. It will take two inputs: the name of the cryptocurrency and the date for which you want to retrieve the price.

Dim http As Object
    Dim url As String
    Dim JSONResponseText As String
    Dim retryCount As Integer
    Dim maxRetries As Integer
    Dim waitTime As Integer

2. Variable Declaration: We use “dim” to characterize our variables into strings and integers, before storing the HTTP request, URL, JSON response text, retry count, maximum number of retries, and wait time between retries. We include retries to “refresh” our data, as the number of calls allowed will vary depending on your CoinGecko API plan.

        ' Set the maximum number of retries and the wait time between retries
    maxRetries = 5
    waitTime = 20
    ' Initialize the retry count

     retryCount = 0

3. Setting Retry Parameters: We will set the maximum number of retries (5)  and the wait time between retries (20 seconds). We will also initialize the retry count to 0.

' Loop until the function succeeds or the maximum number of retries is reached
    Do While retryCount < maxRetries

4. Retrying the Request: To retry our request the function enters a loop where it tries to retrieve the cryptocurrency price until it succeeds or reaches the maximum number of retries.

         ' Define the URL with the input cryptocurrency and date
        url = "https://api.coingecko.com/api/v3/coins/" & cryptocurrency & "/history?date=" & Format(dateInput, "dd-mm-yyyy")
        
        ' Create an HTTP request object
        Set http = CreateObject("MSXML2.ServerXMLHTTP")

' Open the request with the specified URL
        http.Open "GET", url, False
        
        ' Send the request
        http.send

💡If you are a paid API user and have a pro API key, do remember to call the root URL https://pro-api.coingecko.com/api/v3/ instead of https://api.coingecko.com/api/v3/.

5. Building the Request URL: This constructs the URL for the API request based on the cryptocurrency name and date inputs, before creating the HTTP Request Object to make an HTTP request. This also sends the HTTP request to the API. ‘MSXML2.ServerXMLHTTP’ is an object provided by Microsoft XML Core Services, which allows you to send HTTP requests from within your VBA code. Additionally, GET is used in this instance, as it is a GET request to retrieve data from a server. Our previously defined ‘url’ specifies the resource that the client is requesting from the server (the historical price endpoint). The ‘False’ parameter indicates whether the request should be synchronous (false) or asynchronous (true). Setting it to false allows for our code execution to be one line at a time, making the code wait until the request is complete before moving on to the next line.

        ' Check if the request was successful (status code 200)
        If http.Status = 200 Then
            ' Store the JSON response text
            JSONResponseText = http.responseText
            
            ' Extract the USD price from the JSON response
            Dim startIdx As Long
            Dim endIdx As Long
            startIdx = InStr(JSONResponseText, """usd"":") + Len("""usd"":")
            endIdx = InStr(startIdx, JSONResponseText, ",")
            
            If startIdx > 0 And endIdx > startIdx Then
                Dim usdPrice As Variant
                usdPrice = Mid(JSONResponseText, startIdx, endIdx - startIdx)
                
                ' Return the extracted USD price
                GetCryptoPrice = usdPrice
                Exit Function

6. Extracting the USD price from the JSON response: If the request is successful (status code 200), it extracts the USD price from the JSON response and returns it through these steps:  

• JSONResponseText = http.responseText: This line assigns the response text from the HTTP request to the variable JSONResponseText. http.responseText contains the entire response received from the server in the form of a string, which, in our case, includes the price of our chosen cryptocurrency in relation to a number of fiat currencies: CAD, GBP, USD, etc. For our purposes, we only desire the price in relation to one currency: USD.
• startIdx = InStr(JSONResponseText, """usd"":") + Len("""usd"":"): Here, InStr function is used to find the position of the first occurrence of the substring “””usd””:” within the JSONResponseText string. The Len(“””usd””:”) part calculates the length of the substring “””usd””:”. This length is added to the position of the substring found by InStr to find the starting index of the USD price value within the JSON response. 
• endIdx = InStr(startIdx, JSONResponseText, ","): This line finds the position of the comma (,) character starting from the startIdx. It helps to locate the end of the USD price value within the JSON response.
• The If statement checks whether both startIdx and endIdx are valid indices within the JSON response, ensuring that they’re greater than 0 and that endIdx is greater than startIdx. 
• If both indices are valid, it means that a valid USD price exists in the JSON response. It then extracts the substring representing the USD price from the JSONResponseText using the Mid function. The Mid function extracts a portion of a string starting from a specified position (startIdx) and ending at a specified position (endIdx – startIdx). This extracted substring represents the USD price.

  Else
                ' Return an error message if the USD price cannot be extracted
                GetCryptoPrice = "Error: USD price not found in JSON response."
                Exit Function
            End If
        Else
            ' Error handling if the request fails
            GetCryptoPrice = "Error: Unable to retrieve data from API"
            
            ' Increment the retry count
            retryCount = retryCount + 1
            
            ' Wait for the specified time before retrying
            Application.Wait (Now + TimeValue("0:00:" & waitTime))
        End If
        
        ' Clean up objects
        Set http = Nothing
    Loop
    
    ' Return an error message if the maximum number of retries is reached
    GetCryptoPrice = "Error: Maximum number of retries reached."
End Function

7. If not, the code handles the error by incrementing the retry count and waiting for a specified time before retrying.

Now that we have our function created, let’s give it a try! Simply save the module using the ‘Save’ button in the Visual Basic Editor and go to a sheet in excel. For example, you can type ‘ethereum’ in cell A15, ‘30-06-2021’ in cell B15 (ensure date is of the form dd-mm-yyyy to avoid issues with the API request), and type ‘=Module1.GetCryptoPrice(A15,B15)’ into cell C15 to retrieve the price of ethereum on that day!

Step 2: Getting our Transaction History & Template

The complexity of accessing your transaction history varies depending on your chosen wallet. Assuming we use Metamask, open your wallet, select your chosen network (e.g. Ethereum mainnet), and hit the three dots in the top right, and select ‘View on Explorer’. In this instance, this will take you to Etherscan where you can download a CSV of your transaction history in the bottom right, underneath your transactions.

Now we will use the dates of our transactions, Value_IN(ETH), Value_OUT(ETH), potentially contract addresses and methods, and TxnFee (USD). We will build a tax calculator over the period of January 1st, 2021 to December 31st, 2021 in this example. Now, if we return to our excel sheet we can set up our table like so:

As shown above, we will take the date of our transactions from our csv Etherscan file and place it into column C, alongside the Value_IN under purchased assets, and the Value_OUT, in sold assets. We will convert our date stamps into the form “dd-mm-yyy”, so it is an appropriate input for our function, by using:

• =TEXT(B19,"dd-mm-yyyy")

Next we can use our function to draw price data as we have done before, simply type:

• =Module1.GetCryptoPrice (ensure “ethereum” or the properly typed coin id is next to it)

We can multiply our Value_IN column by these prices to determine what our Value_IN in USD was (Column F above). We then include our transaction fees in the column next to that.

Repeat the same process for sold assets, but multiply Value_OUT by Price on Date to find the Price on Date for that column in USD.

Step 3: Determine Capital Gains with FIFO Accounting Method

We will use the “FIFO” accounting method to determine our capital gains. To do this we will make two columns to the right of our Transaction Fee column: Quantity Sold and Cost of Assets Sold (COAS is an arbitrary name). Assuming we sell the crypto we purchased first, our FIRST cell of our quantity sold is calculated by using the formula:

• = min(amount in corresponding Value_IN column, sum of Value_OUT column in sold assets)

The following cells in this column will minimize either the corresponding Value_IN, or the sum of the sold assets subtracted by a running total of previously sold Ethereum:

At the bottom, you can notice our Quantity Sold is equivalent to the Sum of our Value_OUT. This helps us calculate our cost basis (COAS): multiply the quantity sold by the price on that day and add the transaction fee:

• =(H19*D19)+G19

Consolidating this column gives us our cost basis and allows us to calculate our capital gains/losses:

However, we still need to determine if any assets have been held for longer than a year, and, subsequently, if we are entitled to a long-term capital gains tax. To do this we can use the following formula to find any dates that precede our chosen time horizon (in this case January 1st, 2021 to December 31st, 2021):

• =IF(SUM((B19:B34<B4)*(F19:F34)), SUMIFS(F19:F34, B19:B34, "<=" & B4, A19:A34, "ethereum"), 0)

This isolates any dates before January 1st 2021, highlighting any long-term assets. Following the summation of the Value (In USD) on these older dates, our result is included in the long-term capital gains cell. Our short-term capital gains is thus the difference between our total and long-term.

You can determine which tax bracket you fall into in the table included below that.

Download the Free Crypto Tax Calculator Excel Template

Instead of building your own crypto tax calculator in Excel, simply enter your email below to download our free crypto tax calculator template – so you can start calculating your taxes today!

Given the onerous process of reporting taxes, we hope this guide alleviates your crypto-related worries. The CoinGecko API offers a comprehensive and readily usable data source for accurate and aggregated historical pricing, making your capital gains calculator straightforward. Be sure to re-read any updated tax documentation for your region, especially given the exponential movement of this industry, as things may change rapidly.

Interested in more free, downloadable templates? Check out our crypto portfolio tracker template on Google Sheets.