DIY - Build Your Own Crypto Tax Calculator with Google Sheets
- Get cross-chain activity for address
- Get token balances for address
- Get ERC20 token transfers for address
Navigating the turbulent seas of cryptocurrency taxes is like threading a needle while riding a rollercoaster—challenging, dizzying, and with a high chance of screaming involved. The rules are as clear as mud and just when you think you've got a handle on it, the laws change 😱.
Thankfully, there are plenty of great low-cost crypto tax softwares out there such as Koinly, CoinLedger, Awaken Tax, CryptoTaxCalculator, and BlockPit to name a few. However, if you’re looking for a DIY spreadsheet tool, check out this Crypto Accounting Tool (powered by Covalent) to build from:
How it Works Under the Hood
The Crypto Accounting Tool goes through the following steps when analyzing your wallet address:
The Global Activity Scan
Your wallet address is scanned for any activity across 200+ blockchains and compiled into a list. These are your digital footprints from various chains like Bitcoin and Ethereum.
The Token List
For every chain, a list of all token balances (in wei) is fetched for your wallet address. This includes native tokens (e.g. ETH, BTC) and every ERC20 tokens (e.g. USDC). These are your digital artifacts collected across Web3.
The Transaction History Fetch
For every chain, every transaction record is gathered and listed, broken out by native and ERC20 token transfers. This includes internal transactions (for the large chains and provided by Etherscan), historical prices and transfer direction details (IN or OUT of the Wallet Address).
The Balance Sheet
To check that all transactions have been correctly captured, the Crypto Accounting Tool does a balance check comparing the net of all IN and OUT transfers for native and ERC20 tokens with your current balance. For native tokens, this means accounting for gas fees paid for ERC20 token transfers.
Current token balance = SUM(IN transfers) - SUM(OUT transfers)
Check this out in the Native Transfers Balance Sheet and Token Transfers Balance Sheet:
The Cost Basis & Capital Gains Calc
Here begins the fun part - figuring out your cost basis & capital gains/losses. This spreadsheet uses adjusted cost base (ACB) but you can adapt it to use a different method (e.g. FIFO, LIFO, HIFO).
In the Native Transfers and Token Transfers sheets, simply filter by the token contract_ticker (e.g. DAI) and the last two columns tally up the ACB and capital gains/losses dynamically.
Note - special thanks to Ha Duong and his crypto tax sheet from 2019 for compiling the incredibly complicated formulas for calculating cost basis and capital gains in Google Sheets!
The Unrealized PnL
Back in the Native Transfers Balance Sheet and Token Transfers Balance Sheet, the ACB used in the previous step can be compared to the current value of each token to determine the current Unrealized PnL:
Check out a more detailed guide on understanding unrealized PnL.
Calculating the Tax
With all the accounting now handled, the last piece of the puzzle is to calculate your tax owing based on your tax jurisdiction. This could be fairly simple or quite complex at this point. Understanding what constitutes a taxable event is crucial. Here are some common scenarios:
Selling crypto for fiat currency (e.g., selling Bitcoin for USD) results in a capital gain or loss, depending on the difference between the selling price and the purchase cost.
Trading one cryptocurrency for another — each trade is considered a sale of the first cryptocurrency and a purchase of the second, potentially triggering a tax event.
Using crypto to purchase goods or services treats the cryptocurrency as being sold for its market value at the time of the transaction, which could result in a capital gain or loss.
Earning crypto from mining, staking, or as income generally counts as taxable income at the fair market value of the cryptocurrency on the day it was received.
Check out A Guide to DeFi Taxes for more details.
Hopefully with the Crypto Accounting Tool as your foundation, calculating your tax is simply a matter of applying your tax rate to your gains/losses. And if not, good luck!