Home › Templates › Average down spreadsheet
Average Down Spreadsheet: Average Cost, Break-Even and Target Planner
A free average down spreadsheet for Excel and Google Sheets. Log up to 30 buys and sells for each of up to 20 stocks, ETFs or coins. It keeps your weighted average cost with and without fees, handles partial sells with the average-cost method, and shows your break-even price after sell fees. A planner works out how many shares, and how much cash, you would need to reach a target average, and how big the position would become in your portfolio.
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 email gate, no add-in.
| Position (fictional) | Shares | Avg cost incl. fees | Price | Unrealised P/L | Realised P/L | Break-even | % of portfolio | vs 15% limit |
|---|---|---|---|---|---|---|---|---|
| Stock A | 400 | 31.1975 | 24.00 | −2,879.00 | −527.00 | 31.2000 | 12.3% | Within |
| Stock B | 140 | 102.7786 | 112.00 | 1,291.00 | 0.00 | 102.7786 | 20.2% | Above |
| Stock C | 70 | 59.1390 | 75.00 | 1,110.27 | 602.88 | 59.2097 | 6.7% | Within |
| Stock D | 0 | — | — | — | −614.00 | — | — | — |
| Bank E | 87.75 | 40.9213 | 41.30 | 33.24 | 17.64 | 40.9622 | 4.7% | Within |
| Stock F | 200 | 15.4083 | 18.20 | 558.33 | −333.33 | 15.4083 | 4.7% | Within |
| Total (positions) | 113.84 | −853.81 | 48.6% |
Before you average down: the risk
Averaging down means buying more of something after its price has fallen, so the average price you paid goes down. The maths is simple. The risk is easy to miss: a lower average does not make the position safer. Every extra buy puts more of your money on the same company, and the price can keep falling.
"concentration risk—the risk of amplified losses that may occur from having a large portion of your holdings in a particular investment, asset class or market segment relative to your overall portfolio."FINRA, "Concentrate on Concentration Risk", Investor Insights, June 15, 2022
"Diversification reduces the risk of major losses that can result from over-emphasizing a single security or single asset class, however resilient you might expect that asset or asset class to be."FINRA, "Asset Allocation and Diversification"
"Diversification can't guarantee that your investments won't suffer if the market drops. But it can improve the chances that you won't lose money, or that if you do, it won't be as much as if you weren't diversified."Investor.gov (U.S. SEC), "Diversify Your Investments"
Two numbers in the spreadsheet keep this in view:
- % of portfolio. The Summary shows what share of your total portfolio each position is, and flags any position above a limit you set. The Planner shows the weight after a planned buy or ladder. In the fictional example, Stock A is 12.3% of the portfolio today. Four planned buys at prices 10% to 40% lower take it to 15.9%, above the 15% limit, even though the price is lower.
- Gain needed to recover. A fall of d needs a gain of 1 / (1 − d) − 1 to get back. A 50% fall needs a 100% gain. The deeper the fall, the faster the required gain grows.
| Fall | Gain needed to get back |
|---|---|
| 10% | 11.1% |
| 20% | 25.0% |
| 25% | 33.3% |
| 30% | 42.9% |
| 40% | 66.7% |
| 50% | 100.0% |
| 60% | 150.0% |
| 75% | 300.0% |
| 90% | 900.0% |
Averaging down is not the same as dollar-cost averaging. Investor.gov describes dollar-cost averaging as "investing your money in equal portions, at regular intervals, regardless of the ups and downs in the market." Averaging down is a choice to buy because the price fell, often with larger amounts and into one holding. For a schedule of equal buys, the DCA tracker fits better.
What's inside the spreadsheet
| Tab | What it does |
|---|---|
| Start here | Instructions, the formulas, the risk and tax notes. Example data is marked fictional. |
| Trades | 20 position blocks. Each block has the name, current price and your estimated sell fee ($ per sale and % of sale), then 30 rows: date, Buy/Sell, shares, price, fees. For every row: shares held after, cost basis with and without fees, average cost with and without fees, realised P/L on sells and a Check column. Empty, ready for your trades. |
| Summary | One line per position: shares, average cost with and without fees, cost basis, current price, market value, unrealised P/L ($ and %), realised P/L, fees paid, break-even price after sell fees, the price move needed to break even, % of portfolio and a check against your own concentration limit. Totals, the largest position and a bar chart of portfolio weights. |
| Planner | Pick a position (or type shares, average and price by hand). Target-average calculator: shares needed, cash needed, new average, weight after. A ladder of up to 10 planned buys at lower prices with the average, cash and weight after each one. A fall vs gain-needed table, with your own figure. |
| Example trades / Example summary / Example planner | The same three tabs filled with six made-up positions: averaging down with a partial sell, regular buys, a profitable trim, a position fully sold at a loss, fractional shares with a % sell fee, and a loss-making sell. |
How to use it
- Open Trades. In a free block type the name or ticker (C), the current price (F) and, if you want the break-even after costs, your estimated sell fee in H ($ per sale) and J (% of the sale value).
- Enter each trade in date order, oldest first: date, Buy or Sell, number of shares (always positive), price per share, and the total fees for that trade. Fractional shares are fine.
- Check that the block's Rows to fix says 0 and every Check cell says OK. A sell larger than the shares you hold at that point is flagged and ignored, so the totals never go negative.
- Open Summary. Type the value of everything else in the portfolio (other holdings and cash) in C4, and your own maximum % for one position in C5 if you want the concentration check.
- Open Planner. Type a position number in C5, then a target average in C23 (and a buy price in C22 if it is not today's price). For a ladder, type the prices and share counts you are thinking about in rows 41–50.
- Read the results with the risk numbers next to them: the weight after the buy, the cash needed compared with what is already in the position, and the rise needed to break even.
The formulas in the file
Each block on Trades keeps three running totals in grey helper columns: shares held (N), cost with fees (O) and cost without fees (P). Row 9 is the second trade row of block 1; row 8 is the one above it. The Check column decides whether a row counts:
=IF(COUNTA($C8:$F8)=0,"",IF(AND($C8<>"Buy",$C8<>"Sell"),"Pick Buy or Sell",IF(OR(NOT(ISNUMBER($D8)),N($D8)<=0),"Shares: a number above 0",IF(OR(NOT(ISNUMBER($E8)),N($E8)<0),"Price: a number of 0 or more",IF(AND($F8<>"",OR(NOT(ISNUMBER($F8)),N($F8)<0)),"Fees: 0 or more, or blank",IF(AND($C8="Sell",$D8>0+0.000000001),"Sell is more than shares held: ignored","OK"))))))
Shares held after the trade:
=IF($M9="OK",ROUND(IF($C9="Buy",$N8+$D9,$N8-$D9),8),$N8)
Cost with fees. A buy adds shares × price + fees. A sell removes cost in proportion to the shares sold (average-cost method); a sell of every share resets the cost to zero:
=IF($M9="OK",IF($C9="Buy",$O8+$D9*$E9+N($F9),IF($N9<=0,0,$O8*(1-$D9/$N8))),$O8)
=IF($M9="OK",IF($C9="Buy",$P8+$D9*$E9,IF($N9<=0,0,$P8*(1-$D9/$N8))),$P8)
Average cost with fees, and the realised profit or loss on a sell (proceeds minus sell fees minus the cost removed):
=IF(AND($M9="OK",$N9>0),$O9/$N9,"")
=IF(AND($M9="OK",$C9="Sell"),$D9*$E9-N($F9)-$O8*$D9/$N8,"")
On Summary (row 12 = position 1; row 37 of Trades is its last trade row; H6 and J6 hold the estimated sell fee). Average cost, break-even price after sell fees, and % of portfolio (C6 = total portfolio value):
=IF(AND(COUNTIF(Trades!$M$8:$M$37,"OK")>0,Trades!$N$37>0),Trades!$O$37/Trades!$N$37,"")
=IF(AND(COUNTIF(Trades!$M$8:$M$37,"OK")>0,N($C12)>0),($F12+N(Trades!$H$6))/($C12*(1-N(Trades!$J$6))),"")
=IF(AND(ISNUMBER($H12),$C$6>0),$H12/$C$6,"")
On Planner: C10 = shares held (S), C11 = average cost (A), C23 = target (T), C25 = buy price (P), C24 = fee (F). Shares needed comes from solving (S × A + n × P + F) / (S + n) = T for n:
=IF($C$26="OK",($C$10*$C$11+N($C$24)-$C$10*$C$23)/($C$23-$C$25),"")
=IF($C$26="OK",ROUNDUP($C$27-0.000000001,0),"")
=IF($C$26="OK",$C$28*$C$25+N($C$24),"")
=IF($C$26="OK",($C$13+$C$29)/($C$10+$C$28),"")
Weight after the buy, with the portfolio revalued at the buy price (C14 = portfolio value, C12 = today's price):
=IF(AND($C$26="OK",($C$14+$C$10*($C$25-$C$12)-N($C$24))>0),$C$31*$C$25/($C$14+$C$10*($C$25-$C$12)-N($C$24)),"")
Ladder row 41: average after the step (G = cost basis after, F = total shares after) and weight at that price (M = cash for each step):
=IF($L41=1,$G41/$F41,"")
=IF($L41=1,IF(($C$14-$C$10*$C$12-SUM($M$41:$M41)+$F41*$B41)>0,$F41*$B41/($C$14-$C$10*$C$12-SUM($M$41:$M41)+$F41*$B41),""),"")
Gain needed to recover a fall in A56:
=1/(1-$A56)-1
No XLOOKUP, LET, LAMBDA or dynamic arrays, so the file works in Excel 2016 and later, Google Sheets and LibreOffice. Every result was cross-checked against an independent Python calculation on the example and on six test data sets, including all 20 positions × 30 random trades, invalid rows, full sells and impossible targets.
Worked example (fictional data)
Stock A: four buys on the way down, a partial sell, one more buy
Every trade has a 1.00 fee. The table is the Example trades block for position 1.
| Date | Type | Shares | Price | Fee | Shares after | Cost incl. fees | Avg incl. fees | Avg excl. fees | Realised P/L |
|---|---|---|---|---|---|---|---|---|---|
| 2025-02-03 | Buy | 100 | 50.00 | 1.00 | 100 | 5,001.00 | 50.0100 | 50.0000 | |
| 2025-04-07 | Buy | 50 | 40.00 | 1.00 | 150 | 7,002.00 | 46.6800 | 46.6667 | |
| 2025-06-16 | Buy | 100 | 30.00 | 1.00 | 250 | 10,003.00 | 40.0120 | 40.0000 | |
| 2025-09-22 | Buy | 150 | 22.00 | 1.00 | 400 | 13,304.00 | 33.2600 | 33.2500 | |
| 2026-01-12 | Sell | 100 | 28.00 | 1.00 | 300 | 9,978.00 | 33.2600 | 33.2500 | −527.00 |
| 2026-05-04 | Buy | 100 | 25.00 | 1.00 | 400 | 12,479.00 | 31.1975 | 31.1875 |
- After four buys the position holds 400 shares that cost 13,304.00 with fees, an average of 33.2600 (or 33.2500 without the four 1.00 fees).
- The sell of 100 shares at 28.00 removes 100 / 400 = 25% of the cost: 3,326.00. Proceeds after the fee are 2,799.00. Realised result: −527.00. The 300 shares left still average 33.2600.
- One more buy of 100 at 25.00 (+1.00 fee) brings the average to 31.1975 on 400 shares, a cost basis of 12,479.00.
- Break-even with a 1.00 sell fee: (12,479.00 + 1.00) / 400 = 31.20. At today's 24.00 the price is 23.07% below the average and needs to rise 30.00% to break even.
Target average: what would it take to get to 30.00?
Shares needed at 24.00 with a 1.00 fee: (400 × 31.1975 + 1 − 400 × 30) / (30 − 24) = 480 / 6 = 80 shares, costing 1,921.00. The new average is 30.00 on 480 shares. Stock A goes from 12.34% to 14.81% of the portfolio, and the price still has to rise 25% from 24.00 just to break even. Lowering the average by 1.1975 needed 15.4% more money on top of the 12,479.00 already in the stock.
A ladder of planned buys
Suppose the plan is 100 more shares at each of 10%, 20%, 30% and 40% below today's price. Each row assumes the rows above it were filled:
| Step | Buy price | vs today | Shares | Total shares | Cash added | Average after | Rise needed to average | % of portfolio |
|---|---|---|---|---|---|---|---|---|
| 1 | 21.60 | −10% | 100 | 500 | 2,161.00 | 29.2800 | 35.6% | 14.06% |
| 2 | 19.20 | −20% | 100 | 600 | 4,082.00 | 27.6017 | 43.8% | 15.23% |
| 3 | 16.80 | −30% | 100 | 700 | 5,763.00 | 26.0600 | 55.1% | 15.85% |
| 4 | 14.40 | −40% | 100 | 800 | 7,204.00 | 24.6038 | 70.9% | 15.89% |
The average falls from 31.20 to 24.60, but 7,204.00 of new money goes in, and at the last step the price must rise 70.9% to get back to the new average. The weight crosses the example's 15% limit at step 2 (highlighted), even though the price, and so the value of every share already held, has fallen. The ladder assumes the cash was already part of the portfolio and that other holdings did not move.
Other things the example shows
- Stock B was bought in seven equal-sized lots and is now 20.2% of the portfolio because it rose. The check flags it above the 15% limit: concentration can come from gains as well as from adding to losers.
- Stock C pays 4.95 per trade: its average with fees (59.1390) is above the average without fees (59.0400). Selling 30 of 100 shares realised 602.88 and left the average unchanged.
- Stock D was averaged down once and then fully sold: shares 0, realised −614.00. It drops out of the weights.
- Bank E uses fractional shares and a 0.1% sell fee, so its break-even (40.9622) sits slightly above its average (40.9213).
Average cost and US taxes
The average in this file is a tracking number. It is not automatically your tax basis. IRS Publication 550 (2025), under Identification not possible:
"Except for certain mutual fund shares, discussed later, you cannot use the average price per share to figure gain or loss on the sale of the shares."IRS Publication 550 (2025), Investment Income and Expenses
The average basis method is available when the shares are identical, held with a custodian or agent, and are mutual fund (regulated investment company) shares or shares held in connection with a dividend reinvestment plan (DRIP), under the conditions Pub. 550 lists. For shares you cannot identify, the same section says "the basis of the securities you sell is the basis of the securities you acquired first." Pub. 550 also says the basis of stock is "generally the purchase price plus the costs of purchase, such as commissions and recording or transfer fees", which is why the template carries a with-fees average. For your return, use the cost basis your broker reports on Form 1099-B, and see the cost basis spreadsheet for lot-by-lot tracking.
Common mistakes
- Treating a lower average as lower risk. The amount you can lose on the position goes up with every share you add.
- Typing sells as negative shares. Pick Sell and type a positive number. The Check column flags anything else.
- Entering trades out of order. The running average depends on the order. A sell entered before the buys it came from is flagged as more than the shares held.
- Ignoring fees on small trades. A 4.95 fee on a 300 trade is 1.65% of the money. Compare the averages with and without fees.
- Expecting the broker to show the same cost. Brokers usually report first-in first-out or specific-lot cost for stocks, so after a partial sell their figure for the remaining shares can differ from the average-cost figure here.
- Planning a ladder without the cash. The ladder shows the cash each step needs. Make sure it does not come from money you need for something else.
- Forgetting the current price. Without it the position has no market value, no weight and no unrealised P/L. The Summary reminds you.
Limits
The spreadsheet does arithmetic on the numbers you enter. It does not know why a price fell or whether it will recover. It does not track dividends, stock splits, return of capital, currency conversion or wash sales; enter split-adjusted shares and prices yourself. Prices are typed in, not live. The concentration check uses one position at a time and does not see overlap with funds that hold the same company. The tax note is general US information, not tax advice.
FAQ
How do you calculate the average price when you average down?
Add up what you paid for every share you still hold, then divide by the number of shares. Example: 100 shares at 50 and 100 shares at 30 cost 8,000 for 200 shares, an average of 40. In Excel that is SUMPRODUCT(shares, prices) / SUM(shares) when there are no sells. Add buy commissions to the cost if you want the average with fees. Once you sell part of a position, a running calculation like the one in this template is easier, because each sell removes cost as well as shares.
How many shares do I need to buy to lower my average cost?
Shares needed = (S × A + F − S × T) / (T − P), where S is the shares you hold, A your average cost, T the target average, P the price you buy at and F the fee for the buy. It only works when the buy price is below the target and the target is below your current average. Round up to whole shares. In the fictional example, 400 shares at an average of 31.1975 need 80 more shares at 24 (1,921 in cash with a 1 fee) to reach an average of 30.00.
Does selling shares change my average cost?
Not under the average-cost method used here. A sale removes cost in proportion to the shares sold, so the shares you keep have the same average as before. Selling 100 of 400 shares at an average of 33.26 leaves 300 shares at 33.26. What changes is your realised profit or loss. Brokers that use first-in first-out or specific lots will show a different cost for the shares left.
Should commissions be included in the average cost?
For a true break-even, yes. Buy fees raise what each share cost you, and sell fees reduce what you get back. IRS Publication 550 says the basis of stock is generally the purchase price plus the costs of purchase, such as commissions. The template shows the average with and without fees and a break-even price that also includes your estimated sell fee.
Is averaging down a good idea?
It depends on why the price fell, and no spreadsheet can tell you that. Averaging down lowers the price you need to break even, but it puts more money into one holding that is already losing. FINRA calls this concentration risk: the risk of amplified losses from holding a large portion of your portfolio in one investment. Check how big the position becomes, and how far the price must rise, before you add more.
Can I use my average cost for taxes on stocks?
Usually not for ordinary stock in the US. IRS Publication 550 says that, except for certain mutual fund shares, you cannot use the average price per share to figure gain or loss on a sale. The average basis method is for mutual fund shares and certain dividend reinvestment plan (DRIP) shares. For other shares you cannot identify, the basis is that of the shares bought first. Use the cost basis on your broker's Form 1099-B.
Related tools and templates
- Stock average calculatorQuick average price in the browser.
- Cost basis spreadsheetLot-by-lot cost basis and gains.
- Stock portfolio trackerAll holdings, value and returns.
- DCA trackerEqual buys on a schedule.
- Portfolio rebalancing spreadsheetBack to your target weights.
- Position size calculatorSize a position from the risk you accept.
- All templatesFree spreadsheets for investing and trading.
Educational template only, not investment, tax or legal advice. All example names, prices and trades are fictional. Quotes from FINRA, Investor.gov (U.S. SEC) and IRS Publication 550 (2025) were retrieved 14 Nov 2026.