Average Down Calculator in Excel

If you're tracking a position over months or years, want a permanent record with dates and notes alongside the math, or just prefer working in a spreadsheet you already have open, building your own average-cost calculator in Excel or Google Sheets is straightforward — the same weighted-average formula this site's calculator uses translates directly into one spreadsheet function.

The formula: SUMPRODUCT

Set up two columns — quantity and price paid — one row per purchase lot. If your quantities are in B2:B10 and your prices are in C2:C10, the weighted average cost is a single formula:

=SUMPRODUCT(B2:B10,C2:C10)/SUM(B2:B10)

SUMPRODUCT multiplies each quantity by its matching price and adds the results — exactly Σ(shares × price) — and dividing by SUM(B2:B10) (total quantity) gives the same average this site computes; see how we calculate for the identical formula written out in plain math notation. This works identically in Google Sheets with no changes to the formula.

Step-by-step setup

1. Column A — Date (optional but useful for a running record): the date of each purchase.
2. Column B — Quantity: shares, coins/units, or contracts bought in that lot.
3. Column C — Price: the price paid per unit for that lot.
4. Column D — Lot cost (optional, for a visible running total): =B2*C2, filled down each row.
5. A summary cell for total quantity: =SUM(B2:B10)
6. A summary cell for total cost: =SUM(D2:D10) or =SUMPRODUCT(B2:B10,C2:C10) directly
7. A summary cell for average cost: =SUMPRODUCT(B2:B10,C2:C10)/SUM(B2:B10)

Adding break-even / target price

With your average cost in, say, cell F2, and a target gain percentage in F3 (enter as a plain number like 15 for 15%, or format the cell as a percentage and adjust the formula accordingly):

=F2*(1+F3/100)

With F3 set to 0, this returns exactly your break-even price — the same target-price formula described on how we calculate.

Adding unrealized gain/loss

With total quantity in F4, total cost in F5, and a current market price you type in manually in F6 (spreadsheets don't pull live prices without an add-in or a manual refresh):

=(F4*F6)-F5

gives your unrealized gain/loss in dollars; divide that by F5 and format as a percentage for the gain/loss percentage.

A worked cell-by-cell example

Using the calculator's own default numbers (100 units at $50, then 100 units at $30):

Cell	Content	Result
B2 / C2	100 / 50	Lot 1
B3 / C3	100 / 30	Lot 2
F2	=SUMPRODUCT(B2:B3,C2:C3)/SUM(B2:B3)	40.00

Matches the calculator's own worked example on how we calculate exactly, as it should — it's the same formula either way.

When a spreadsheet is genuinely the better tool

• Multi-year tracking with dates and notes — a spreadsheet keeps a permanent, editable history alongside your averaging math, which a stateless browser calculator doesn't retain between visits (this site's optional Pro save feature is aimed at closing that specific gap without needing a spreadsheet).
• Tracking many separate positions at once — one tab per position, or one sheet with a position-ID column, scales further than re-entering lots into a single-position calculator repeatedly.
• Combining with other portfolio math — allocation percentages, tax-lot notes, or dividend tracking alongside cost basis in the same workbook.

When the browser calculator is faster

For a one-off "what would my average be if I bought X more at Y" check, typing numbers into the calculator above is faster than opening a spreadsheet, avoids any risk of a broken cell reference or formula typo, and works identically on a phone with no formatting to fight with. Both approaches use the exact same underlying math — pick whichever fits how you want to use the result.

FAQs

Does Excel have a built-in "average cost" function? No dedicated built-in function — SUMPRODUCT divided by SUM, as shown above, is the standard way to compute a weighted average in both Excel and Google Sheets.

What if a row is blank or a lot gets removed? SUMPRODUCT and SUM both simply ignore blank cells within the range, so leaving unused rows empty (rather than deleting them) won't distort the result — the same forgiving behavior as removing a row in this site's multi-lot table.

Can I build the unrealized gain/loss with a live price feed? Google Sheets supports pulling some price data via add-ons or the GOOGLEFINANCE function for certain tickers; Excel has a Stocks data type on newer versions. Both require setup this page doesn't cover in detail — a manually entered current price, as shown above, works everywhere with no setup at all.

→ Calculate my average cost (free)