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.
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.
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
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