Breakeven Formula In Excel?

[shua101]

Hi,

I am curious to see a breakeven formula in excel to help me know how many shares of a stock I have to sell to cover the original cost and the taxes from the gain (taxes are 35%)

My goal is to quickly see how many shares I can keep of a stock after I take out the original amount it took to buy plus the taxes on the gains...

Example: I bought 100 shares xyz at $1.00, it then went to $3.00, how many shares do I have to sell to cover the original $100 investment plus the taxes from selling the shares (taxes are 35% on the $2.00 profit from the shares appreciating).

Thanks!

 

[TheNoocH]

welcome to the board...if i'm following correctly see if this is what you are after

Excel Workbook
	A	B
1	shares	100
2	cost per share	1
3	total cost	100
4
5	current cost per share	3
6	profit per share	2
7	tax	0.35
8	Tax on profit	0.7
9	Total Cost + Tax	100.7
10	# shares to break even	33.56667
Sheet4

Excel 2007

Cell Formulas
Range	Formula
B3	=+B1*B2
B6	=+B5-B2
B8	=+B6*B7
B9	=+B3+B8
B10	=+B9/B5

or to simplify...could do this...

Excel Workbook
	A	B	C	D
1	Original # shares	Original Cost Per Share	Current Cost Per Share	Tax Rate
2	100	$1	$3	35%
3
4	# Shares at current cost to break even
5	33.57
Sheet4

Excel 2007

Cell Formulas
Range	Formula
A5	=(A2+((C2-B2)*D2))/C2

 

[gauntletxg]

Excel Workbook
	A	B
1	Shares	100
2	NBV	1
3	FMV	3
4	Appreciation	2
5	Taxes	70
6	Total Investment	170
7	Need to sell	56.67
Sheet1

 

[TheNoocH]

Forgot to multiply the tax on profit by # of shares in original post...

Excel Workbook
	A	B	C	D
1	Original # shares	Original Cost Per Share	Current Cost Per Share	Tax Rate
2	100	$1	$3	35%
3
4	# Shares at current cost to break even
5	56.67
Sheet4

Excel 2007

Cell Formulas
Range	Formula
A5	=(A2+((C2-B2)*D2*A2))/C2

 

[shua101]

Hi,

Thanks for the prompt response!

If I invested $100 to buy the 100 shares, and I sell 57 shares at $3 I get $171 back and incur $40 in taxes ($114 in profit * .35)...doesn't that give me $131 left over (I need to sell the amount get to $100 after taxes)?

Am I missing something?

Thanks!

 

[shua101]

I see the correct answer as 44 shares, not 56 or 34 shares as presented below... please advise:


<table style="border-collapse: collapse; width: 288px; height: 262px;" border="0" cellpadding="0" cellspacing="0"><col style="width: 61pt;" width="81"> <col style="width: 42pt;" width="56"> <tbody><tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt; width: 61pt;" width="81" height="20">Shares</td> <td class="xl65" style="border-left: medium none; width: 42pt;" width="56" align="right">100</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="border-top: medium none; height: 15pt;" height="20">Bought @</td> <td class="xl66" style="border-top: medium none; border-left: medium none;"> $ 1 </td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="border-top: medium none; height: 15pt;" height="20">Sell @</td> <td class="xl66" style="border-top: medium none; border-left: medium none;"> $ 3 </td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="border-top: medium none; height: 15pt;" height="20">Profit</td> <td class="xl66" style="border-top: medium none; border-left: medium none;"> $ 2 </td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="border-top: medium none; height: 15pt;" height="20">Sell Qty @ 3</td> <td class="xl65" style="border-top: medium none; border-left: medium none;" align="right">44</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="border-top: medium none; height: 15pt;" height="20">Sell Val</td> <td class="xl67" style="border-top: medium none; border-left: medium none;"> $ 132 </td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="border-top: medium none; height: 15pt;" height="20">Profit</td> <td class="xl67" style="border-top: medium none; border-left: medium none;"> $ 88 </td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="border-top: medium none; height: 15pt;" height="20">Taxes</td> <td class="xl66" style="border-top: medium none; border-left: medium none;"> $ 31 </td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="border-top: medium none; height: 15pt;" height="20">Net</td> <td class="xl66" style="border-top: medium none; border-left: medium none;"> $ 101 </td> </tr> </tbody></table>
I just need a formula to help me get to the 44 shares without the guess work

Thanks!

 

[wsjackman]

Excel Workbook
	G	H	I	J	K
14	purchase	1		100	original
15	sell	3		44	sell
16	tax	0.35
Sheet10

 

[shua101]

Brilliant wsjackman!

Thanks so much!!!