Stock Break Even Work Sheet.

This is something I worked out a while back.  Oddly I couldn't find an easy formula to plug in for excel, so I had to create one.  Recently somebody asked me for it, so I wanted to post it here so I could reference it.  The reason for creating this is I was trying to buy some ford stock at a 1.60 a share and wanted to know what the break even price was if I had to bale out of it.  Of course I didn't bail out and today it's a $9 something a share, "Quality is job 1"; Ford made my year !

Basically I needed a formula that would help me calculate how much a stock must rise in price to cover my commissions and taxes and break even.

There is some really good reading here in this book, Page 116, "Calculating the Stock Break Even Sales Price", my formula is adapted from that, good book to read.

http://books.google.com/books?id=YmZV-Jxt28kC&pg=PA114&lpg=PA114&dq=break+even+formula+for+selling+stocks&source=bl&ots=dR84EvLZGH&sig=dL-LdOG4oEptNx1wrrW9xaisjUY&hl=en&ei=NUGxSeHSAo3Btgequ-TDBw&sa=X&oi=book_result&resnum=1&ct=result#v=onepage&q=&f=false

Technincally my formula is not 100% accurate as depending on your particular tax situation the rate can can change between 15% or 28% and there is some "cost basis" versus "individual lots".  The forumula is just meant to be used as a guide, your actual mileage may vary.

I've updloaded the excel spreadhseet here.
Stock_Break_Even_Price.xls (22.5 KB)

Here is the formula, surprisingly my Algebra was terrible and it took me awhile to solve this.

Variable Description
 
N # of Shares
B  Buy Price per Share
C1  Buy Commission
C2 Sell Commission
P  Purchase Cost (Cash out)
X Sell Price Per Share
T Total Cash In
TR  Tax Rate
TX Total Taxes
G Gain
 Calculate Gain

Example Ford Stock 1.60 per share, 100 shares, 10 commission for both buy and sell
 
Description   Formulas                          Example
Cost            (N*B) + C1 = P                 (100 * 1.6) + 10 = 170
Gross           (N*X) - C2 = T                 (100 * X) - 10 = T
Taxes           ((N*X) - (N*B))*TR=TX     (( 100 * X) - (100 * 1.6)) * .15 = TX
Break Even    T-P-TX=0                        ((100 * X) - 10) - 170 - ((( 100 * X ) - (100 * 1.6)) * .15) = 0
                                                        ((100 * X) - 10) - ((( 100 * X ) - (100 * 1.6)) * .15) = 170
 How to solve for x ?                            (100X - 10) - ((100X  - 160) * .15) = 170
                                                        (100X - 10) - (15X  + 24) = 170
                                                        100X - 10 - 15X  + 24 = 170
                                                        100X  - 15X = 156
                                                        85X = 156
                                                        X=1.835294118