# Calculate Wash Sale (Stock Transactions)

#### Dustinkli

##### Board Regular
Good day,

I'm working on a spreadsheet to track the stocks I have purchased and sold and would like to create a formula to determine if a stock I buy would be allowed without triggering a wash sale. Basically my spreadsheet will look like this:
 Date​ Transaction​ Symbol​ Price​ 12/1/2021​ Bought MSFT \$320.00​ 12/1/2021​ Bought ASO \$50.00​ 12/1/2021​ Sold NVDA \$310.50​ 12/3/2021​ Bought HIBB \$68.04​ 12/7/2021​ Bought AAPL \$170.47​ 12/8/2021​ Bought PL \$11.05​ 12/10/2021​ Sold MSFT \$342.55​ 12/10/2021​ Sold ASO \$44.43​ 12/15/2021​ Sold AAPL \$160​

Where if I type in a stock ticker, for instance AAPL, it will tell me if I sold it at a loss in the past 30 days. In this case AAPL would be a wash sale. If I type in MSFT it would not be a wash sale since I sold it for a profit.

So I am trying to create a formula that looks at the current date and then looks in Column A and goes back 30 days then looks at the purchase price of the tickers and the sold price and determines if it was sold at a profit or at a loss in the past 30 days.

I'm not quite sure where to start on this formula. Any ideas?

### Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.

#### Dustinkli

##### Board Regular
I have come up with this formula:

Excel Formula:
``IF(SUMIFS(D2:D10, C2:C10, F2,B2:B10,"Bought")>SUMIFS(D2:D10, C2:C10, F2,B2:B10,"Sold"),"Wash Sale", "No Wash Sale")``

which disregards the date column, but it's a start sort of. Though I haven't tested it yet to see if it works properly or if it has flaws in it.

#### Dustinkli

##### Board Regular
One flaw I've identified is that if I purchased a stock prior to the 30 days I look back at and sell part of it at a loss then part of it as a gain and the gain is higher overall than the 30 day cost basis then it will count it as not a wash sale even though it is a wash sale.

Example:

 Date Transaction Symbol Price 12/1/2021​ Bought MSFT \$320.00​ 12/1/2021​ Bought ASO \$50.00​ 12/1/2021​ Sold NVDA \$310.50​ 12/3/2021​ Bought HIBB \$68.04​ 12/7/2021​ Bought AAPL \$170.47​ 12/8/2021​ Bought PL \$11.05​ 12/10/2021​ Sold MSFT \$342.55​ 12/10/2021​ Sold ASO \$44.43​ 12/15/2021​ Sold AAPL \$160​ 12/20/2021​ Sold AAPL \$170.00​

In this case if I look at AAPL, Say I bought 10 shares of AAPL 35 days ago for \$150 and then on 12/7/2021 I bought 10 shares for \$170 and on the 12/15/2021 I sell 10 shares of AAPL for \$160 which results in a wash sale. Then on 12/20/2021 I sell the other 10 shares of AAPL for \$170. With my formula it would say it's NOT a wash sale since the sale price is higher than the buy price.

Any ideas on resolving this?

Replies
13
Views
679
Replies
1
Views
255
Replies
19
Views
390
Replies
1
Views
702
Replies
2
Views
1K

1,191,709
Messages
5,988,236
Members
440,139
Latest member
ngaicuong2017

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back