Home › Templates › Crypto staking rewards tracker
Crypto Staking Rewards Tracker
A free staking rewards tracker for Excel and Google Sheets: list your staking positions, log every reward with its US dollar value when you received it, and the sheet totals reward income by asset, month and tax year, compares the rewards that arrived with what the stated APR or APY promised, works out your effective APR, and shows the gain or loss on the reward coins you still hold.
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: you type the rewards and prices yourself.
Not tax advice. This is a record-keeping template. The tax notes below summarise US IRS guidance with links; rules differ by country and change over time, so check your own figures with a tax professional.
| Asset | Rewards | Reward qty | Income at receipt | Basis held | Price now | Value held | Unrealised | Actual / expected | Effective APR |
|---|---|---|---|---|---|---|---|---|---|
| ETH | 31 | 0.479303 | $1,555.43 | $1,198.27 | $3,100.00 | $1,170.79 | −$27.48 | 96.4% | 3.03% |
| SOL | 21 | 35.7162 | $5,944.22 | $5,944.22 | $150.00 | $5,357.43 | −$586.79 | 97.9% | 6.82% |
| ADA | 19 | 509.49 | $306.70 | $306.70 | $0.55 | $280.22 | −$26.48 | 90.0% | 2.54% |
| Total | 71 | $7,806.35 | $7,449.19 | $6,808.44 | −$640.75 |
What's inside the spreadsheet
| Tab | What you type | What it calculates |
|---|---|---|
| Start here | Nothing | Steps, the maths, US tax notes with sources, limits, disclaimer |
| Positions | Up to 20 positions: asset, platform or validator, amount staked, start date, stated rate, APR or APY, lock-up / unbonding days, auto-compound Y/N, end date if you unstaked; an as-of date (optional) | Days staked, APR and APY, expected reward quantity, actual rewards from the log, actual ÷ expected, effective rate, the date the coins would be liquid, a row check |
| Rewards | 1,000 rows: date received, asset, quantity, US dollar price at receipt, platform, tx id or note, Held or Sold | USD income at receipt (the lot's basis), tax year, month, running reward quantity and basis per asset, current value and unrealised gain or loss of each held lot, a row check |
| Summary | Up to 15 assets with today's price; the tax years you want; an optional first month | By asset: income, basis and value of held reward lots, unrealised gain or loss, expected vs actual rewards, effective APR. By tax year and asset. By month for 24 months. Two charts |
| Example Positions, Example Rewards, Example Summary | Already filled | Four fictional positions and 71 fictional reward rows, so you can see every formula working |
The other BeCoin crypto templates do different jobs. The crypto portfolio tracker follows every buy, sell and transfer and your average cost. The crypto tax spreadsheet matches sales to lots first in, first out. This tracker stays on staking: what each position was supposed to pay, what actually arrived, and what that income was worth on the day you got it.
How to use it
- Open Summary. Type each asset you stake once in A7:A21 (for example ETH, SOL, ADA), today's price per unit in column B and the price date in B3.
- Open Positions. Add one row per position. Copy the stated rate from your platform and pick APR or APY to match how it is quoted. Type Y in Auto-compound if rewards are added to your stake. Leave the end date blank while you are still staking.
- Open Rewards. Export the reward history from your exchange, wallet or a block explorer and add one row per reward. Type the date you could first sell or move it, the quantity, and the US dollar price of one unit at that time. Spell the platform exactly as on Positions so the reward counts toward that position.
- When you sell reward coins, change column G to Sold. They stay in income but leave the current value block.
- Check the Check columns (Positions V, Rewards P). Summary K3 shows how many rows need a fix.
- Type your tax years in Summary A26:A33. J34 tells you if any reward falls outside the years listed.
Leave Positions!B3 blank to measure up to today. Type a date there to freeze the numbers, for example 31 December for a year-end review.
Staking rewards and tax: what the IRS says
The US rule for staking rewards is set out in Revenue Ruling 2023-14 (issued 31 July 2023). Its holding: "If a cash-method taxpayer stakes cryptocurrency native to a proof-of-stake blockchain and receives additional units of cryptocurrency as rewards when validation occurs, the fair market value of the validation rewards received is included in the taxpayer's gross income in the taxable year in which the taxpayer gains dominion and control over the validation rewards." The fair market value is "determined as of the date and time the taxpayer gains dominion and control", and the ruling says the same applies when staking through an exchange.
In the ruling's own example, the taxpayer gains dominion and control through the "ability, as of this date, to sell, exchange, or otherwise dispose of" the reward units. That is why the Rewards tab asks for the date you could first sell or move each reward, and the price at that moment.
| What the IRS says | Where the sheet uses it |
|---|---|
| Income = fair market value of the rewards when you gain dominion and control (Rev. Rul. 2023-14) | Rewards column H = quantity × USD price at receipt; Summary totals it by tax year |
| Answer "Yes" to the digital asset question on Form 1040 if you received digital assets for "Mining, staking and similar activities" (IRS digital assets page) | Any reward row in a year means that year's answer is Yes |
| "To report income from forks, staking, mining, etc., use Form 1040 (Schedule 1)" (same page) | Summary year table: the US dollar total for each year |
| Property received as income: "The amount you include in income becomes your basis" (Publication 551, for property received for services; FAQ A13 and A21 give the same fair-market-value basis) | Each reward row is its own lot with basis = its income value; Summary shows basis, value and unrealised gain or loss of held lots |
| Selling or exchanging virtual currency gives a capital gain or loss, reported on Form 8949 (IRS virtual currency FAQs, A4 and A43) | Not computed here: mark sold lots and use the crypto tax spreadsheet for FIFO gains |
Outside the US the rules differ. Countries classify staking rewards in different ways and may time the income differently. Rev. Rul. 2023-14 also notes it does not address issues under rules it does not cite. Treat the sheet as a clean record of dates, quantities and values, then apply your own country's rules or ask a tax professional.
Expected vs actual rewards, and effective APR
A stated APR is an estimate. Validator commission, missed rewards, rate changes and reward delays all change what arrives. The Positions tab measures the gap.
- Expected reward, no auto-compound: amount × APR × days ÷ 365. With auto-compound: amount × ((1 + APY)days/365 − 1).
- Actual reward: the sum of logged rewards with the same asset and platform, dated from the start date to the end date (or the as-of date).
- Effective rate: actual ÷ amount × 365 ÷ days for simple positions (compare with the APR), or (1 + actual ÷ amount)365/days − 1 for auto-compounding positions (compare with the APY).
- APR and APY are converted assuming daily compounding: APY = (1 + APR/365)365 − 1. For APR and APY in other settings, see the APR calculator.
| Asset | Platform (fictional) | Staked | Start | Stated | Compound | Days | Expected | Actual | Actual / exp. | Compare with | Effective | Liquid by |
|---|---|---|---|---|---|---|---|---|---|---|---|---|
| ETH | Example validator A | 8 | 15 Jan 2025 | 3.2% APR | N | 654 | 0.458696 | 0.440397 | 96.0% | 3.20% | 3.07% | 10 Nov 2026 |
| ETH | Example exchange D | 2 | 1 Jun 2025 | 2.6% APR | N | 272 | 0.038751 | 0.038906 | 100.4% | 2.60% | 2.61% | 7 Mar 2026 |
| SOL | Example validator B | 300 | 1 Feb 2025 | 6.8% APY | Y | 637 | 36.4991 | 35.7162 | 97.9% | 6.80% | 6.66% | 3 Nov 2026 |
| ADA | Example stake pool C | 12,000 | 1 Mar 2025 | 2.8% APY | Y | 609 | 565.84 | 509.49 | 90.0% | 2.80% | 2.52% | 31 Oct 2026 |
The formulas, explained
Exact formulas from the file (row 7 on Positions and Summary, row 6 on Rewards). On Positions, A is the asset, B the platform, C the amount, D the start date, E the rate type, F the stated rate, G the unbonding days, H auto-compound and I the end date. The Rewards log reads rows 6 to 1005.
| Field | Formula |
|---|---|
| Positions: date used (B4) | =IF(B3="",TODAY(),B3) |
| Positions: end used (K) | =IF(OR(A7="",D7=""),"",IF(I7="",$B$4,MIN(I7,$B$4))) |
| Positions: days staked (L) | =IF(K7="","",MAX(0,K7-D7)) |
| Positions: APR (M) | =IF(OR(A7="",F7=""),"",IF(E7="APY",365*((1+F7)^(1/365)-1),F7)) |
| Positions: APY (N) | =IF(M7="","",IF(E7="APY",F7,(1+F7/365)^365-1)) |
| Positions: expected reward qty (O) | =IF(OR(L7="",M7="",N(C7)<=0),"",IF(H7="Y",C7*((1+N7)^(L7/365)-1),C7*M7*L7/365)) |
| Positions: actual reward qty (P) | =IF(K7="","",SUMIFS(Rewards!$C$6:$C$1005,Rewards!$B$6:$B$1005,A7,Rewards!$E$6:$E$1005,B7,Rewards!$A$6:$A$1005,">="&D7,Rewards!$A$6:$A$1005,"<="&K7)) |
| Positions: actual / expected (Q) | =IF(OR(P7="",N(O7)=0),"",P7/O7) |
| Positions: effective rate (S) | =IF(OR(P7="",N(L7)=0,N(C7)<=0),"",IF(H7="Y",(1+P7/C7)^(365/L7)-1,P7/C7*365/L7)) |
| Positions: liquid by (U) | =IF(OR(A7="",D7=""),"",IF(I7="",$B$4,I7)+N(G7)) |
| Rewards: USD income at receipt = lot basis (H) | =IF(OR(C6="",D6=""),"",C6*D6) |
| Rewards: tax year (I) | =IF(A6="","",YEAR(A6)) |
| Rewards: running reward basis for the asset (L) | =IF(H6="","",SUMIFS(H$6:H6,B$6:B6,B6)) |
| Rewards: unrealised gain / loss on a held lot (O) | =IF(N6="","",N6-H6) |
| Summary: basis of held lots (G) | =IF(A7="","",E7-SUMIFS(Rewards!$H$6:$H$1005,Rewards!$B$6:$B$1005,A7,Rewards!$G$6:$G$1005,"Sold")) |
| Summary: effective APR, simple (O) | =IF(A7="","",IF(SUMIFS(Positions!$T$7:$T$26,Positions!$A$7:$A$26,A7)=0,"",M7/SUMIFS(Positions!$T$7:$T$26,Positions!$A$7:$A$26,A7))) |
| Summary: income for a tax year and asset (D26) | =IF(OR($A26="",D$25=""),"",SUMIFS(Rewards!$H$6:$H$1005,Rewards!$I$6:$I$1005,$A26,Rewards!$B$6:$B$1005,D$25)) |
| Summary: income for a month (C41) | =SUMIFS(Rewards!$H$6:$H$1005,Rewards!$J$6:$J$1005,A41) |
N() turns a blank cell into 0, so an empty input never breaks a formula. The running basis in Rewards column L uses a range that grows with each row (H$6:H6), so each row shows the total basis of that asset's rewards up to that point.
Worked example from the sample data
The Example tabs hold four made-up positions and 71 monthly reward rows from January 2025 to 31 October 2026. Every price is fictional.
One reward lot
The first row: 31 Jan 2025, 0.010773 ETH from Example validator A at a fictional $3,000.00. Income at receipt = 0.010773 × $3,000.00 = $32.32. That is also the lot's basis. At the current price of $3,100 the lot is worth $33.40, an unrealised gain of $1.08.
ETH validator A: realised vs expected
8 ETH at a stated 3.20% APR, no auto-compound, from 15 Jan 2025 to 31 Oct 2026 = 654 days. Expected: 8 × 3.20% × 654 ÷ 365 = 0.458696 ETH. Logged: 0.440397 ETH, or 96.0% of expected. Effective APR: 0.440397 ÷ 8 × 365 ÷ 654 = 3.07%.
SOL validator B: auto-compounding
300 SOL at a stated 6.80% APY with rewards restaked, 637 days. Expected: 300 × (1.068637/365 − 1) = 36.4991 SOL. Logged: 35.7162 SOL (97.9%). Effective: (1 + 35.7162 ÷ 300)365/637 − 1 = 6.66%, compared with the 6.80% APY. On Summary the SOL effective APR is 6.82%: that column is a simple yield on the original stake, so it includes the extra coins earned by compounding.
ADA stake pool C: a slow start
In the example the first month paid nothing, so actual rewards are 90.0% of expected and the effective rate is 2.52% against a 2.80% APY. The sheet does not know why rewards were short; it shows that they were.
Sold lots and the current value block
Five ETH reward lots from February to June 2025 are marked Sold. They still count in 2025 income, but the current value block uses only held lots: ETH basis held $1,198.27, value at $3,100 $1,170.79, unrealised −$27.48. Across all three assets the held reward lots cost $7,449.19 in income terms and are worth $6,808.44 at the example prices: −$640.75. Income already counted does not shrink when the price falls; the drop shows up as an unrealised loss on the lots.
Income by tax year
| Tax year | Rewards | ETH | SOL | ADA | Total income |
|---|---|---|---|---|---|
| 2025 | 39 | $901.42 | $2,821.47 | $105.88 | $3,828.77 |
| 2026 | 32 | $654.01 | $3,122.75 | $200.82 | $3,977.58 |
The 2026 row covers rewards to 31 October. These are the fair-market-value totals that Rev. Rul. 2023-14 describes for a cash-method US taxpayer, assuming each date is the date of dominion and control. They are not a tax calculation.
Common mistakes
- Using today's price for old rewards. Income is the value when you received each reward. Look up the price for that date and time, and keep the source in the note column.
- Typing an APY as an APR. Platforms quote either. A wrong rate type makes expected rewards and the comparison rate off.
- Platform names that don't match. "Validator A" and "validator A " are different labels. Rewards P flags rows with no matching position.
- Logging the stake itself as a reward. Only new units count. Moving coins into or out of staking is not income.
- Deleting sold rewards. Mark them Sold instead. Deleting a row removes income you already received.
- Ignoring locked rewards. If rewards could not be moved until later, the date you type matters for the tax year. Use the date you gained control and note why.
Limits
- US dollars only. No tax due, state tax, FIFO or other lot matching for sales of reward coins.
- Expected rewards assume the stated rate held for the whole period. If the rate changed, end the position and start a new one at the new rate.
- Rewards are matched to positions by asset, platform and date. Two positions with the same asset and platform at the same time double-count; the Check column flags it.
- The unrealised figure covers reward lots only, not the coins you staked.
- Liquid by adds the unbonding days you typed. Real exit queues and unbonding times vary.
- Prices are typed by you. The sheet does not fetch prices or connect to wallets.
FAQ
Are staking rewards taxable?
In the US, yes. IRS Revenue Ruling 2023-14 says a cash-method taxpayer who receives staking rewards includes their fair market value in gross income for the tax year in which the taxpayer gains dominion and control over them. That applies to staking on your own and through an exchange. Other countries have their own rules; check your tax authority's guidance or a tax professional.
When are staking rewards taxed: when earned or when sold?
Under Rev. Rul. 2023-14, the income arises when you gain dominion and control: the date you can sell, exchange or otherwise dispose of the reward. Selling the reward later is a separate event: the IRS treats it as a sale of property with a capital gain or loss measured from your basis. This template records the first part and shows the unrealised gain or loss on the units you still hold.
What is the cost basis of staking rewards?
The sheet uses the US dollar fair market value you included in income as each reward lot's basis. That follows IRS guidance for property received as income: IRS Publication 551 says "The amount you include in income becomes your basis" for property received for services, and the IRS virtual currency FAQs (A13, A21) give the same fair-market-value rule. Rev. Rul. 2023-14 itself sets the income amount and timing and does not discuss basis.
How do I calculate the real APR of my staking rewards?
Divide the rewards that actually arrived by the amount staked, then annualise: rewards ÷ amount × 365 ÷ days staked. In the fictional example, 8 ETH staked for 654 days earned 0.440397 ETH, so 0.440397 ÷ 8 × 365 ÷ 654 = 3.07%, against a stated APR of 3.20%. For positions that restake rewards automatically, the sheet compounds the result and compares it with the APY instead.
What is the difference between APR and APY in staking?
APR is the simple yearly rate. APY includes compounding, so it is higher whenever rewards are restaked. With daily compounding, APY = (1 + APR/365)^365 − 1: a 3.20% APR is about 3.25% APY. Platforms quote either one, so the Positions tab asks which one you typed and converts it.
Can I use this staking rewards tracker 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 formulas use SUMIFS, COUNTIFS, COUNTIF, INDEX, MATCH, DATE, YEAR, MONTH, MIN, MAX, N and IF, which work in Excel 2016+, Google Sheets and LibreOffice. There are no macros, no API keys and no dynamic arrays.
Related tools and templates
- Crypto tax spreadsheetFIFO gains on sales by tax year
- Crypto portfolio trackerHoldings, average cost, allocation
- Crypto profit calculatorProfit on a single trade
- APR calculatorAPR and APY explained
- Net worth trackerAssets minus debts over time
- All templatesFree spreadsheets
Educational template only, not financial or tax advice. Example data is fictional: the platforms, quantities, rates and prices are made up to show the formulas and are not market data or a recommendation. Staking involves risks, including price falls, lock-ups and loss of rewards. IRS material was retrieved on 1 November 2026.