By the CryptoTrackPro team · Published
Quick answer: value the same holdings at the start and end of a period, calculate end value / start value - 1, and compare it with Bitcoin's price return over those exact dates. This tutorial handles a fixed portfolio with no deposits, withdrawals, trades, or changes in units during the period.
A rising portfolio total does not tell you how it compared with a reference asset. A Bitcoin benchmark provides a specific comparison: what happened to the same starting value if it had followed BTC's price movement? It is a measurement choice, not a recommendation to hold Bitcoin.
Keep the dates, quote currency, and treatment of cash flows consistent. Comparing a portfolio's return since January with Bitcoin's last-24-hour change answers two different questions. Here we use historical daily prices at both endpoints, rather than mixing a historical close with a live intraday quote.
1. Choose two completed dates
Install the CryptoTrackPro add-on and open it from Extensions → CryptoTrackPro → Open CryptoTrackPro to connect. In a blank tab, put Start date in A1 and End date in A2. In B1 enter =DATE(2026,1,1) and in B2 enter =DATE(2026,6,30).
Use dates when both assets have available price history. The date formulas create actual Sheets dates; the lookup formulas below convert them to the required YYYY-MM-DD text. If your spreadsheet locale uses semicolons as formula separators, replace argument-separating commas with semicolons.
2. Price a fixed basket at both endpoints
Use row 4 for headers: Symbol, Units, Start price USD, End price USD, Start value USD, and End value USD in columns A through F. Enter BTC and 0.01 in A5:B5, then ETH and 0.5 in A6:B6. These holdings remain fixed throughout this example.
| Cell | Formula |
|---|---|
| C5 | =cryptotrackproPrice(A5,TEXT($B$1,"yyyy-mm-dd"),"USD") |
| D5 | =cryptotrackproPrice(A5,TEXT($B$2,"yyyy-mm-dd"),"USD") |
| E5 | =B5*C5 |
| F5 | =B5*D5 |
Put =SUM(E5:E6) in E7 and =SUM(F5:F6) in F7. Before interpreting the result, confirm all four price cells are numeric with =COUNT(C5:D6)=4. A missing historical price is a missing observation, not a zero. Keep its error or message visible until you can resolve the symbol, date, or quota issue.
3. Calculate and compare returns
Put Portfolio return in A9 and Bitcoin return in A10. The portfolio formula checks that all endpoint prices are present; the BTC formula reuses the existing BTC prices:
B9: =IF(COUNT(C5:D6)=4,F7/E7-1,NA())
B10: =D5/C5-1
B11: =B9-B10Enter only the part after each cell label into Sheets. Format B9:B11 as percentages, and label A11 Return difference (percentage points). If B11 displays 3.33%, describe it as a 3.33-percentage-point difference, not “3.33% more money.” The subtraction compares two return percentages.
| Asset | Units | Start price | End price | Start value | End value |
|---|---|---|---|---|---|
| BTC | 0.01 | $50,000 | $60,000 | $500 | $600 |
| ETH | 0.5 | $2,000 | $2,500 | $1,000 | $1,250 |
| Total | — | — | — | $1,500 | $1,850 |
Using these sample numbers, the basket gains 23.33% and BTC gains 20%, a difference of 3.33 percentage points. An equal $1,500 starting value following BTC would finish at $1,800, compared with $1,850 for the basket. This example is arithmetic only; actual formula results depend on the returned data.
4. Normalize both starting values to 100
A normalized comparison removes the visual distraction of different starting dollar amounts. In H4:J4 enter Date, Portfolio index, and BTC index. Set H5 to =$B$1, I5 and J5 to 100, and H6 to =$B$2. In I6 enter =(1+$B$9)*100; in J6 enter =(1+$B$10)*100.
A column chart of the ending index values shows 123.33 versus 120 with the sample data. Two endpoints do not reveal the path between them or the largest interim decline. Add actual observations for intermediate dates if you want a time-series chart; do not draw conclusions about daily volatility from these two rows.
When this calculation needs a different model
Deposits increase a balance without being investment returns. Withdrawals, trades, staking rewards, and transfers that change the units also break the fixed-basket assumption. For those cases, this sheet remains a hypothetical comparison of the entered holdings. It does not measure your account's cash-flow-adjusted performance.
Use the same currency throughout. Both endpoints can request EUR instead of USD, but change every relevant lookup and label together. A EUR comparison includes currency movement relative to a USD comparison. Historical closing prices also differ from the actual prices you paid or received.
Single-date price lookups are available on all CryptoTrackPro plans within their call limits. Free includes 50 daily calls; first-time users start with a 30-day Base trial without a card. You can extend the free portfolio template with this separate analysis tab, or use the allocation tutorial for a current-weight review.
Start with a working portfolio sheet
Copy the free template, install CryptoTrackPro, and adapt the layout to your workflow. First-time users get a 30-day Base trial with no card required.
Get the free portfolio template