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:
| A | B | C | D | E | F | |
| 1 | Shares | Purchase Price | Total Purchase | Current Price | Current Value | Current Gain/Loss |
| 2 | 60 | 65.26 | 3,915.60 | 20.79 | 1,247.40 | (2,668.20) |
| 3 | 20 | 17.26 | 345.20 | 20.79 | 415.80 | 70.60 |
| 4 | 75 | 14.37 | 1,077.75 | 20.79 | 1,559.25 | 481.50 |
| 5 | 100 | 6.95 | 695.00 | 20.79 | 2,079.00 | 1,384.00 |
| 6 | ||||||
| 7 | Total Gains | 1,936.10 | ||||
| 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.
Download the example file here.
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.