Home › Templates › Crypto tax spreadsheet
Crypto Tax Spreadsheet
A free crypto tax spreadsheet that logs buys, sells, swaps, income and fees, keeps each purchase as a cost basis lot, matches every sale to lots first in, first out with plain formulas, and totals short-term and long-term gains for each tax year.
Use it in Google Sheets: choose File → Import → Upload in Sheets and pick the .xlsx, or upload it to Google Drive and open it with Google Sheets. No sign-up, no API key, no add-on. The formulas are SUMIFS, SUMPRODUCT, COUNTIFS and EDATE.
Not tax advice. This is a record-keeping template for learning. It follows the US default FIFO method and does not apply UK pooling rules. Tax rules change and depend on your situation, so check your figures with a tax professional before you file.
| Tax year | Disposals | Proceeds | Cost basis | Gain / loss | Short-term | Long-term | Income (FMV) |
|---|---|---|---|---|---|---|---|
| 2024 | 1 | $14,985.00 | $8,008.00 | $6,977.00 | $6,977.00 | $0.00 | $500.00 |
| 2025 | 4 | $57,753.50 | $39,064.00 | $18,689.50 | $3,755.00 | $14,934.50 | $405.00 |
| 2026 | 1 | $20,979.00 | $25,025.00 | −$4,046.00 | −$5,033.00 | $987.00 | $0.00 |
| Total | 6 | $93,717.50 | $72,097.00 | $21,620.50 | $5,699.00 | $15,921.50 | $905.00 |
What's inside the spreadsheet
| Tab | What you type | What it calculates |
|---|---|---|
| Start here | Nothing | Steps, transaction types, US notes, the UK warning, limits, disclaimer |
| Transactions | Date, type (dropdown: Buy, Sell, Swap out, Swap in, Income, Fee), asset, wallet or exchange, quantity, US dollar price per unit, fee, notes. 1,000 rows. | Lot cost basis and cost per unit, FIFO cost basis of each disposal, gain or loss, the long-term and short-term part, tax year, income at fair market value, and a row check |
| Summary | Tax years (up to 8), assets you still hold and their price now | Per tax year: disposals, proceeds, cost basis, gain or loss, short-term, long-term, crypto income. Open lots left after FIFO with remaining basis and unrealized gain. A chart and two checks |
| Example / Example Summary | Already filled | 13 fictional transactions: buys, two staking rewards, a swap, a network fee and four sales |
How to use it
- Export the transaction history from each exchange and wallet you used.
- On Transactions, add one row per event, oldest first. On the same day, put buys before sells.
- Type the quantity as a positive number. Type the US dollar fair market value of one unit at that moment, and the dollar fee for that row.
- Enter a crypto-to-crypto trade as two rows: Swap out for the coin you gave up and Swap in for the coin you received.
- Enter staking, mining, airdrops or crypto pay as Income. The sheet opens a new lot at that value and totals it as income.
- Do not enter transfers between your own wallets. They are not sales (IRS FAQ Q38).
- Read the Check column. Every filled row should say OK. Then type your tax years on Summary and confirm both checks show 0.
How the FIFO lot matching works with plain formulas
Most spreadsheets need a macro or a manual lot table for FIFO. This one uses a cumulative quantity method instead:
- Each acquisition is a lot. Column L stores how many units of that asset were acquired before the lot. So a lot of 0.3 units with L = 0.5 covers units 0.5 to 0.8 of that asset's history.
- Each disposal stores how many units of that asset were disposed of before it (column O). FIFO means a sale of M units takes units O to O + M.
- The FIFO cost basis is the sum, over all lots of that asset, of cost per unit × the overlap between the lot's range and the sale's range. SUMPRODUCT computes the overlap for every lot at once.
- The long-term part uses the same overlap, but only for lots acquired before
EDATE(sale date,-12), which means held more than one year.
| COIN-A lot | Units | Cost per unit | Covers units |
|---|---|---|---|
| 15 Jan 2024 buy | 0.5 | $40,040.00 | 0 – 0.5 |
| 10 Mar 2024 buy | 0.3 | $60,060.00 | 0.5 – 0.8 |
| 1 Feb 2025 buy | 0.4 | $95,095.00 | 0.8 – 1.2 |
| 20 May 2025 swap in | 0.048 | $100,000.00 | 1.2 – 1.248 |
Cost per unit includes the buy fee: ($40,000 × 0.5 + $20) ÷ 0.5 = $40,040.00. The IRS says basis includes "fees, commissions and other acquisition costs" (FAQ Q8).
The formulas, explained
Exact formulas from the Transactions tab, row 5. Inputs: A = date, B = type, C = asset, E = quantity, F = price, G = fee. Rows 5–1004.
| Field | Formula |
|---|---|
| Acquired qty (I) | =IF(OR($B5="Buy",$B5="Swap in",$B5="Income"),N($E5),0) |
| Lot cost basis (J) and cost per unit (K) | =IF($I5>0,$I5*N($F5)+N($G5),0) and =IF($I5>0,$J5/$I5,0) |
| Units acquired before this lot (L) | =IF($I5>0,SUMIFS($I$5:$I5,$C$5:$C5,$C5)-$I5,0) |
| Disposed qty (M) and proceeds after fee (N) | =IF(OR($B5="Sell",$B5="Swap out",$B5="Fee"),N($E5),0) and =IF($M5>0,$M5*N($F5)-N($G5),0) |
| Units disposed before this row (O) | =IF($M5>0,SUMIFS($M$5:$M5,$C$5:$C5,$C5)-$M5,0) |
| FIFO cost basis (P) | =IF($M5>0,SUMPRODUCT(($C$5:$C$1004=$C5)*$K$5:$K$1004*(((($O5+$M5-$L$5:$L$1004)>=$I$5:$I$1004)*$I$5:$I$1004+(($O5+$M5-$L$5:$L$1004)>0)*(($O5+$M5-$L$5:$L$1004)<$I$5:$I$1004)*($O5+$M5-$L$5:$L$1004))-((($O5-$L$5:$L$1004)>=$I$5:$I$1004)*$I$5:$I$1004+(($O5-$L$5:$L$1004)>0)*(($O5-$L$5:$L$1004)<$I$5:$I$1004)*($O5-$L$5:$L$1004)))),0) |
| Long-term qty (R) | Same as P without the cost per unit, times ($A$5:$A$1004<EDATE($A5,-12)) |
| Long-term gain (T), short-term gain (U) | =IF($R5>0,$N5*$R5/$M5-$S5,0) and =IF($M5>0,$Q5-$T5,0) |
| Summary: short-term gain for a year | =IF($A6="","",SUMIFS(Transactions!$U$5:$U$1004,Transactions!$V$5:$V$1004,$A6)) |
The overlap of a lot with the range 0 to x is MIN(MAX(x − L, 0), lot qty). Inside SUMPRODUCT it is written as (x−L>=I)*I + (x−L>0)*(x−L<I)*(x−L), so it needs no array entry and works in Excel 2016+ and Google Sheets. The cost of a sale is the overlap up to O + M minus the overlap up to O.
Worked example from the sample data
| Date | Type | Asset | Qty | Price | Fee | Cost basis | Gain / loss | Long / short |
|---|---|---|---|---|---|---|---|---|
| 15 Jan 2024 | Buy | COIN-A | 0.5 | $40,000.00 | $20.00 | $20,020.00 | – | – |
| 10 Mar 2024 | Buy | COIN-A | 0.3 | $60,000.00 | $18.00 | $18,018.00 | – | – |
| 01 Jun 2024 | Buy | COIN-B | 5 | $3,000.00 | $15.00 | $15,015.00 | – | – |
| 20 Sep 2024 | Income | COIN-B | 0.2 | $2,500.00 | $0.00 | $500.00 | – | – |
| 05 Nov 2024 | Sell | COIN-A | 0.2 | $75,000.00 | $15.00 | $8,008.00 | $6,977.00 | $0.00 / $6,977.00 |
| 01 Feb 2025 | Buy | COIN-A | 0.4 | $95,000.00 | $38.00 | $38,038.00 | – | – |
| 05 Mar 2025 | Sell | COIN-A | 0.5 | $85,000.00 | $42.50 | $24,024.00 | $18,433.50 | $13,462.50 / $4,971.00 |
| 20 May 2025 | Swap out | COIN-B | 2 | $2,400.00 | $10.00 | $6,006.00 | −$1,216.00 | $0.00 / −$1,216.00 |
| 20 May 2025 | Swap in | COIN-A | 0.048 | $100,000.00 | $0.00 | $4,800.00 | – | – |
| 01 Jul 2025 | Fee | COIN-B | 0.01 | $2,600.00 | $0.00 | $30.03 | −$4.03 | −$4.03 / $0.00 |
| 15 Aug 2025 | Income | COIN-B | 0.15 | $2,700.00 | $0.00 | $405.00 | – | – |
| 10 Dec 2025 | Sell | COIN-B | 3 | $3,500.00 | $20.00 | $9,003.97 | $1,476.03 | $1,476.03 / $0.00 |
| 20 Jan 2026 | Sell | COIN-A | 0.3 | $70,000.00 | $21.00 | $25,025.00 | −$4,046.00 | $987.00 / −$5,033.00 |
A sale that uses two lots: 5 Mar 2025, 0.5 COIN-A
Proceeds: 0.5 × $85,000 − $42.50 fee = $42,457.50. Earlier sales used 0.2 units, so this sale takes units 0.2 to 0.7. That is 0.3 units of the 15 Jan 2024 lot ($12,012.00) and 0.2 units of the 10 Mar 2024 lot ($12,012.00). FIFO cost basis $24,024.00, gain $18,433.50.
The cut-off date is 5 Mar 2024. The January lot was held more than one year, so its share is long-term: $42,457.50 × 0.3 ÷ 0.5 − $12,012.00 = $13,462.50. The March lot was not, so $4,971.00 is short-term.
A loss split the same way: 20 Jan 2026, 0.3 COIN-A
Proceeds $20,979.00. Units 0.7 to 1.0 come from the rest of the 10 Mar 2024 lot (0.1 units, long-term) and the 1 Feb 2025 lot (0.2 units, short-term). Cost basis $25,025.00, a loss of $4,046.00: a $987.00 long-term gain and a $5,033.00 short-term loss.
Swap, fee and income rows
The 20 May 2025 swap gave up 2 COIN-B worth $4,800 (fee $10), a short-term loss of $1,216.00, and opened a COIN-A lot with a $4,800 basis. The network fee paid in 0.01 COIN-B is treated as a small disposal, a $4.03 loss. Staking rewards of $500.00 (2024) and $405.00 (2025) appear as income and start lots at that value.
By tax year and what is left
2024: gain $6,977.00, all short-term. 2025: $18,689.50, of which $3,755.00 short-term and $14,934.50 long-term. 2026: −$4,046.00. After FIFO, 0.248 COIN-A remain with a basis of $23,819.00, and 0.34 COIN-B with $880.00.
US rules the sheet follows
- Crypto is property. IRS Notice 2014-21: "For federal tax purposes, virtual currency is treated as property." Sales, swaps and spending can create a gain or loss.
- FIFO by default. The IRS virtual currency FAQ (Q41): if you do not identify specific units, they are disposed of "in chronological order beginning with the earliest unit" you acquired, "that is, on a first in, first out (FIFO) basis." The IRS notes these FAQs generally apply to transactions completed before Jan. 1, 2025.
- One year. Held "more than one year" = long-term; one year or less = short-term (FAQ Q6).
- Where it goes. Sales go on Form 8949 and Schedule D (FAQ Q43). The 2025 Form 8949 instructions add boxes G, H and I for short-term and J, K and L for long-term digital asset transactions.
- Form 1099-DA. Under the final broker regulations, brokers "must report gross proceeds for transactions effected on or after January 1, 2025" and "must report basis on certain transactions effected on or after January 1, 2026." See the Form 1099-DA instructions.
- Wallet by wallet from 2025. The same IRS page says Revenue Procedure 2024-28 sets how to allocate basis "to digital assets held within each wallet or account" as of January 1, 2025. To keep a separate FIFO line per wallet, give the asset a wallet suffix, for example
COIN-A@Exchange1.
UK users: this sheet does not apply HMRC pooling
HMRC does not use FIFO lots. Its Cryptoassets Manual, CRYPTO22200 sets three matching steps for each token type:
- Same day: tokens bought and sold on the same day are matched first.
- 30 days: tokens bought in the 30 days after a sale are matched to that sale.
- Section 104 pool: everything else sits in one pool per token at a "pooled allowable cost", that is, average cost.
The FIFO numbers in this template will usually differ from that result. Use it as a transaction log only, and work out UK gains with HMRC's guidance or a UK tax adviser.
Common mistakes
- Rows out of date order. FIFO depends on order. The Check column flags a date earlier than the row above.
- Forgetting swaps. A coin-to-coin trade is a disposal in the US, not just a balance change.
- Logging wallet transfers as sales. Moving coins between your own wallets is not taxable; leave those rows out.
- Leaving out fees. Buy fees raise the cost basis; sell fees lower the proceeds. Both reduce the gain.
- Missing income lots. Staking or airdropped coins have a basis equal to the value when you received them. Without that lot, a later sale shows too much gain.
- Two spellings of one coin. "COIN-A" and "coin a" are separate pools to SUMIFS.
- Using it for a UK return. See the section above.
FAQ
How do I calculate crypto taxes in a spreadsheet?
Log every buy, sell, swap, income and fee in date order with the US dollar value at the time. Each acquisition is a lot with a cost basis (cost plus fees). Each disposal is matched to lots, FIFO by default in the US, and gain = proceeds after fees minus the matched cost basis. Then split each gain into short-term (held one year or less) and long-term (more than one year) and total by tax year. The free template on this page does the matching with formulas.
What cost basis method does the IRS use for crypto if I don't choose one?
FIFO. The IRS virtual currency FAQ (Q41) says that if you do not identify specific units, they are treated as disposed of in chronological order, starting with the earliest units you acquired. Specific identification is allowed if you can identify the units and substantiate their basis (Q39-Q40).
Is swapping one crypto for another a taxable event?
In the US, yes. The IRS FAQ (Q16) says exchanging virtual currency held as a capital asset for other property, including another virtual currency, creates a capital gain or loss. Enter a swap as two rows: Swap out at the fair market value of what you gave up, and Swap in for the coin you received.
How do I know if a crypto gain is short-term or long-term?
Count from the day after you acquired the units. Held one year or less is short-term; held more than one year is long-term (IRS FAQ Q6). One sale can be both if FIFO takes units from an old lot and a newer one; the template splits such sales by lot.
Do I still need my own records if I get a Form 1099-DA?
Yes. Brokers report gross proceeds on Form 1099-DA for sales on or after January 1, 2025, and basis only for certain sales on or after January 1, 2026, mainly assets acquired in that account after 2025. Coins bought earlier, moved between wallets, or received as income still need your own cost basis records.
Can I use this crypto tax spreadsheet in the UK?
Not for the gain figures. HMRC matches disposals with same-day acquisitions, then acquisitions in the next 30 days, then a section 104 pool at average cost. This template uses FIFO lots, which is a different method, so UK users should follow HMRC's Cryptoassets Manual or ask a UK tax adviser.
Related tools and templates
- Capital gains tax calculatorEstimate tax on a gain
- Crypto portfolio trackerHoldings, value and P/L
- Crypto profit calculatorP/L on a single trade
- DCA trackerRecurring buys and average cost
- Stock portfolio trackerTrades, cost basis, allocation
- All templatesFree spreadsheets
Educational template only, not tax, legal, financial or investment advice. It does not file anything and may not match your broker's forms. Check with a tax professional. Example data is fictional: COIN-A, COIN-B and all prices, fees and dates are made up. IRS and HMRC wording was retrieved on 27 October 2026.