Home › Templates › Crypto airdrop tracker
Crypto Airdrop Tracker Spreadsheet
A free airdrop tracker for Excel and Google Sheets with a US tax layout. Log each airdrop or hard-fork token at the moment you could first sell or move it, with its fair market value then. The sheet works out the ordinary income and cost basis of each lot, links later sales to the lot, splits short-term and long-term gains, totals everything by tax year, and keeps a checklist of unclaimed airdrops and deadlines.
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 wallet connection, no price feed: you type each value from a block explorer or your exchange's records.
Not tax advice. The template follows published IRS guidance as of 5 Nov 2026 and is for record keeping. Tax treatment depends on your facts, and guidance changes. Check the sources below and ask a tax professional before you file.
| Lot | Type | Received | Token (fictional) | Qty | FMV / token | Ordinary income | Qty left | Official source verified |
|---|---|---|---|---|---|---|---|---|
| EX-001 | Hard fork | 2024-03-12 14:05 | FICT-F | 25 | $2.00 | $50.00 | 0 | Yes |
| EX-002 | Airdrop | 2024-09-03 09:30 | FICT-A | 1,200 | $0.45 | $540.00 | 0 | Yes |
| EX-003 | Hard fork | 2025-02-18 18:20 | FICT-B | 300 | $1.80 | $540.00 | 100 | Yes |
| EX-004 | Airdrop | 2025-07-01 00:00 | FICT-C | 5,000 | $0.012 | $60.00 | 0 | Yes |
| EX-005 | Airdrop | 2026-01-15 11:45 | FICT-D | 80 | $6.25 | $500.00 | 50 | Yes |
| EX-006 | Other | 2026-04-02 08:10 | Unknown token (FICT-Z) | 10,000 | $0.00 | $0.00 | 10,000 | No |
Ordinary income (FMV at receipt)Short-term gain / lossLong-term gain / loss
Which sheet do you need? This one is for tokens you received for free: airdrops and hard forks. For every buy, sell and swap across all coins, use the crypto tax spreadsheet or the cost basis spreadsheet. Staking rewards have their own staking rewards tracker.
What's inside the spreadsheet
| Tab | What you type | What it calculates |
|---|---|---|
| Start here | Nothing | Steps, the rules the formulas follow, what the IRS has said, scam-safety notes, limits, disclaimer |
| Airdrops (300 rows) | Type (Airdrop / Hard fork / Other), date and time received, token, chain, wallet, quantity, FMV per token in USD, FMV source, official source verified (Y/N), notes | Lot ID, ordinary income, cost basis per token, tax year, quantity disposed and remaining, basis remaining, a status check |
| Disposals (500 rows) | Date and time, Lot ID (dropdown), quantity, gross proceeds in USD, fees, where, Form 1099-DA received (Yes / No / Not expected) | Token, date received, cost basis, net proceeds, gain or loss, days held, short-term or long-term, tax year, a status check (unknown lot, sale before receipt, over-sold lot) |
| Claims (200 rows) | Project, chain, eligible wallet, snapshot date, claim opens, claim deadline, official link, source verified (Y/N), claimed (Y/N), Lot ID once logged; an as-of date (TODAY() by default) | Days to the deadline and a status: Open, Due within 7 days, Verify the official source first, Deadline passed, Claimed: add it to the log, Claimed and logged |
| Summary | Tax years in column A (2017–2032 preset) | Per year: lots received, ordinary income, disposals, net proceeds, cost basis, short-term and long-term gain or loss, gross proceeds on 1099-DA rows; a year check, a checks panel and a bar chart |
| Example | Nothing | The four blocks filled with fictional data: 6 lots, 7 disposals, 6 claims |
How to use it
- Airdrops: add one row each time tokens arrive. Type the date and time you could first transfer, sell or exchange them. If an exchange credits a fork token days after the fork, use the credit time.
- Look up the US-dollar price per token at that moment on a block explorer or your exchange's records, type it in the FMV column, and note where it came from. If you find no market price, record that in the FMV source column.
- Type Y in Official source verified? only after you checked the claim on the project's own site or docs. The cell stays amber until you do.
- Disposals: when you sell, swap or spend airdropped tokens, pick the Lot ID, then the date, quantity, gross proceeds in USD and fees. A sale that uses two lots needs two rows.
- Claims: list airdrops you may qualify for, with the snapshot date and claim deadline. Mark Claimed Y and add the Lot ID once the tokens are in the log.
- Summary: read the totals for each tax year and fix anything the checks panel counts. Keep the file with your tax records.
Do not sort or delete rows in the Airdrops log. Lot IDs follow the row number and the Disposals tab points at them. To remove a lot, clear its yellow cells.
What the IRS has said about airdrops
| Rule | Source | Where it is in the sheet |
|---|---|---|
| An airdrop of new cryptocurrency after a hard fork gives "gross income, ordinary in character". A hard fork with no new tokens received is not income. | Rev. Rul. 2019-24, holdings 1 and 2 | Airdrops, column L |
| Income is the fair market value when the airdrop is recorded on the ledger, "provided you have dominion and control" so you can "transfer, sell, exchange, or otherwise dispose of" it. | IRS virtual currency FAQ, A24 | Airdrops, columns C and H |
| If an exchange does not credit the new token at once, you receive it when you later get "the ability to transfer, sell, exchange, or otherwise dispose of" it. | Rev. Rul. 2019-24 | Airdrops, column C |
| Basis equals the amount included in income. | IRS FAQ A25 | Airdrops, column M |
| The holding period "begins the day after it is received"; held more than one year = long-term. | IRS FAQ A29, A6 | Disposals, columns O and P |
| On Form 1040, answer Yes to the digital asset question if you received digital assets from "an airdrop as it relates to a hard fork". | IRS, Digital assets | Summary, lots received |
| Brokers report gross proceeds on Form 1099-DA for transactions on or after 1 Jan 2025, and basis on certain transactions on or after 1 Jan 2026. | IRS, Digital assets; About Form 1099-DA | Disposals, column H; Summary, column J |
| From 1 Jan 2025, unused basis is allocated to the digital assets in each wallet or account. | Rev. Proc. 2024-28 | Airdrops, column F (wallet) |
What is not settled. Rev. Rul. 2019-24 covers an airdrop that follows a hard fork. The IRS has not issued a separate ruling for airdrops with no fork, such as project token launches. The template records them the same way so you have the numbers in one place; how they are taxed is a question for a tax professional. The IRS FAQ also says it "generally appl[ies] to transactions involving digital assets completed before Jan. 1, 2025", so check the IRS digital assets page for later updates.
The formulas, explained
Ordinary income = quantity × FMV per token at receipt. Each lot's basis per token is that same FMV. A disposal's cost basis = quantity sold × the lot's basis per token; gain or loss = proceeds − fees − cost basis. A disposal is long-term when its date is later than the same calendar date one year after receipt, which matches "held more than one year" with the holding period starting the day after receipt. Dates compare by calendar day; the time of day only documents when you received the tokens. Exact formulas from row 6 of each tab:
| Tab | Field | Formula |
|---|---|---|
| Airdrops | Ordinary income (L6) | =IF($S6=1,G6*H6,"") |
| Airdrops | Cost basis per token (M6) | =IF($S6=1,H6,"") |
| Airdrops | Tax year received (N6) | =IF($S6=1,YEAR(C6),"") |
| Airdrops | Quantity disposed (O6) | =IF($S6=1,SUMIFS(Disposals!$D$6:$D$505,Disposals!$C$6:$C$505,A6,Disposals!$S$6:$S$505,1),"") |
| Airdrops | Quantity remaining (P6) | =IF($S6=1,G6-O6,"") |
| Disposals | Lot row, helper (T6) | =IF(OR(A6="",C6=""),"",IFERROR(MATCH(C6,Airdrops!$A$6:$A$305,0),"")) |
| Disposals | Cost basis (L6) | =IF($S6=1,D6*K6,"") |
| Disposals | Net proceeds (M6) | =IF($S6=1,E6-N(F6),"") |
| Disposals | Gain or loss (N6) | =IF($S6=1,M6-L6,"") |
| Disposals | Days held (O6) | =IF($S6=1,INT(B6)-INT(J6),"") |
| Disposals | Term (P6) | =IF($S6=1,IF(INT(B6)>EDATE(INT(J6),12),"Long-term","Short-term"),"") |
| Claims | Days to deadline (L6) | =IF(OR(COUNTA(A6:K6)=0,I6="Y",NOT(ISNUMBER(F6)),NOT(ISNUMBER(Claims!$B$3))),"",INT(F6)-INT(Claims!$B$3)) |
| Summary | Ordinary income in a year (C5) | =IF(ISNUMBER($A5),SUMIFS(Airdrops!$L$6:$L$305,Airdrops!$N$6:$N$305,$A5,Airdrops!$S$6:$S$305,1),"") |
| Summary | Short-term gain or loss in a year (G5) | =IF(ISNUMBER($A5),SUMIFS(Disposals!$N$6:$N$505,Disposals!$Q$6:$Q$505,$A5,Disposals!$P$6:$P$505,"Short-term",Disposals!$S$6:$S$505,1),"") |
Column S on each log is a 1/0 helper that marks a complete, valid row. Only valid rows feed the totals, so a half-typed row never changes the Summary. The status column says what to fix: a missing date or token, a quantity of 0, a lot ID that does not exist, a sale dated before the lot was received, or disposals that add up to more than the lot.
Worked example from the sample data
All names and numbers below are fictional.
- Hard fork, lot EX-001. 25 FICT-F arrive on 2024-03-12 14:05 and can be sold at once. At $2.00 each, income = 25 × $2.00 = $50.00, and the basis is $2.00 a token. These are the same numbers as Situation 2 of Rev. Rul. 2019-24 (25 units worth $50 in total, basis $50).
- More than one year: long-term. EXD-002 sells 15 of that lot on 2025-03-13, 366 days after receipt. Net proceeds $59.00 − basis $30.00 = $29.00, long-term.
- Exactly one year: still short-term. Lot EX-003 was credited on 2025-02-18. EXD-005 sells 100 on 2026-02-18, 365 days later: net $258.50 − basis $180.00 = $78.50, short-term. A day later it would have been long-term.
- 2025 in total: 2 lots received, ordinary income $600.00; 3 disposals with a short-term result of −$328.00 and a long-term result of $29.00. Gross proceeds on rows marked Form 1099-DA = Yes: $455.00, which you can compare with the forms you receive.
- An unknown token. Lot EX-006 appeared unasked. It is logged with no market price found and the official-source column set to N, so it stays amber and is counted in the checks panel. The tokens were not touched.
| Disposal | Date | Lot | Qty | Net proceeds | Cost basis | Gain / loss | Days held | Term | Form 1099-DA |
|---|---|---|---|---|---|---|---|---|---|
| EXD-001 | 2024-11-20 | EX-001 (FICT-F) | 10 | $30.50 | $20.00 | $10.50 | 253 | Short-term | Not expected |
| EXD-002 | 2025-03-13 | EX-001 (FICT-F) | 15 | $59.00 | $30.00 | $29.00 | 366 | Long-term | Yes |
| EXD-003 | 2025-01-10 | EX-002 (FICT-A) | 1,200 | $298.00 | $540.00 | −$242.00 | 129 | Short-term | Yes |
| EXD-004 | 2025-12-01 | EX-003 (FICT-B) | 100 | $94.00 | $180.00 | −$86.00 | 286 | Short-term | Yes |
| EXD-005 | 2026-02-18 | EX-003 (FICT-B) | 100 | $258.50 | $180.00 | $78.50 | 365 | Short-term | Yes |
| EXD-006 | 2026-03-05 | EX-004 (FICT-C) | 5,000 | $34.50 | $60.00 | −$25.50 | 247 | Short-term | Not expected |
| EXD-007 | 2026-09-30 | EX-005 (FICT-D) | 30 | $239.00 | $187.50 | $51.50 | 258 | Short-term | Yes |
| Tax year | Lots received | Ordinary income | Disposals | Short-term | Long-term | Total gain / loss | Gross proceeds, 1099-DA rows |
|---|---|---|---|---|---|---|---|
| 2024 | 2 | $590.00 | 1 | $10.50 | $0.00 | $10.50 | $0.00 |
| 2025 | 2 | $600.00 | 3 | −$328.00 | $29.00 | −$299.00 | $455.00 |
| 2026 | 2 | $500.00 | 3 | $104.50 | $0.00 | $104.50 | $500.00 |
| Total | 6 | $1,690.00 | 7 | −$213.00 | $29.00 | −$184.00 | $955.00 |
Claims checklist and scam safety
The Claims tab tracks airdrops before they reach your wallet. Each row shows the days left to the deadline from the as-of date (today, unless you type a date) and one status. A source you have not verified is flagged before the countdown; only a completed claim or a passed deadline outranks it. The example, as of 5 Nov 2026:
| Project (fictional) | Snapshot | Deadline | Source verified | Claimed | Days left | Status |
|---|---|---|---|---|---|---|
| FICT-B fork | 2025-02-02 | 2025-05-18 | Yes | Yes | — | Claimed and logged |
| FICT-D | 2025-12-01 | 2026-04-15 | Yes | Yes | — | Claimed and logged |
| FICT-E | 2026-09-01 | 2026-11-10 | Yes | No | 5 | Due within 7 days |
| FICT-G | 2026-10-01 | 2027-01-31 | No | No | 87 | Verify the official source first |
| FICT-H | 2026-05-01 | 2026-08-31 | Yes | No | -66 | Deadline passed |
| FICT-J | 2026-10-15 | — | Yes | No | — | Open, no deadline entered |
Airdrops are a common lure because they promise something for free. Regulators' general warnings apply:
- The FTC: "Know that only scammers guarantee big payouts or fast, easy money" (FTC, Spotting the FTC's most reported crypto scams, 3 Jun 2022). Report fraud at ReportFraud.ftc.gov.
- The CFTC tells the public to "be cautious of calls, text messages, letters, or emails from people or companies they've never contacted" and warns that victims of one fraud are often targeted again by fraud-recovery scams (CFTC, Release 8936-24, 30 Jul 2024).
- A joint SEC and CFTC investor alert lists "claims of high guaranteed returns and promises that the investments carry little or even no risk" as red flags (CFTC, Watch Out for Fraudulent Digital Asset and "Crypto" Trading Websites).
Practical habits the checklist builds in: find the claim link on the project's own site or docs, never through a direct message, a reply or the name of a token that appeared in your wallet; never type a seed phrase into a claim page; read what a wallet approval allows before signing it.
Common mistakes
- Using the snapshot date as the receipt date. Income is measured when you can dispose of the tokens, not when your balance was recorded for eligibility.
- Leaving basis at zero. If you reported the airdrop as income, the same value is your basis. A zero basis would tax it again when you sell.
- Typing the total value in the per-token column. FMV is per token; the sheet multiplies by quantity.
- Counting exactly one year as long-term. It must be more than one year.
- Relying on Form 1099-DA alone. Tokens moved into an exchange from your own wallet can be reported without basis.
- Mixing bought and airdropped units. If you also bought the same token, track all lots in one cost basis spreadsheet.
Limits
- US federal framing only. No state tax, wash-sale, business, self-employment or staking rules.
- Specific identification by Lot ID; no automatic FIFO. USD only. You type every FMV; nothing is fetched.
- Airdropped and forked tokens only, up to 300 lots, 500 disposals and 200 claims (copy rows down for more, keeping the formulas).
- Long-term uses the same calendar date one year later (EDATE). For a 29 February receipt that date is 28 February.
- 10,122 formulas, recalculated and checked in LibreOffice; functions chosen to exist in Excel 2016+ and Google Sheets.
FAQ
Are crypto airdrops taxable in the US?
For an airdrop that follows a hard fork, yes. Rev. Rul. 2019-24 holds that a taxpayer who receives new cryptocurrency in that situation has "gross income, ordinary in character", equal to its fair market value when received. A hard fork with no new tokens received is not income. The IRS has not published a separate ruling for other airdrops; this template records them the same way so none are missed. Ask a tax professional how yours should be treated.
How do I find the fair market value of an airdrop?
Use the US-dollar value at the date and time you could first sell or move the tokens. The IRS FAQ says that for a transaction on an exchange the value is the amount the exchange recorded, and that it "will accept as evidence of fair market value the value as determined by a cryptocurrency or blockchain explorer" at the exact date and time (A26, A27). Note the source in the FMV source column. The sheet does not fetch prices.
What is the cost basis of airdropped crypto, and when does the holding period start?
Basis is the amount you included in income, which is the fair market value when received (IRS FAQ A25). The holding period "begins the day after it is received" (A29), and tokens held more than one year give a long-term gain or loss (A6). In the example, a lot received on 12 Mar 2024 and sold on 13 Mar 2025 is long-term; one received on 18 Feb 2025 and sold on 18 Feb 2026 is still short-term.
Will I get a Form 1099-DA for airdropped tokens?
Possibly, when you sell through a broker. The IRS says brokers report gross proceeds for transactions on or after 1 Jan 2025 and basis on certain transactions on or after 1 Jan 2026. The 2026 Form 1099-DA instructions treat a digital asset transferred in to the broker, or acquired before 2026, as a noncovered security, so tokens claimed in your own wallet may show proceeds without basis. Your log supplies the basis.
What should I do with unknown tokens that appear in my wallet?
Do not visit sites named in the token, connect your wallet or sign approvals to claim them. Get claim links only from a project's own site or documentation. The CFTC tells the public to "be cautious of calls, text messages, letters, or emails from people or companies they've never contacted". Log the token with the official-source column set to N so it is on record, and ask a tax professional how to treat it.
Does this airdrop tracker work in Google Sheets?
Yes. In Google Sheets choose File → Import → Upload and pick the .xlsx, or upload it to Google Drive and open it with Google Sheets. The file uses only AND, COUNTA, COUNTIF, COUNTIFS, EDATE, IF, IFERROR, INDEX, INT, ISNUMBER, MATCH, N, NOT, OR, ROW, SUM, SUMIFS, SUMPRODUCT, TEXT, TODAY, YEAR, which exist in Excel 2016+, Google Sheets and LibreOffice. No macros, no add-in, no wallet connection.
Related templates and tools
- Crypto tax spreadsheetEvery trade, gains by year
- Staking rewards trackerRewards at value when received
- Cost basis spreadsheetLots, FIFO and average cost
- Crypto portfolio trackerHoldings, allocation, P/L
- Crypto trading journalTrades, reasons and results
- Capital gains tax calculatorEstimate tax on a gain
- All templatesFree spreadsheets
Educational template only, not tax, legal or investment advice. Example data is fictional: tokens FICT-A to FICT-Z, chains, wallets, prices and dates are made up to show the formulas. IRS rules are quoted from Rev. Rul. 2019-24, the IRS virtual currency FAQ (page reviewed 30 Jun 2026), the IRS digital assets page (reviewed 2 Sep 2026), the Form 1099-DA page and 2026 instructions, and Rev. Proc. 2024-28; scam guidance from the FTC and CFTC; all retrieved on 5 November 2026. Check irs.gov for later changes.