Calculate break even sell price of an asset

pecunianonolet

Hi,

Need some help to calculate the break even price of an asset. Algebra isn't my strongest skill, sadly.

Example:

Trading fees:

Maker (Buy): 0.6%
Taker (Sell): 1.2%

Let's assume I invest $100 and buy an asset with a price of $5 and the fees are 0.6%. I would now be able to buy 19.88 shares. I have to pay a fee of $0.6

Now the share price is rising and I know that when I sell I have to pay 1.2% in fees of the sell price.

I can now manually go and work out what happens when I sell at a given price and can calculate my profit. However, I'd like to find a way to do this in Excel so that I just have to manually type in my buy price and Excel calculates my break even sell price considering buy and sell fees.

I know for example that in the above example I could sell again when the share price has risen to $5.10 and I would make a profit of $0.17 considering buy and sell fees, which equates to a 0.17% gain/profit.

How do I put this in Excel so I can always see my break even price and can capture my trades and their performance that way.

*Ideally CGT would be part of the calculation too but that would make it even more complex so let's ignore that for now.

Any help would be greatly appreciated 

eskbanker

If the fees total 1.8%, then selling at 1.018 * buying price will be a close enough approximation to the breakeven point.

cwep2

So if you invest £100, you get £99.40 worth of shares after the fees. 

When you sell, you pay 1.2% of the total value sold as fees, so receive 100%-1.2% = 98.8% of the proceeds. So to get £100 back, you need to sell £100 / 0.988 = £101.2145 worth of shares. The 1.2% fee of £101.2145 = £1.2145 leaving you with almost exactly £100. There are some extra decimals here which I’ve rounded, but you don’t need to do so in Excel. 

So to break even you need the shares go up by:
(£101.2145 / £99.40 -1) x 100 = 1.8255%. Or multiply the buy price by (£101.2145 / £99.40). 

To keep full precision if buy price is BP, then breakeven price BE can be expressed as:

BE = BP / ((0.988) * (0.994))
or 
BE = BP / ((1 - 1.2%) * (1 - 0.6%))

So if you buy at £5 then BE price is £5.0913.

CGT is paid on gains, so won’t affect the breakeven price, but will reduce gains if sold above the BE price.