Free Crypto Portfolio Tracker Excel Template

Free Crypto Portfolio Tracker Excel Template

Most crypto tracking starts the same way: a note on your phone, a screenshot of an exchange balance, maybe a free app that logs three coins before asking you to upgrade. It works until you actually need to answer a real question — what did I pay for this Bitcoin, what happens to my portfolio if the market drops 30%, or which lot am I selling if I want to keep my tax bill reasonable. At that point most trackers stop being useful, because they were built to show a balance, not to help you plan.

The Crypto Portfolio & Market Analytics workbook is built around that second problem. It's a single Excel file — no macros, no linked accounts, no monthly fee — organized as a chain of sheets that each do one job: a Settings sheet where you define your portfolio rules, a Coin Master list of the assets you actually hold, a Transactions ledger for every buy, sell, transfer, reward, and staking event, and a set of analysis sheets (Holdings, Allocation Analysis, Tax Lots, Scenario & Risk Lab) that turn that ledger into decisions. A Dashboard and a print-ready Summary sheet sit on top, pulling the same numbers into a one-screen view you can review daily or hand to someone else.

What separates this from a basic tracker template is the depth of the analysis layer. The workbook ships with a 1,800-row historical OHLCV (open/high/low/close/volume) database covering ten coins over 180 days, with RSI, MACD, moving averages, and drawdown already built out as formulas — so you can see exactly how the indicators are constructed instead of trusting a locked chart. The Tax Lots sheet runs a FIFO/LIFO allocation against your actual purchase history to estimate realized gains before you sell. The Scenario & Risk Lab lets you model a market crash or rally and see the dollar impact on your specific holdings, plus a position-sizing calculator based on your stop-loss distance and risk tolerance.

This is a free website version, built for manual, disciplined use — you replace the sample data with your own exchange exports, keep prices reasonably current by hand, and use it as a planning and record-keeping tool rather than a live trading terminal. The sections below walk through exactly what's in the file and how to set it up.

What This Template Helps You Do

• Log every transaction in one ledger — The Transactions sheet holds buys, sells, transfers in and out, rewards, staking income, and fees across 21 columns covering date, coin, exchange/wallet, units, price, fees, and whether the activity is taxable.
• See your real cost basis and unrealized gains — The Holdings sheet calculates net invested amount, average cost per unit, current value, and unrealized gain or loss for every coin automatically from the transaction log.
• Compare your actual allocation to your targets — Set target allocation percentages per coin in Coin Master (for example, 35% Bitcoin, 23% Ethereum). Allocation Analysis shows the gap versus your target, flags concentration risk, and suggests a dollar amount to buy or sell to get back on target.
• Estimate tax exposure before you sell — The Tax Lots sheet runs a lot-by-lot FIFO or LIFO calculation. Enter a hypothetical or real sale and it walks through your eligible purchase lots in order, allocates cost basis, and estimates realized gain, gain margin, and how many lots the sale touches.
• Study price action with real technical indicators — The Price Updates sheet is a full market analysis page: candlestick-style price range charts, trading volume, SMA 20/50, RSI 14, MACD vs. signal line, and a historical drawdown chart, all built on the 1,800-row OHLCV database.
• Stress-test your portfolio against market scenarios — The Scenario & Risk Lab lets you pick a coin, set bear/base/bull/custom percentage moves, and instantly see the target price, portfolio dollar impact, and a nine-step stress ladder from a 50% crash to a 100% rally.
• Review everything on one dashboard — The Dashboard pulls price data, portfolio value, allocation, RSI, and scenario outputs into a single filtered view; pick a coin, timeframe, and scenario from three dropdowns and every chart and KPI updates together.
• Print or export a clean summary — The Print Summary sheet condenses the whole workbook into a report-style layout — portfolio value, top holdings, allocation gaps, and a notes/action-plan area — formatted to print or save as a PDF.

What's Included in the Workbook

The workbook contains 14 sheets. Here is what each one is for, and what you edit versus just review:

How to Use the Template

• Step 1: Open the workbook and read Start Here — Review the six-step workflow and color legend before entering anything. Yellow cells are user input; light blue cells hold formulas you shouldn't overwrite.
• Step 2: Configure your Settings sheet — Set your portfolio name, base currency and symbol, reporting year, and default tax method (FIFO or LIFO). Set your target portfolio value, max single-coin allocation limit (the sample file uses 20%), and review threshold.
• Step 3: Build your asset universe in Coin Master — List every coin you hold or plan to track — ticker, network, asset type, stablecoin flag, and target allocation. The sample ships with ten coins (BTC, ETH, USDT, BNB, XRP, USDC, SOL, TRX, DOGE, ADA); delete what you don't hold and add rows for anything missing.
• Step 4: Replace the sample transactions with your real history — Delete the 80 sample rows and enter your actual buys, sells, transfers, rewards, staking income, and fees. Use the Type and Exchange/Wallet dropdowns rather than typing free text.
• Step 5: Update prices on the Price Updates sheet — Enter current prices manually (the Price Source URL column in Coin Master links to CoinGecko by default), or replace the sample OHLCV rows with your own exchange/API export.
• Step 6: Review Holdings, Allocation Analysis, and Tax Lots — Check your real cost basis and unrealized gain/loss, how far you've drifted from your target weights, and — if you're planning a sale — an estimated realized gain under FIFO or LIFO before you execute it.
• Step 7: Run scenarios in the Risk Lab — Test bear, base, bull, and custom price moves against your actual portfolio value, and use the position-sizing calculator when sizing a new trade against a stop-loss distance and risk-per-trade percentage.
• Step 8: Check the Dashboard, then export the Print Summary — Use the three filters for a fast daily or weekly review, and open Print Summary when you want a static record for your own files or a tax preparer.

Practical Example

Say you're holding a small, real crypto portfolio: some Bitcoin bought over several months, a staking position in Ethereum, and a stablecoin balance you use for rebalancing. Here's how the workflow plays out using the workbook's own sample data as a reference point.

What you enter: In Transactions, you'd log each Bitcoin purchase separately — for example, 0.0219 BTC bought at $36,553.16 on Feb 6, and 0.0092 BTC bought at $43,484.26 on May 14 — along with the exchange or wallet used and any fees paid. Staking rewards get logged as their own transaction type with no cash outflow, just added units.

What the workbook calculates: Holdings automatically blends those purchases into a single average cost per unit and current value for Bitcoin, alongside your unrealized gain/loss and how your actual BTC allocation compares to your Coin Master target — in the sample data, actual Bitcoin allocation sits at 6.7% against a 35% target, a gap Allocation Analysis flags as "Review" with a suggested buy amount.

What you get if you decide to sell: Say you want to sell 0.025 BTC at $68,000. On Tax Lots, you'd enter those sale details with FIFO as the method. The workbook walks through your purchase lots in date order and shows it needs two lots to cover the sale, with an allocated cost basis around $939 against proceeds of $1,700 — an estimated realized gain near $761 and a 44.8% gain margin. That's not a tax filing; it's a planning number that tells you roughly what you're looking at before you place the sell order.

This example uses the template's own sample figures to illustrate the workflow only — it is not a real trade recommendation or a completed tax calculation, and the numbers here should not be treated as investment or tax advice.

Understanding the Dashboard and Key Reports

Who This Template Is Best For

Who This Template May Not Be For

• People who want automatic wallet or exchange sync — This workbook uses manual entry; there's no API connection or live wallet read.
• Traders who need real-time, tick-level pricing — The OHLCV database is a 1,800-row demonstration dataset meant to show how the indicators are built, not a live feed.
• Anyone who needs their tax numbers finalized for filing — The Tax Lots sheet produces a planning estimate using a simplified FIFO/LIFO method; it doesn't account for jurisdiction-specific tax rules or every edge case a tax professional would apply.
• Teams that need multi-user, cloud-based collaboration — This is a single Excel file without built-in multi-user editing, version history, or permissions.
• Users who want heavy automation via VBA or Power Query — The workbook is intentionally macro-free and doesn't include Power Query refresh pipelines.
• Anyone looking for investment or trading advice — The scenario percentages, stress ladder, and target prices are mathematical what-if outputs based on inputs you control, not forecasts, signals, or recommendations.

Why This Template Is Different

A blank spreadsheet can hold a list of coins and prices, but it can't tell you your real cost basis after a dozen scattered purchases, walk a hypothetical sale through FIFO or LIFO lot logic, or show you what a 30% drop does to your specific portfolio. That gap — between "a spreadsheet with numbers in it" and "a spreadsheet that helps you decide something" — is where this workbook is built to sit.

The structure does a lot of the work. Because Settings, Coin Master, and Transactions feed everything downstream, you enter data once and every analysis sheet stays in sync — you're not maintaining separate copies of the same numbers. The color-coded input system (yellow for user input, light blue for formulas) means you always know what's safe to edit. And because the indicator math (RSI, MACD, SMA, drawdown, VaR) is built as visible formulas rather than a locked chart, you can see exactly how each number is derived and trust the output because you can trace it.

It's also honest about what it is. The workbook tells you directly, on the Start Here sheet, that scenario prices are mathematical what-if outputs, not forecasts, and that the sample OHLCV data should be replaced with trusted exchange or API data before real use.

What You Get in the Download

The download is a single ZIP folder with everything you need to get started right away:

• A ready-to-use Crypto Portfolio Tracker workbook (.xlsx) — pre-filled with 80 sample transactions across ten coins and a 1,800-row OHLCV database so you can see every sheet working together before you replace it with your own data.
• A PDF export of the full workbook — every sheet in one document, useful as a quick offline reference for the layout before you open the Excel file.
• A short note file — a quick summary of what is in the download and where to go if you need something beyond the free version.

Practical Tips Before You Start

• Set up Settings and Coin Master before touching Transactions — the dropdowns and target allocations in those two sheets drive validation and calculations everywhere else.
• Enter transactions in date order where possible — the Tax Lots FIFO/LIFO logic depends on purchase dates to determine lot order.
• Use the dropdown lists instead of typing free text — Transaction Type, Exchange/Wallet, and Tax Method are all controlled by Settings dropdowns; a variant spelling can break lookups elsewhere.
• Update prices on a consistent schedule — since prices are manual, pick a cadence and stick to it; Holdings and Dashboard are only as current as your last price update.
• Keep a backup copy before a big data replacement — before deleting the 80 sample transactions or the 1,800 sample OHLCV rows, save a duplicate of the file.
• Don't overwrite the light-blue formula cells — if a cell recalculates automatically, it's not meant for direct input; check the color legend on Start Here if you're unsure.
• Review Allocation Analysis on a schedule, not just when something feels off — allocation drift is gradual, and checking weekly or monthly catches it before it becomes a large rebalancing job.
• Treat the Scenario & Risk Lab as a planning exercise, not a prediction tool — run multiple scenarios rather than anchoring on one number.
• Save a dated copy at meaningful checkpoints — there's no built-in version history, so a copy at month-end or after a major trade gives you a record to look back on.

Common Mistakes to Avoid

• Typing dates as text instead of real dates — this can make sorting and lot-order calculations on the Tax Lots sheet misread the sequence.
• Editing formula cells directly — it's tempting to "fix" a number by typing over it, but that breaks the link to Transactions or Price Updates. Check the input data feeding it instead.
• Creating too many micro-categories in Coin Master — listing every coin you've ever touched, including dust amounts, adds noise to the allocation charts without adding useful signal.
• Batching transaction entry once a month instead of logging as you go — this increases the odds of a missed transaction or a price typo, and leaves your Dashboard stale for weeks.
• Forgetting to update Settings when your strategy changes — if your target allocation, tax method, or risk tolerance shifts, update Settings and Coin Master accordingly.
• Using unrealistic scenario percentages — a "bear" scenario at -5% or a "bull" scenario at +300% defeats the purpose of stress-testing.
• Comparing Allocation Analysis or Holdings before prices are updated — this gives a false read on how far you've actually drifted.
• Deleting entire rows in the Transactions table instead of clearing contents — this can disturb the table's structured range and any charts or formulas referencing it by row position.

Need a Custom or Premium Version?

This free download is the standard website version of the Crypto Portfolio & Market Analytics workbook. If you'd like a Premium version with additional features, or a professionally designed custom Excel or Google Sheets template built around your own coin list, tax method, or risk framework, we're happy to help.

Custom templates can be developed for personal, educational, business, financial, productivity, and other specialized needs. Share your query and details at quote@dothecalculation.com and we'll send you a quote for that work.

Limitations and Honest Notes

• Not financial, tax, or investment advice — every calculation in this workbook (scenario prices, tax-lot estimates, risk metrics) is a planning tool based on inputs you control, not a professional recommendation.
• No live wallet or exchange connection — all data entry is manual, with no bank, wallet, or exchange sync built into the file.
• No macros or VBA — this is a formula-only workbook by design, which keeps it safe to open without enabling macros, but also means there's no automated data refresh.
• Built primarily for Excel — Google Sheets compatibility is reasonable for most formulas and layout, but some conditional formatting or chart styling may render slightly differently.
• Sample data is for demonstration only — the 80 sample transactions and 1,800-row OHLCV database show the workbook working end-to-end; they must be replaced with your own trusted figures before real use.
• Tax-lot estimates are simplified — FIFO/LIFO allocation here is a planning approximation and doesn't account for every jurisdiction's specific tax treatment of crypto transactions.
• Manual price updates required — current price, RSI, MACD, and related metrics are only as accurate as the last time you updated Price Updates.
• Save a backup before heavy editing — because formulas reference other sheets throughout the workbook, keep an unedited copy on hand.

Related Calculators and Guides

To help you get the most out of tracking a crypto portfolio, consider these related tools and guides on our platform:

• Quick trade profit check — Blend multiple DCA purchase lots, factor in your holding period, and get an estimated after-tax profit without opening the full workbook using the Crypto Profit Calculator.
• See the Tax Lots sheet's method worked through step by step — FIFO vs. LIFO for Crypto Taxes walks the same lot-matching logic through a full example, plus the 2025 wallet-by-wallet rule change.
• See the Allocation Analysis sheet's math worked through step by step — How Crypto Portfolio Rebalancing Works uses this template's own sample allocation gaps as a worked example.
• Understand your tax exposure — Read Understanding Capital Gains Tax on Appreciated Assets for the framework behind how holding periods and gains are taxed, which the Tax Lots sheet is built to estimate against.
• Get the ROI math right — See the return-calculation logic behind the workbook's return percentages in ROI Calculation: Formula, Annualized Return, and Examples.
• Model long-term growth — Project compound growth of contributions and returns with the Investment Calculator or the Compound Interest Calculator.
• Evaluate risk-adjusted performance — Calculate Sharpe, Sortino, and other risk-adjusted return ratios with the Risk-Adjusted Return Calculator.
• More spreadsheet templates — Browse the full Excel Template Library for other free, downloadable workbooks.