Home › Templates › Cost basis spreadsheet
Cost Basis Spreadsheet: Stock and ETF Lot Tracker
A free cost basis spreadsheet for Excel and Google Sheets. Log every buy, reinvested dividend, fee and stock split, then see the basis, the realized gain and the short- or long-term split of each sale by FIFO, average cost or the specific lot you sold, with a Form 8949 box and a wash-sale screen for every line.
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 add-on and no broker connection: you type your trades from the confirmations.
| Ticker | Sold | Shares | Method | Net proceeds | Basis used | Gain / loss | Term | Acquired | 8949 box | Wash-sale screen |
|---|---|---|---|---|---|---|---|---|---|---|
| STOCKA | 8 Apr 2025 | 120 | FIFO | 6,607.05 | 5,046.93 | 1,560.12 | Both: report 2 lines | VARIOUS | A/D | |
| FUNDXC | 21 Apr 2025 | 150 | Average cost | 3,855.00 | 3,719.69 | 135.31 | Long-term | 10 Jan 2024 | D | |
| STOCKC | 12 Sep 2025 | 40 | FIFO | 1,315.05 | 749.99 | 565.06 | Long-term | 17 May 2010 | E | |
| STOCKB | 20 Oct 2025 | 60 | Specific lot (STOCKB-2) | 4,320.00 | 4,800.00 | −480.00 | Short-term | 15 Jan 2025 | A | Possible wash sale: 25 shares bought within 30 days |
| ETFXXD | 20 Jan 2026 | 80 | FIFO | 5,072.00 | 4,896.00 | 176.00 | Long-term | 6 Jan 2025 | D | |
| FUNDXC | 17 Feb 2026 | 120 | Average cost | 2,880.00 | 2,979.63 | −99.63 | Long-term | VARIOUS | D | Possible wash sale: 4.3 shares bought within 30 days |
| STOCKA | 2 Mar 2026 | 30 | FIFO | 1,494.00 | 1,562.97 | −68.97 | Long-term | 3 Sep 2024 | D | Loss: no same-ticker buy within 30 days in this file |
| STOCKB | 15 Jun 2026 | 50 | Specific lot (STOCKB-1) | 4,050.00 | 3,500.00 | 550.00 | Long-term | 6 Nov 2023 | D |
Planning a tax-loss sale instead? The tax-loss harvesting spreadsheet starts from the total basis your broker shows for each open lot and looks forward: unrealized losses, harvest candidates, Schedule D netting and the $3,000 limit. This cost basis spreadsheet works out that basis from your trades and looks back: what each sale cost, by which method, and which Form 8949 box it belongs in.
What's inside the spreadsheet
| Tab | What you type | What it calculates |
|---|---|---|
| Start here | Nothing | Steps, the IRS rules used, method limits, colour key, disclaimer |
| Lots | Up to 300 rows: ticker, trade date, type (Buy or Reinvested dividend), security type (Stock, ETF, Mutual fund, DRIP shares), shares, price, fees, other basis adjustment (+/−), notes | Lot ID, a row check, total cost basis, split factor, split-adjusted shares, basis per share, the date after which the lot is long-term, shares left and basis left in the lot |
| Sales | Up to 300 rows: ticker, trade date, shares, price, selling fees, method (FIFO, Specific lot, Average cost), lot ID for a specific-lot sale, whether the 1099-B shows basis reported to the IRS | Status, cost basis used, gain or loss, short-term / long-term / both, date acquired (or VARIOUS), Form 8949 box for each part, wash-sale screen, method warnings, and the FIFO and average basis of every sale side by side |
| Splits | Up to 50 splits or reverse splits: ticker, effective date, new shares for each old share | Converts earlier lots and sales to today's share count; dollar basis never changes |
| Summary | Optional tax year | Short- and long-term totals, wash-sale flags and data checks for the year; proceeds, basis and gain per Form 8949 box A–F; a table and native chart by tax year; per ticker: shares held, basis left, basis per share and realized gain by the method used, if all FIFO and if all average |
| Example Lots, Example Sales, Example Splits, Example Summary | Already filled | A fictional portfolio: 21 lots (9 of them reinvested dividends), one 3-for-1 split and 8 sales across 2025 and 2026 |
How to use it
- Open Lots. Add one row per purchase and one per dividend reinvestment, in any order. Copy the trade date, shares, price and commission from each confirmation. Mark the security type: Stock, ETF, Mutual fund or DRIP shares.
- If a statement shows a basis change, type it in Other basis adjustment: plus for a disallowed wash-sale loss added to replacement shares, minus for a nondividend distribution (return of capital). Check column K says OK.
- Had a split? Add it once on Splits with the effective date and the ratio (3 for 3-for-1, 0.1 for 1-for-10). Do not edit the old lots; the file converts them.
- Open Sales. Add each sale in date order for that ticker: trade date, shares, price and selling fees. Pick the method. For a specific-lot sale, pick the lot ID your broker confirmed. Answer Basis reported to IRS? from your Form 1099-B (Yes, No, or No 1099-B).
- Check column J says OK. Read the basis used, the gain, the term and the Form 8949 box. An amber cell means check this: a possible wash sale, a sale with both short- and long-term shares, or a method warning.
- Open Summary. Type the tax year (or leave it blank for the latest year). Compare the box A–F totals with your 1099-B before you file.
The IRS rules the sheet uses
What goes into basis
IRS Publication 551: "The basis of stocks or bonds you buy is generally the purchase price plus any costs of purchase, such as commissions and recording or transfer fees." (IRS, Pub. 551, Basis of Assets). Publication 550 adds that the basis "must be adjusted for certain events that occur after purchase", such as stock splits and nondividend distributions (IRS, Pub. 550, Investment Income and Expenses).
FINRA puts the investor's side plainly: the cost basis "is generally your purchase price for the securities, including reinvested dividends or reinvested capital gains distributions, plus additional costs such as the commission or other fees you paid", and "You're responsible for reporting your cost basis information accurately to the IRS" (FINRA, Cost Basis Basics).
Which shares you sold: three methods
| Method | When the IRS allows it | How the sheet does it |
|---|---|---|
| FIFO | The default. Pub. 550: if you "cannot adequately identify the shares you sell, the basis of the securities you sell is the basis of the securities you acquired first." | Lines each ticker's lots up by trade date and takes shares from the front |
| Specific lot | When you adequately identify the shares: you "Tell your broker or other agent the particular stock to be sold or transferred at the time of the sale" and "Receive a written confirmation of this" within a reasonable time. | Uses the basis per share of the lot ID you pick and checks the lot has enough shares left |
| Average cost | Only for identical shares left with a custodian or agent that are mutual fund (or other regulated investment company) shares or DRIP shares, after you elect it in writing with the custodian. Pub. 550: "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." | Divides the basis of all shares held by the number held, just before each sale. Warns on lots marked Stock (and asks you to check an ETF's structure) |
Under average cost, the holding period still follows the oldest shares. Pub. 550: "To determine your holding period, the shares disposed of are considered to be those acquired first." So one average-cost sale can have short- and long-term parts, as in its LJO Mutual Fund example. The sheet splits it the same way.
Use one method per ticker. The FIFO and average columns are what-ifs that assume every sale of that ticker used that method, and a note appears if one ticker mixes methods.
Short-term or long-term
"If you hold investment property more than 1 year, any capital gain or loss is a long-term capital gain or loss. If you hold the property 1 year or less, any capital gain or loss is a short-term capital gain or loss." Counting begins "on the date after the day you acquired the property", and for exchange-traded securities the period runs from the day after the trade date you bought to the trade date you sold (Pub. 550). The Lots tab shows the cut-off for each lot: STOCKA-2, bought 3 Sep 2024, is long-term if sold after 3 Sep 2025.
Stock splits and reinvested dividends
Pub. 550 tells you to figure the basis of stock splits "in the same way as stock dividends if identical stock is distributed", dividing the old basis over the old and new shares. A dividend reinvested through a plan is new stock with its own basis and holding period, and "you must still report the dividends as income." If the plan sells you shares at a discount, "your basis is the full fair market value of the stock on the dividend payment date."
Covered, noncovered and the Form 8949 boxes
The Form 8949 instructions: "Generally, a covered security is any of the following. Stock you acquired after 2010 (generally after 2011, if in a mutual fund or other regulated investment company, or acquired through a DRP)." For covered sales the broker reports basis to the IRS; "If Form 1099-B ... shows that the cost or other basis was reported to the IRS, always report the basis shown on that form" and make any correction in column (g) (IRS, Instructions for Form 8949).
- Short-term goes on Part I: box A (basis reported to the IRS), B (basis not reported) or C (no Form 1099-B).
- Long-term goes on Part II: box D, E or F on the same logic. Boxes G–L are for digital assets on Form 1099-DA and are not used here.
- For a block bought on several dates you "may report the sale on one row and enter "VARIOUS" in column (b). However, you must still report the short-term gain (or loss) on the sale on Part I and the long-term gain (or loss) on Part II." The sheet's Both: report 2 lines flag and the two box columns do that split.
Wash sales: a screen, not a calculation
Pub. 550: "A wash sale occurs when you sell or trade stock or securities at a loss and within 30 days before or after the sale you" buy substantially identical stock or securities, acquire them in a fully taxable trade, acquire a contract or option to buy them, or acquire them for your IRA or Roth IRA. A purchase by your spouse or a corporation you control also counts. The disallowed loss is added to the basis of the new shares.
The sheet flags a loss sale when this file has same-ticker shares bought within 30 days before or after it, other than the shares sold. Reinvested dividends count, because they are purchases. It cannot see your other accounts, IRAs or your spouse's trades, or decide what is "substantially identical", and it does not change any basis. Treat a flag as a reason to check your 1099-B and, for confirmed amounts, use the Other basis adjustment column and code W on Form 8949.
The formulas, explained
Exact formulas from the file, row 8. On Lots, B is the ticker, C the date, F the shares, G the price, H the fees, I the adjustment, K the row check. On Sales, A is the ticker, B the date, C the shares and F the method; the grey helper columns from AC onwards hold the intermediate steps.
| Field | Formula |
|---|---|
| Lots: total cost basis (L8) | =IF($K8="OK",F8*G8+N(H8)+N(I8),"") |
| Lots: split factor since bought (M8) | =IF($K8="OK",EXP(SUMIFS(Splits!$F$8:$F$57,Splits!$A$8:$A$57,B8,Splits!$B$8:$B$57,">"&C8)),"") |
| Lots: shares today, split-adjusted (N8) | =IF($K8="OK",F8*M8,0) |
| Lots: basis per split-adjusted share (O8) | =IF($K8="OK",L8/N8,0) |
| Lots: long-term if sold after (P8) | =IF($K8="OK",EDATE(C8,12),0) |
| Lots: FIFO queue start (R8) | =IF($K8="OK",SUMIFS($N$8:$N$307,$B$8:$B$307,B8,$Q$8:$Q$307,"<"&Q8,$W$8:$W$307,1),0) |
| Sales: shares sold, split-adjusted (AE8) | =IF(AC8="OK",C8*AD8,0) |
| Sales: FIFO start in the ticker's queue (AF8) | =IF(AC8="OK",SUMIFS($AE$8:$AE$307,$A$8:$A$307,A8,$AT$8:$AT$307,"<"&AT8,$AC$8:$AC$307,"OK"),0) |
| Sales: FIFO basis (U8) | =IF(AC8="OK",SUMPRODUCT((Lots!$W$8:$W$307=1)*(Lots!$B$8:$B$307=$A8)*(((($AG8)+(Lots!$S$8:$S$307)-ABS(($AG8)-(Lots!$S$8:$S$307)))/2-(($AF8)+(Lots!$R$8:$R$307)+ABS(($AF8)-(Lots!$R$8:$R$307)))/2)+ABS((($AG8)+(Lots!$S$8:$S$307)-ABS(($AG8)-(Lots!$S$8:$S$307)))/2-(($AF8)+(Lots!$R$8:$R$307)+ABS(($AF8)-(Lots!$R$8:$R$307)))/2))/2*Lots!$O$8:$O$307),"") |
| Sales: average basis (V8) | =IF(AC8="OK",IF(AM8-AN8>0.000001,AE8*(AL8-AO8)/(AM8-AN8),0),"") |
| Sales: specific-lot basis (AQ8) | =IF(N(AP8)>0,AE8*INDEX(Lots!$O$8:$O$307,AP8),0) |
| Sales: cost basis used (K8) | =IF(J8<>"OK","",IF(F8="FIFO",U8,IF(F8="Average cost",V8,AQ8))) |
| Sales: gain or loss (L8) | =IF(J8<>"OK","",S8-K8) |
| Sales: short / long-term (M8) | =IF(J8<>"OK","",IF(AS8<=0.000001,"Long-term",IF(AS8>=AE8-0.000001,"Short-term","Both: report 2 lines"))) |
| Sales: Form 8949 box, short-term part (O8) | =IF(OR(J8<>"OK",AS8<=0.000001),"",IF(H8="Yes","A",IF(H8="No","B",IF(H8="No 1099-B","C","?")))) |
| Sales: shares bought in the 61-day window (AX8) | =IF(AC8="OK",SUMIFS(Lots!$N$8:$N$307,Lots!$B$8:$B$307,A8,Lots!$W$8:$W$307,1,Lots!$C$8:$C$307,">="&(B8-30),Lots!$C$8:$C$307,"<="&(B8+30)),0) |
| Sales: wash-sale screen (Q8) | =IF(OR(J8<>"OK",N(L8)>=0),"",IF(AX8-AY8>0.000001,"Possible wash sale: "&ROUND(AX8-AY8,4)&" shares bought within 30 days","Loss: no same-ticker buy within 30 days in this file")) |
| Summary: short-term gain, year shown (B12) | =SUMIFS(Sales!$Y$8:$Y$307,Sales!$AU$8:$AU$307,$B$5) |
| Summary: Form 8949 box A proceeds (C24) | =SUMIFS(Sales!$W$8:$W$307,Sales!$O$8:$O$307,"A",Sales!$AU$8:$AU$307,$B$5) |
Splits without editing old rows. Each lot gets a split factor: the product of the ratios of every later split of that ticker, written as EXP(SUMIFS(LN ratios)) because spreadsheets have no PRODUCTIFS. Shares × factor gives today's share count. Sales get the same factor for splits after the sale date, so every share in the file is counted in the same units and the dollar basis never moves.
FIFO without macros. Each ticker's lots form a queue ordered by trade date: lot i holds positions from start to start + shares (column R). Each sale takes the next positions after the shares sold before it. The FIFO basis is the overlap of the sale's range with each lot's range, times that lot's basis per share, summed with SUMPRODUCT. MIN and MAX are written with ABS (min(x,y) = (x + y − |x − y|) / 2) because they do not work element by element inside SUMPRODUCT. The same overlap gives the short-term shares, the lots used and the shares bought in the wash-sale window.
Average cost. For each sale: (basis of all shares bought up to the sale date − basis already sold under average) ÷ (shares bought − shares already sold) × shares sold. This is the IRS worksheet in Pub. 550: total adjusted basis of the shares owned just before the sale, divided by the number of shares owned.
Checked against the IRS examples. With Pub. 550's Table 4-2 data (160 shares at $25, 240 at $20, 10 reinvested at $30; 210 sold) the sheet gives a FIFO basis of $5,000, as the IRS does, and an average basis of $4,660.98. The IRS rounds the average to $22.20 a share and gets $4,662. Its LJP fund example (an average of $16, 150 shares sold) gives $2,400. Its holding-period dates (31 Jan 2024 to 29 Jan 2025 short-term, to 6 Feb 2025 long-term), its split example ($45 basis, three shares, $15 each) and its wash-sale example (replacement shares with a $1,050 basis) all come out the same. check.py recomputes every value with a separate Python simulation.
Worked example from the sample data
The Example tabs hold a fictional portfolio: three stocks, one mutual fund with quarterly reinvested dividends and one ETF. None of the tickers, prices or dates are real.
| Lot ID | Acquired | Type | Security | Shares | Price | Fees | Cost basis | Shares today | Basis / share | Long-term if sold after | Shares left |
|---|---|---|---|---|---|---|---|---|---|---|---|
| STOCKC-1 | 17 May 2010 | Buy | Stock | 40 | 18.50 | 9.99 | 749.99 | 40 | 18.7497 | 17 May 2011 | 0 |
| STOCKB-1 | 6 Nov 2023 | Buy | Stock | 30 | 210.00 | 0.00 | 6,300.00 | 90 | 70.0000 | 6 Nov 2024 | 40 |
| FUNDXC-1 | 10 Jan 2024 | Buy | Mutual fund | 200 | 25.00 | 0.00 | 5,000.00 | 200 | 25.0000 | 10 Jan 2025 | 0 |
| STOCKA-1 | 12 Feb 2024 | Buy | Stock | 100 | 40.00 | 4.95 | 4,004.95 | 100 | 40.0495 | 12 Feb 2025 | 0 |
| FUNDXC-2 | 28 Mar 2024 | Reinvested dividend | Mutual fund | 3.912 | 25.56 | 0.00 | 99.99 | 3.912 | 25.5600 | 28 Mar 2025 | 0 |
| FUNDXC-3 | 27 Jun 2024 | Reinvested dividend | Mutual fund | 4.118 | 24.89 | 0.00 | 102.50 | 4.118 | 24.8900 | 27 Jun 2025 | 0 |
| FUNDXC-4 | 15 Jul 2024 | Buy | Mutual fund | 100 | 24.10 | 0.00 | 2,410.00 | 100 | 24.1000 | 15 Jul 2025 | 38.03 |
| STOCKA-2 | 3 Sep 2024 | Buy | Stock | 50 | 52.00 | 4.95 | 2,604.95 | 50 | 52.0990 | 3 Sep 2025 | 0 |
| FUNDXC-5 | 26 Sep 2024 | Reinvested dividend | Mutual fund | 5.6 | 25.92 | 0.00 | 145.15 | 5.6 | 25.9200 | 26 Sep 2025 | 5.6 |
| FUNDXC-6 | 27 Dec 2024 | Reinvested dividend | Mutual fund | 5.55 | 26.71 | 0.00 | 148.24 | 5.55 | 26.7100 | 27 Dec 2025 | 5.55 |
| ETFXXD-1 | 6 Jan 2025 | Buy | ETF | 80 | 61.20 | 0.00 | 4,896.00 | 80 | 61.2000 | 6 Jan 2026 | 0 |
| STOCKB-2 | 15 Jan 2025 | Buy | Stock | 20 | 240.00 | 0.00 | 4,800.00 | 60 | 80.0000 | 15 Jan 2026 | 0 |
| FUNDXC-7 | 27 Mar 2025 | Reinvested dividend | Mutual fund | 5.7 | 26.40 | 0.00 | 150.48 | 5.7 | 26.4000 | 27 Mar 2026 | 5.7 |
| STOCKA-3 | 16 Jun 2025 | Buy | Stock | 40 | 47.25 | 0.00 | 1,890.00 | 40 | 47.2500 | 16 Jun 2026 | 40 |
| FUNDXC-8 | 26 Jun 2025 | Reinvested dividend | Mutual fund | 3.9 | 25.10 | 0.00 | 97.89 | 3.9 | 25.1000 | 26 Jun 2026 | 3.9 |
| STOCKB-3 | 4 Aug 2025 | Buy | Stock | 45 | 76.00 | 0.00 | 3,420.00 | 45 | 76.0000 | 4 Aug 2026 | 45 |
| FUNDXC-9 | 25 Sep 2025 | Reinvested dividend | Mutual fund | 4 | 25.60 | 0.00 | 102.40 | 4 | 25.6000 | 25 Sep 2026 | 4 |
| STOCKB-4 | 5 Nov 2025 | Buy | Stock | 25 | 70.00 | 0.00 | 1,750.00 | 25 | 70.0000 | 5 Nov 2026 | 25 |
| ETFXXD-2 | 1 Dec 2025 | Buy | ETF | 20 | 58.00 | 0.00 | 1,160.00 | 20 | 58.0000 | 1 Dec 2026 | 20 |
| FUNDXC-10 | 29 Dec 2025 | Reinvested dividend | Mutual fund | 4.1 | 25.20 | 0.00 | 103.32 | 4.1 | 25.2000 | 29 Dec 2026 | 4.1 |
| FUNDXC-11 | 12 Mar 2026 | Reinvested dividend | Mutual fund | 4.3 | 24.30 | 0.00 | 104.49 | 4.3 | 24.3000 | 12 Mar 2027 | 4.3 |
A FIFO sale with short- and long-term parts
On 8 Apr 2025, 120 STOCKA shares are sold at $55.10 with a $4.95 fee: net proceeds $6,607.05. FIFO takes all 100 shares of STOCKA-1 ($4,004.95, bought 12 Feb 2024, held more than a year) and 20 of STOCKA-2 at $52.0990 a share ($1,041.98, bought 3 Sep 2024). Basis $5,046.93, gain $1,560.12: $1,500.92 long-term in box D and $59.19 short-term in box A. Average cost would have given $5,287.92, but STOCKA is a stock, so average cost is not an option.
A mutual fund on average cost
FUNDXC has two purchases and quarterly reinvested dividends. Just before the 21 Apr 2025 sale the fund lots hold 324.88 shares with $8,056.36 of basis: $24.7980 a share. 150 shares × that average = $3,719.69, against $3,750.00 under FIFO. The shares sold count as the oldest ones (bought 10 Jan 2024), so the gain of $135.31 is long-term. On 17 Feb 2026 a second sale closes at a $99.63 loss, and the dividend reinvested on 12 Mar 2026 (4.3 shares) is within 30 days, so the screen says: Possible wash sale: 4.3 shares bought within 30 days.
A split, a specific lot and a wash-sale flag
STOCKB had a 3-for-1 split effective 1 Jul 2025. STOCKB-2 was 20 shares at $240.00 ($4,800.00); today that is 60 shares at $80.00. On 20 Oct 2025 the investor tells the broker to sell exactly that lot: 60 shares at $72.00 for $4,320.00. Basis $4,800.00, a short-term loss of $480.00. FIFO would have sold the older, cheaper STOCKB-1 shares for a basis of $4,200.00 and a gain. On 5 Nov 2025, 16 days after the loss sale, the investor buys 25 more STOCKB shares (STOCKB-4), so the screen says: Possible wash sale: 25 shares bought within 30 days. Whether it is a wash sale, and for how much, is for the investor and the 1099-B to settle; if it is, Pub. 550 says the disallowed loss is added to the cost of the new shares.
Noncovered shares
STOCKC was bought on 17 May 2010, before covered-security reporting began, and the broker's 1099-B shows no reported basis. The sale on 12 Sep 2025 is long-term and goes in box E: proceeds $1,315.05, basis $749.99 (including the $9.99 commission), gain $565.06. This is the case where your own records carry the basis.
The 2025 summary
| Form 8949 | What goes there | Proceeds | Cost basis | Gain / loss | Lines |
|---|---|---|---|---|---|
| Part I, box A | Short-term, basis reported to IRS | 5,421.18 | 5,841.98 | −420.80 | 2 |
| Part I, box B | Short-term, basis not reported to IRS | 0.00 | 0.00 | 0.00 | 0 |
| Part I, box C | Short-term, no Form 1099-B | 0.00 | 0.00 | 0.00 | 0 |
| Part II, box D | Long-term, basis reported to IRS | 9,360.88 | 7,724.64 | 1,636.23 | 2 |
| Part II, box E | Long-term, basis not reported to IRS | 1,315.05 | 749.99 | 565.06 | 1 |
| Part II, box F | Long-term, no Form 1099-B | 0.00 | 0.00 | 0.00 | 0 |
4 sales in 2025: −$420.80 short-term and $2,201.29 long-term, $1,780.49 in total. If every sale had used FIFO, the total would be $2,350.18; with average cost everywhere, $1,871.80. Those what-ifs include stocks, where average cost is not allowed. They show how much the method moves the result.
Holdings by ticker
| Ticker | Method used | Shares held | Basis left | Basis / share now | Realized gain | If all FIFO | If all average |
|---|---|---|---|---|---|---|---|
| STOCKC | FIFO | 0 | 0.00 | – | 565.06 | 565.06 | 565.06 |
| STOCKB | Specific lot | 110 | 7,970.00 | 72.45 | 70.00 | 470.00 | 214.09 |
| FUNDXC | Average cost | 71.18 | 1,765.14 | 24.80 | 35.68 | 39.04 | 35.68 |
| STOCKA | FIFO | 40 | 1,890.00 | 47.25 | 1,491.15 | 1,491.15 | 1,436.57 |
| ETFXXD | FIFO | 20 | 1,160.00 | 58.00 | 176.00 | 176.00 | 227.20 |
STOCKB now holds 110 split-adjusted shares with $7,970.00 of basis left, $72.45 a share, because the specific-lot sales took the shares named, not the oldest ones.
Common mistakes
- Leaving fees out. Commissions are part of basis on the buy, and selling fees reduce proceeds. Small amounts add up over many lots.
- Forgetting reinvested dividends. Each reinvestment is a purchase with its own basis. Leave them out and you overstate the gain, as FINRA's $1,400-versus-$1,000 example shows.
- Retyping old lots after a split. Keep the original shares and price and add the split once on Splits. Editing both double-counts the split.
- Using average cost for a stock. Pub. 550 allows it only for mutual fund (regulated investment company) and DRIP shares, and only after you elect it with the custodian.
- Picking a lot after the fact. Specific identification needs your instruction at the time of the sale and the broker's written confirmation. Otherwise the shares sold are the first acquired.
- Using settlement dates. Holding periods and the tax year use trade dates. A trade on 31 December belongs to that year even if it settles in January.
- Reading the wash-sale flag as a final answer. It only sees this file. Purchases in IRAs, a spouse's account or a substantially identical fund with another ticker can still create a wash sale.
Limits
- Long positions in one currency. No short sales, options, bonds or digital assets.
- Gifts, inheritances, spin-offs, mergers, return of capital and cash in lieu of fractional shares are not modelled. Enter the basis yourself with the Other basis adjustment column and the right acquisition date.
- One method per ticker. Average cost is shown as a what-if for every sale; use it only where the IRS allows it and you have elected it.
- Wash sales are flagged, not adjusted. The sheet does not compute the disallowed amount or carry it into the replacement lot.
- Up to 300 lots, 300 sales and 50 splits; 20 tickers in the Summary list (a red note appears if there are more). Recalculation was tested in LibreOffice; the file uses only functions that also exist in Excel 2016+ and Google Sheets.
- Your broker's Form 1099-B is the record for covered securities. Use this file to check it and to keep the basis of noncovered shares.
FAQ
How do I calculate the cost basis of a stock?
Start with what you paid and add the costs of buying. IRS Publication 551 says the basis of stocks or bonds you buy "is generally the purchase price plus any costs of purchase, such as commissions and recording or transfer fees." Add reinvested dividends as new purchases, spread the basis over the new share count after a split, and subtract the basis of shares you have sold. The Lots tab does this per lot: shares × price + fees ± adjustments.
Can I use average cost basis for individual stocks?
Generally no. IRS Publication 550 says that if you cannot identify the shares you sold, you use the shares acquired first, and "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." The average basis method is for identical shares held with a custodian or agent that are mutual fund (or other regulated investment company) shares or DRIP shares. The sheet shows a warning if you pick Average cost for a lot marked Stock.
What is the FIFO method for cost basis?
First in, first out: the oldest shares count as sold first. Publication 550 makes it the default when you cannot adequately identify the shares you sold, and FINRA describes it the same way. With specific identification you name the lot at the time of the sale and get a written confirmation from your broker. The Sales tab shows both, plus the average cost figure, so you can compare.
Do reinvested dividends increase my cost basis?
Yes. Each reinvestment buys new shares, and their cost is added to your basis. FINRA's example: a $1,000 investment with $100 and $300 of reinvested dividends has an adjusted cost basis of $1,400, so a $1,500 sale is a $100 gain, not $500. The dividends themselves are still taxable income in the year paid. Publication 550: "you must still report the dividends as income."
How do stock splits affect cost basis?
Your total basis stays the same and is spread over more (or fewer) shares. Publication 550's example: one share bought for $45 becomes three shares after a distribution of two new shares for each share held, so each share has a basis of $15. Enter each split once on the Splits tab and keep the original share counts and prices on Lots.
What is the difference between covered and noncovered securities?
For a covered security your broker reports the basis to the IRS on Form 1099-B. The Form 8949 instructions say a covered security is generally "Stock you acquired after 2010 (generally after 2011, if in a mutual fund or other regulated investment company, or acquired through a DRP)." For noncovered shares you are responsible for the basis yourself, which is where a lot tracker helps most. The answer decides the Form 8949 box: A/D, B/E or C/F.
When is a stock gain long-term?
When you held the shares more than 1 year. Publication 550: "begin counting on the date after the day you acquired the property. The day you disposed of the property is part of your holding period." Its example: bought 31 January 2024, sold 29 January 2025 is short-term; sold 6 February 2025 is long-term. Use trade dates, not settlement dates. The Lots tab shows the date after which each lot turns long-term.
Can I use this cost basis spreadsheet in Google Sheets?
Yes. In Google Sheets choose File → Import → Upload and pick the .xlsx, or upload it to Google Drive and open it with Google Sheets. The formulas use IF, AND, OR, NOT, N, INT, ABS, ROUND, LN, EXP, ISNUMBER, IFERROR, COUNTA, COUNTIF, COUNTIFS, SUMIFS, SUMPRODUCT, INDEX, MATCH, EDATE, YEAR, TODAY, LEN, TRIM, LEFT, SEARCH, MIN, MAX and SUM, which work in Excel 2016+, Google Sheets and LibreOffice. No macros and no dynamic arrays.
Related tools and templates
- Tax-loss harvesting spreadsheetUnrealized losses, harvest candidates, Schedule D netting
- Stock average calculatorAverage price per share across buys
- Stock portfolio trackerHoldings, allocation and returns
- Crypto tax spreadsheetGains and losses on digital assets
- Capital gains tax calculatorEstimate the tax on a gain
- All templatesFree spreadsheets
Educational template only, not tax, legal or investment advice. Example data is fictional: the tickers, prices, dates and trades are made up to show the formulas and are not a recommendation. IRS rules are quoted from Publication 550 (2025), Publication 551 (12/2025) and the Instructions for Form 8949 (2025); FINRA material is quoted from finra.org. All were retrieved on 3 November 2026. Tax rules change; check the current IRS publications or a tax professional for your situation.