Calculate Wash Sale (Stock Transactions)

[Dustinkli]

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?

 

[Dustinkli]

I have come up with this 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]

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?