Excel Zoom

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:

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 G8 (labeled “Total Losses” in cell F8), I have the following formula: =(G10*C3)-(G10*E3), 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 G8, then click Tools | Goal Seek.  In the box that pops up, enter the following:

• Set cell: Keep as G8.  If it isn’t G8, enter G8 here.
• To value: Enter 1,936.10, as this is the value we wish to set the losses to.
• By changing cell: Enter cell G9, 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.