Recently I found myself deciding if I should sell some stock in order to provide a little extra cash to my bank account. I purchased shares of the company’s stock four times in various quantities, and at varying prices. Due to the downturn in the stock market recently the prices dropped steadily, so I was able to purchase some of the shares at a low price. The company has done a little better in recent months and has produced a gain for some of the shares I bought at the lower price.
The question I want to solve is: if I sell all the shares that have produced a gain, how many of the shares should I sell at a loss in order to offset the gains, and the resulting taxes?
To do this, I set up my spreadsheet like the one below:
|1||Shares||Purchase Price||Total Purchase||Current Price||Current Value||Current Gain/Loss|
|8||Losses to Break Even||–|
|9||Shares to be Sold to Break Even|
Note: these are not the actual amounts I used in my own personal situation. They are only provided for illustration purposes.
In cell F8 (labeled “Losses to Break Even” in cell E8), I have the following formula: =(F9*B2)-(F9*D2), which I want to equal the total gains amount, of $1,936.10. Right now it is showing a zero value because I haven’t determined how many shares need to be sold at a loss to equal the gains.
To do this, select cell F8, then click Tools | Goal Seek. In the box that pops up, enter the following:
- Set cell: Keep as F8. If it isn’t F8, enter F8 here.
- To value: Enter 1,936.10, as this is the value we wish to set the losses to.
- By changing cell: Enter cell F9, as you want to determine how many shares need to be sold in order to break even.
- Click OK
The result will tell you that you should sell 43.54 shares in order to break even.
For more information on Excel’s Goal Seek feature, take a look at the post Goal Seek to Solve a Problem.