Build a DCA calculator in Google Sheets

Dollar-cost averaging means making purchases on a regular schedule. This worksheet records those purchases from exchange statements and adds market references for comparison. It assumes one crypto asset, one reporting currency and a buys-only history in which you still hold the purchased units. Sales, transfers with fees, staking rewards and other balance changes require additional ledger entries and reconciliation.

Step 1 — log each buy

Use four columns: A: Date · B: Total spent (USD, including fees) · C: Historical reference price · D: Actual units received. Enter A, B and D from your transaction records. For this example, every row is a BTC purchase in USD. Do not combine different currencies in B or different coins in D. Install the add-on and connect through Extensions → CryptoTrackPro → Open CryptoTrackPro.

Step 2 — add a separate historical reference

In C2, convert the actual date value in A2 into ISO text before requesting its historical price:

=cryptotrackproPrice("BTC", TEXT(A2,"yyyy-mm-dd"), "USD")

For supported Kraken daily candles, this returns the day's close. Older requests can use a trade-based approximation, and fallback coverage can use another historical snapshot. Availability depends on the coin, currency and date. This reference does not replace your actual execution price or units received. Fill C down, keeping D as the actual units from your records. For an optional hypothetical estimate, use this in E2:

=B2/C2

Label E as Hypothetical units at daily reference. It assumes the entire recorded spend could buy at that reference price and does not model execution fees or spread. Keep E out of actual-holdings totals. Formulas here use commas; if your spreadsheet expects semicolons, change the argument separators. See the historical date guide for date-cell handling.

Step 3 — summarize the actual purchase ledger

Put the formulas in the G cells listed above, keeping the input rows in A:D. Adjust every input range together if you have more than 99 purchases. Format G7 as a percentage. The average acquisition cost uses actual spend and units, while the current value is an indicative market valuation before selling costs. Use Refresh All Prices when reviewing it. Timed refresh requires the sidebar open: Base every 30 minutes or Pro every 5 minutes, within plan limits. These calculations are a personal tracking summary, not a tax cost-basis method.

Keep tokenized references separate from brokerage holdings

The actual-spend and actual-units layout can also record broker transactions, but those holdings need an appropriate exchange-listed security price source. =cryptotrackproRWA("SPY") and =cryptotrackproRWA("AAPL") return tokenized asset references, which can diverge from the underlying ETF or share. Do not silently use them to value broker-held securities. The historical crypto function does not provide a stock execution record. See the tokenized RWA guide before combining asset types.

FAQ

How do I get the price on a past date?

Use =cryptotrackproPrice("BTC",TEXT(A2,"yyyy-mm-dd"),"USD") for a date cell in A2. It returns an available historical reference, usually a daily close for supported Kraken candles. Fallback methodology can differ. Keep your actual execution prices and units from transaction records.

How do I calculate average acquisition cost?

For this buys-only BTC ledger, sum the actual USD spend including fees in B2:B100 and divide by actual BTC units received in D2:D100. Do not derive actual units from a daily reference price. Sales and other balance changes require additional reconciliation.

Is it free?

Current and single-date historical crypto prices are available on Free within 50 daily lookups. New eligible accounts receive a 30-day Base trial without a card and return to Free unless they subscribe. Base and Pro provide higher allowances; technical indicators require Base or Pro.

← Back to the blog