Skip to main content

Avoiding Wash Sales: A Smart Date Check for Your Stock Trading Spreadsheet

Avoiding Wash Sales: A Smart Date Check for Your Stock Trades

Why You Should Watch the Wash-Sale Rule

In stock trading, one of the most important tax considerations is the Wash-Sale Rule, enforced by the IRS. This rule disallows claiming a loss on a security if you repurchase the same or a "substantially identical" security within 30 days before or after the sale.

For traders who "short their own shares"—meaning they sell stocks at a loss with the intention of repurchasing later—this rule can be a tax trap. If you don’t track your trades carefully, you might accidentally repurchase the same security too soon, voiding your ability to deduct the loss.

To prevent this, I use a formula that checks whether the trade date is at least 30 days past the sale date before allowing a repurchase. If not, it returns "Hold" to prevent premature trading.

note: I use a spreadsheet for trading stocks and dictates when to make a move

Google Sheets Formula for Date Check

In Google Sheets, you can use the following formula to ensure you don't repurchase shares within the restricted 30-day window:

Google Sheets (A2 has today's Date and it must be greater than the Date of the Sale + 30):
=IF(A2>=DATE(Year,Month,Day)+30,  
   IF(AND(INDIRECT("P"&ROW())<>"Sleeping", INDIRECT("P"&ROW())<>"LOADING", INDIRECT("P"&ROW())<>"eR", INDIRECT("P"&ROW())<>"sleeping"),
    IF(INDIRECT("P"&ROW())<>"Locked",
        IF(AND(INDIRECT("D"&ROW())="SELL",INDIRECT("I"&ROW())="Partially Executed"),
            IF(INDIRECT("P"&ROW())<INDIRECT("G"&ROW()),"Buy","Watching"),
            IF(AND(INDIRECT("D"&ROW())="BUY",INDIRECT("I"&ROW())="Open"),
                IF(INDIRECT("P"&ROW())<INDIRECT("F"&ROW()),"Buy","Watching"),
                IF(AND(INDIRECT("D"&ROW())="BUY",INDIRECT("I"&ROW())="Partially Executed"),
                    IF(INDIRECT("P"&ROW())<>"CLOSED",
                        IF(INDIRECT("P"&ROW())>INDIRECT("G"&ROW()),"Sell","Watching"),
                        "sleepiNG"
                    ),
                    IF(AND(INDIRECT("D"&ROW())="Sell",INDIRECT("I"&ROW())="Open"),
                        IF(INDIRECT("P"&ROW())>INDIRECT("F"&ROW()),"Sell","Watching"),
                        "Not Watching"
                    )
                )
            )
        ),
        "Watching"
    ),
    "sleepinG"
  ),
  "Hold"
)

How It Works:

  • Checks if the date in column A is at least 30 days before today (TODAY()-30).
  • If it's too soon, it returns "Hold", stopping any trade action.
  • If it's past 30 days, it proceeds with my trade logic, determining whether to Buy, Sell, or Watch based on trading conditions.

Excel Version of the Formula

For those who use Excel, the formula requires slight modifications because Excel doesn't support INDIRECT() the same way Google Sheets does. Here’s how you can achieve the same logic:

Excel, A2 holds the Days date and it must be greater than the date of sale + 30:
=IF(A2>DATE(YEAR,MONTH,DAY)+30,   
   IF(AND(P2<>"Sleeping", P2<>"LOADING", P2<>"eR", P2<>"sleeping"),
    IF(P2<>"Locked",
        IF(AND(D2="SELL",I2="Partially Executed"),
            IF(P2<G2,"Buy","Watching"),
            IF(AND(D2="BUY",I2="Open"),
                IF(P2<F2,"Buy","Watching"),
                IF(AND(D2="BUY",I2="Partially Executed"),
                    IF(P2<>"CLOSED",
                        IF(P2>G2,"Sell","Watching"),
                        "sleepiNG"
                    ),
                    IF(AND(D2="Sell",I2="Open"),
                        IF(P2>F2,"Sell","Watching"),
                        "Not Watching"
                    )
                )
            )
        ),
        "Watching"
    ),
    "sleepinG"
  ),
  "Hold"
)

Key Differences in Excel

  • Instead of INDIRECT("P"&ROW()), just use P2 (or whatever row you're working with).
  • Excel handles references more directly, so no need for INDIRECT() in most cases.

Why This Formula is Useful

  • Prevents Wash Sales – Ensures you don’t mistakenly buy a stock you just sold at a loss too soon.
  • Automates Decision-Making – Instead of manually checking sale dates, this formula automatically filters out trades that could trigger a wash sale.
  • Keeps Your Trading Organized – Helps you follow a disciplined strategy rather than making emotional decisions.

Final Thoughts

If you trade actively and track your stocks using spreadsheets, this formula is a powerful tool to help you stay compliant with the Wash-Sale Rule while managing losses effectively. Automating these checks allows you to trade with confidence, knowing that you won’t run into issues when filing your taxes.

For long-term traders, this may not be a huge concern, but for those who make frequent trades, avoiding wash sales can save you money and keep your tax records clean.

Disclaimer:

This blog post is for informational purposes only and should not be considered tax or financial advice. While the formulas help track wash sales, tax laws can be complex, and individual circumstances may vary. Consult a qualified tax professional or financial advisor before making trading decisions. The author is not responsible for any financial losses or tax implications resulting from the use of these formulas. 

Labels:

Popular posts from this blog

How to Add Beneficiaries on E*TRADE Without Losing Your Mind

“Because your money should go where you want it, not where the probate court thinks it should, I am sharing this information.” Ah, E*TRADE. The place where your money grows, your trades execute (sometimes), and your hopes for financial freedom flutter like a candlestick chart on a volatile Thursday. But what happens if you kick the bucket before you get that Tesla stock to moon? Simple: you assign a beneficiary. Unfortunately, E*TRADE doesn’t make this as intuitive as you might think. This isn’t a “click here and boom, you’re immortal” situation. But fear not, fellow capitalist. I’ve braved the pixelated jungle so you don’t have to. 🛠️ Step-by-Step: Setting a Beneficiary for Your E*TRADE Brokerage Account (aka “How to ensure your money doesn’t end up in your ex’s lap or your neighbor's GoFundMe”) Log in at etrade.com . (Obvious, yes. But worth saying—this isn’t Webkinz, you need the real site.) At the top, click “Accounts” and select your Brokerage Account . (The on...
Read more

NJ's Middle-Class Squeeze: Too Much for Help, Not Enough for Comfort

This is a long post — longer than what I usually write — because what I’m talking about here isn’t a small annoyance or a passing frustration. It’s something that has been building for years, and I’m finally putting it all into words. I’m upset, I’m exhausted, and I’m passionate about what follows, because it affects every working person in this state who’s trying to stay afloat. There’s a growing group in New Jersey — people who work full‑time, sometimes more than one job, who earn too much to qualify for assistance but not enough to absorb the constant increases in living costs. These are the people tightening their budgets, lowering their thermostats, cutting back wherever they can, and still watching their bills rise for reasons that have nothing to do with their own usage or behavior. If you’re part of that group, or you know someone who is, then what follows will probably resonate with you. And if you’re not, then I hope this gives you a clearer picture of what the middle class i...
Read more

Understanding Treasury Bond Auctions: The Difference Between High Yield and Interest Rate

Treasury bonds are a popular choice for investors looking for a reliable source of income backed by the U.S. government. However, understanding how these bonds are priced at auction can be confusing, especially when comparing the High Yield and the Interest Rate (Coupon Rate) columns. In this post, I'll break it down using a real-world example.  A Look at a Recent Treasury Bond Auction Here’s an example of a 20-year Treasury bond that was recently auctioned: Security Term CUSIP Reopening Issue Date Maturity Date High Yield Interest Rate 20-Year 912810UF3 Yes 01/31/2025 11/15/2044 4.900% 4.625% What Do These Numbers Mean? CUSIP : This is a unique identifier for the bond. Reopening : Since it says "Yes," this means the bond was originally issued earlier and is now being reoffered. Issue Date : January 31, 2025—this is when the bond will be offi...
Read more