Home › Templates › Tax-loss harvesting spreadsheet

Tax-Loss Harvesting Spreadsheet

A free tax-loss harvesting spreadsheet for US investors that lists your tax lots, ranks every unrealized loss from biggest to smallest, marks each lot short-term or long-term, flags possible wash sales within 30 days before or after a sale, and nets your gains and losses the way Schedule D does, including the $3,000 limit and the carryover.

Download Excel template (.xlsx) 286 KB · no macros · Excel 2016+ and Google Sheets · v1.0, 30 Oct 2026

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, no broker connection.

US federal rules only. The sheet follows IRS Publication 550, Publication 544, Topic 409 and the Schedule D (Form 1040) instructions. It does not cover state tax or other countries' rules.

Preview: the Example Candidates tab. It shows 8 fictional lots valued on 2 Nov 2026 (tickers TKR-A to TKR-E, FUND-X and FUND-Y; all prices are made up). Lots with a loss are listed biggest loss first.
No.TickerBuy dateSharesCost basisMarket valueUnrealized lossTermDays to long-termWash-sale watchHarvest?Cumulative
1TKR-A2024-03-1240$7,200.00$6,052.00−$1,148.00Long-term0Recent buy: check−$1,148.00
2TKR-B2025-12-01120$4,560.00$3,582.00−$978.00Short-term30No recent buy hereYes−$2,126.00
3TKR-D2026-02-18200$3,180.00$2,210.00−$970.00Short-term109No recent buy hereYes−$3,096.00
4FUND-Y2025-11-0350$5,450.00$4,910.00−$540.00Short-term2No recent buy here−$3,636.00
5TKR-A2026-10-1510$1,540.00$1,513.00−$27.00Short-term348No recent buy here−$3,663.00
−$2,515.00short-term unrealized losses
−$1,148.00long-term unrealized losses
$2,990.00loss deducted this year (sales so far)
$1,938.00carryover if the 2 "Yes" lots are sold

What's inside the spreadsheet

TabWhat you typeWhat it calculates
Start hereNothingSteps, the IRS rules used, colour key, limits, disclaimer
LotsAn as-of date (blank = today), then one row per tax lot in a taxable account: ticker, account, buy date, shares, total cost basis, current price, Harvest? (Yes/No). 300 rows.Market value, unrealized gain/loss in $ and %, short- or long-term on the as-of date, days until long-term, loss rank, wash-sale watch, earliest same-ticker rebuy date
CandidatesNothingEvery lot with a loss, sorted biggest loss first, with a running total and totals of short-term and long-term losses
Wash-sale checkSales this year (date sold, ticker or group, shares, date acquired, proceeds, cost basis) and purchases in all accounts (date, ticker or group, shares, account)Gain/loss, term, the 61-day window, shares bought in the window, a "Possible wash sale" flag, estimated disallowed loss and the allowed gain/loss
SummaryFiling status, last year's short-term and long-term carryover, other gains or losses, taxable income (optional)Net short-term (Schedule D line 7), net long-term (line 15), total (line 16), the deduction against ordinary income (line 21), and the short-term and long-term carryover to next year. Two columns: sales so far, and the same plus lots marked "Yes"
Example Lots / Candidates / Wash-sale / SummaryAlready filledFictional data so you can see every formula working
ListsOptional editsFiling statuses and account types for the dropdowns

How to use it

  1. On Lots, leave the as-of date blank to use today, or type a date to plan ahead. Then add one row per lot: each purchase you still hold in a taxable account. Copy the cost basis from your broker's lot detail.
  2. Update the current price column when you review. The grey columns fill in by themselves.
  3. Open Candidates. Your losses are listed biggest first. Check the term and the wash-sale watch for each one.
  4. Type "Yes" in the Harvest? column on Lots for each lot you plan to sell. Summary then shows the effect in its second column.
  5. On Wash-sale check, list every sale you made this tax year on the left. On the right, list every purchase in all accounts: taxable, IRA, Roth IRA and your spouse's. Leave out the purchase of the shares you sold.
  6. On Summary, choose your filing status and type last year's carryovers as positive numbers, from last year's Capital Loss Carryover Worksheet.
  7. Before you file, compare the numbers with your broker's Form 1099-B. Your return is built from the broker forms, not from this sheet.

The IRS rules the sheet uses

Short-term or long-term

IRS Topic 409: "Generally, if you hold the asset for more than one year before you dispose of it, your capital gain or loss is long-term. If you hold it one year or less, your capital gain or loss is short-term."

How to count the days, from Publication 544: "start counting on the day following the day you acquired the property. The day you disposed of the property is part of your holding period." Its example: bought on June 17, 2024 and sold on June 17, 2025 is not longer than 1 year, but sold on June 19, 2025 is. The sheet tests this with sale date > EDATE(buy date, 12). Publication 550 adds that the holding period runs on trade dates: "Do not confuse the trade date with the settlement date."

The wash sale rule

Publication 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:

It goes on: "If you sell stock and your spouse or a corporation you control buys substantially identical stock, you also have a wash sale." The loss is postponed, not erased, except for IRA buys: "add the disallowed loss to the cost of the new stock or securities (except in (4) above)". Item (4) is the IRA and Roth IRA case. "Your holding period for the new stock or securities includes the holding period of the stock or securities sold."

When you buy fewer shares than you sold, only part of the loss is disallowed. Publication 550 says to match "the shares bought with an equal number of the shares sold". The sheet estimates this as loss × MIN(1, shares bought ÷ shares sold).

"Substantially identical" is a judgment call. Publication 550: "In determining whether stock or securities are substantially identical, you must consider all the facts and circumstances in your particular case. Ordinarily, stocks or securities of one corporation are not considered substantially identical to stocks or securities of another corporation." The IRS gives no list of funds that count. The sheet only matches the exact ticker or group label you type. To treat two funds as the same, give both the same group label, for example "SP500". Whether they really are substantially identical is your decision, or your tax professional's.

Crypto: the 2025 Schedule D instructions say "The wash sale rules generally apply to transactions involving digital assets that are also stock or securities for tax purposes (tokenized securities)." For other crypto, check the current IRS guidance. For crypto records, use the crypto tax spreadsheet.

Netting, the $3,000 limit and the carryover

Publication 550 says to "combine your net short-term capital gain or loss (Schedule D (Form 1040), line 7) with your net long-term capital gain or loss (Schedule D (Form 1040), line 15)". Then, from Topic 409: "If your capital losses exceed your capital gains, the amount of the excess loss that you can claim to lower your income is the lesser of $3,000 ($1,500 if married filing separately) or your total net loss shown on line 16 of Schedule D". And: "If your net capital loss is more than this limit, you can carry the loss forward to later years."

Publication 550 on the carryover: "When you carry over a loss, it remains long-term or short-term." And: "use your short-term capital losses first, even if you incurred them after a long-term capital loss." The Summary tab follows the 13-line Capital Loss Carryover Worksheet in Publication 550 line by line, so the carryover comes out split into short-term and long-term.

The formulas, explained

These are the exact formulas from row 7 of each tab. On Lots: A = ticker, C = buy date, D = shares, E = cost basis, F = current price, and C4 = the as-of date used.

FieldFormula
Lots: unrealized gain/loss (J)=IF(OR(I7="",E7=""),"",I7-E7) where I7 = D7*F7
Lots: term (L)=IF(OR(A7="",C7=""),"",IF($C$4>EDATE(C7,12),"Long-term","Short-term"))
Lots: days until long-term (M)=IF(L7="","",IF(L7="Short-term",EDATE(C7,12)+1-$C$4,0))
Lots: loss rank (N)=IF(OR(J7="",N(J7)>=0),"",COUNTIF($J$7:$J$306,"<"&J7)+COUNTIF($J$7:J7,J7))
Lots: wash-sale watch (O)=IF(N7="","",IF(COUNTIFS($A$7:$A$306,A7,$C$7:$C$306,">="&($C$4-30),$C$7:$C$306,"<="&$C$4)-IF(AND(C7>=$C$4-30,C7<=$C$4),1,0)>0,"Recent buy: check","No recent buy here"))
Candidates: row lookup (B) and ticker (C)=IFERROR(MATCH(A7,Lots!$N$7:$N$306,0),"") then =IF($B7="","",IF(INDEX(Lots!$A$7:$A$306,$B7)="","",INDEX(Lots!$A$7:$A$306,$B7)))
Wash-sale: shares bought in window (K)=IF(OR(A7="",B7=""),"",SUMIFS($R$7:$R$306,$Q$7:$Q$306,B7,$P$7:$P$306,">="&I7,$P$7:$P$306,"<="&J7)) where I7 = A7-30, J7 = A7+30
Wash-sale: check (L)=IF(G7="","",IF(G7>=0,"Gain: rule n/a",IF(N(K7)>0,"Possible wash sale","No buy in window")))
Wash-sale: est. disallowed loss (M)=IF(G7="","",IF(AND(L7="Possible wash sale",N(C7)>0),-G7*MIN(1,K7/C7),0))
Summary: net short-term (B16)=SUM(B12:B15): sales after wash-sale adjustment, other short-term, minus last year's short-term carryover, plus planned harvest (column C only)
Summary: annual limit (B23) and deduction (B24)=IF($B$4="Married filing separately",1500,3000) and =IF(B22<0,MIN(-B22,B23),0)
Summary: short-term carryover (B36)=MAX(0,B33-B35): worksheet line 5 − line 7
Summary: long-term carryover (B41)=MAX(0,B37-B40): worksheet line 9 − line 12

The wash-sale watch on Lots counts other lots of the same ticker bought in the 30 days up to the as-of date. It leaves out the lot itself, because those are the shares you would sell. The sheet cannot see future purchases. If you sell, do not buy the same ticker in the 30 days after. The "earliest rebuy" column shows the as-of date + 31 days.

Worked example from the sample data

The biggest loss is flagged

TKR-A bought on 12 Mar 2024 shows the biggest loss: 40 shares × $151.30 = $6,052.00 against a $7,200.00 basis, or −$1,148.00. It is flagged "Recent buy: check" because 10 more TKR-A shares were bought on 15 Oct 2026, 18 days before the 2 Nov 2026 as-of date. Selling the old lot now would be a loss sale with a purchase within 30 days before it. Under the Publication 550 matching rule, the loss on 10 of the 40 shares would be disallowed: about $1,148.00 × 10 ÷ 40 = $287.00.

Two days from long-term

FUND-Y was bought on 3 Nov 2025. On 2 Nov 2026 it is short-term. EDATE gives 3 Nov 2026, and the lot becomes long-term on a sale from 4 Nov 2026, which is 2 days away. The sheet highlights short-term lots that are 30 days or less from turning long-term, because the term decides which net the loss goes into.

The wash-sale tab

SoldTickerSharesGain/lossTermBought in windowCheckDisallowedAllowed
2026-02-20TKR-C10$880.00Long-term0Gain: rule n/a$0.00$880.00
2026-03-16TKR-F100−$750.00Short-term100Possible wash sale$750.00$0.00
2026-05-11TKR-G50−$1,200.00Long-term20 (IRA)Possible wash sale$480.00−$720.00
2026-08-24FUND-Z40−$500.00Short-term0No buy in window$0.00−$500.00
2026-09-29TKR-H30$1,350.00Short-term0Gain: rule n/a$0.00$1,350.00
2026-10-06TKR-J200−$3,600.00Long-term0No buy in window$0.00−$3,600.00

The summary

The example uses: filing status Single, a $400.00 short-term carryover from last year, and $85,000.00 taxable income.

LineSales so far+ TKR-B and TKR-D sold
Short-term from sales$850.00$850.00
Short-term carryover from last year−$400.00−$400.00
Planned harvest, short-term$0.00−$1,948.00
Net short-term (line 7)$450.00−$1,498.00
Net long-term (line 15)−$3,440.00−$3,440.00
Net total (line 16)−$2,990.00−$4,938.00
Deducted against ordinary income (line 21)$2,990.00$3,000.00
Short-term carryover to next year$0.00$0.00
Long-term carryover to next year$0.00$1,938.00

The sales already made leave a net loss of $2,990.00, just under the $3,000 limit. Selling the two lots marked "Yes" adds $1,948.00 of short-term loss. Only $10.00 of it raises this year's deduction. The other $1,938.00 carries over to next year as a long-term loss: the worksheet uses the short-term losses first, so what is left over is long-term. A harvest can still make sense in a year like this, since a carryover can offset future gains. But the sheet shows you the split before you sell, not after.

Common mistakes

FAQ

How does tax-loss harvesting work?

You sell an investment that is worth less than you paid, and the realized loss offsets realized capital gains. If losses are more than gains, up to $3,000 a year ($1,500 if married filing separately) can reduce other income. The rest carries over to later years. The loss counts only if the sale is not a wash sale.

What is the wash sale rule?

Under IRS Publication 550, a wash sale occurs when you sell stock or securities at a loss and, within 30 days before or after the sale, you buy substantially identical stock or securities. This also applies when you buy in your IRA or Roth IRA, or when your spouse buys. The loss is disallowed for now and usually added to the basis of the new shares.

How much capital loss can I deduct per year?

IRS Topic 409 says the amount you can claim is the lesser of $3,000 ($1,500 if married filing separately) or your total net loss on Schedule D line 16. Any net loss above that can be carried forward to later years. The carryover keeps its short-term or long-term character.

Does buying in an IRA trigger a wash sale?

Yes. Publication 550 lists acquiring substantially identical stock "for your individual retirement arrangement (IRA) or Roth IRA" within the 30-day window as a wash sale. In that case the disallowed loss is not added to the basis of the IRA shares. That is why the purchases list in the sheet covers all accounts.

Do short-term or long-term losses get used first?

On Schedule D, short-term results are netted on line 7 and long-term results on line 15, and then the two are combined on line 16. For the carryover, Publication 550 says to use your short-term capital losses first, even if they came after a long-term loss. The Summary tab follows that worksheet, so you can see which part carries over.

Can I use this tax-loss harvesting spreadsheet in Google Sheets?

Yes. In Google Sheets choose File → Import → Upload and pick the .xlsx, or upload it to Drive and open it with Google Sheets. The formulas use functions that work in Excel 2016+, Google Sheets and LibreOffice: EDATE, COUNTIFS, SUMIFS, INDEX and MATCH.

Limits

Related tools and templates

Educational template only, not tax, legal or investment advice. It applies US federal rules as stated in IRS Publication 550 (2025), Publication 544 (2025), Topic 409 and the 2025 Instructions for Schedule D (Form 1040), retrieved on 30 October 2026. Tax rules change, and whether two securities are substantially identical depends on your facts. Check the current IRS publications or ask a tax professional before you act. Example data is fictional: the tickers TKR-A to TKR-J and FUND-W to FUND-Z and all prices and results are made up.