Stock Trader break even formula needed

[MaxxABillion]

I am a new trader and I need an excel formula that will tell me the break even stock price I need to hit before I start taking profits. Below is the first example of a scenario

i) I buy 2000 units of Coca Cola Amatil Ltd (ASX:CCL) at $11.83/unit = $23,660
ii) My transactional costs to buy and sell is $9.99 each way, therefore a total of $19.98
iii) Calculating my break even costs:
($23,660+$19.98)/2000 = $11.84/unit
iv) Therefore I MUST sell my CCL stocks for at least $11.84/unit to break even.
If I sell for anything greater than $11.85/unit then I've made a profit, which is known as capital gains.


The second scenario applies to short selling a stock. Keeping with the same Coca Cola Scenario, the only change I will make is, instead of adding $19.98, I would subtract it from the total cost. This will give me the price that the stock would have to go down to in order for me to break even.

i) I sell 2000 units of Coca Cola Amatil Ltd (ASX:CCL) at $11.83/unit = $23,660
ii) My transactional costs to buy and sell is $9.99 each way, therefore a total of $19.98
iii) Calculating my break even costs:
($23,660-$19.98)/2000 = $11.82/unit
iv) Therefore I MUST sell my CCL stocks for at least $11.82/unit to break even.
If I sell for anything lower than $11.82/unit then I've made a profit, which is known as capital gains.

What I need is two separate formulas that I can use to calculate my break even points. Any help that you all can provide will be greatly provided.

 

[Taisho]

I'm quite sure that the transactional costs that you have mentioned are only the minimal ones and transactional costs in general will be based on % of your transactions' value.

 

[MaxxABillion]

I'm quite sure that the transactional costs that you have mentioned are only the minimal ones and transactional costs in general will be based on % of your transactions' value.

You maybe right, but as of now, i'm only factoring in just the standard transactional cost.

 

[Taisho]

Price per share Number of shares Transaction amount One way transactional cost Transaction cost per share Break even per share (buy) Break even per share (sell) 10 100 1000 9,99 0,1998 10,1998 9,8002 <tbody> </tbody>

<tbody>
</tbody>

Transaction cost per share =(2*D2)/B2
Break even per share (buy) =A2+E2

Break even per share (sell) =A2-E2

<tbody>
</tbody>

 

[MaxxABillion]

Thanks, I have my formula dialed in, but I have a odd question.

Is it possible to populate different formulas based on a selection made in a row? For example, if I want to use the formula for going long on a trade, I would select the drop down box, select "long", and that entire row would contain my formulas for going long. On the contrary, if I want to short a stock, In the drop box, I would select "short" and it would populate my formulas for shorting.

Is this at all possible in excel, and if so, can someone point me in the right direction to do so.

 

[needtoknownow]

You will need to use the "if statement"

=IF(A2="long","long formula goes here","short formula goes here")

 

[MaxxABillion]

You will need to use the "if statement"

=IF(A2="long","long formula goes here","short formula goes here")

Got it, i'll try it out. If i run into any issues, i will let you know