![]() It minimizes the cost due to premium by writing a call option of same/similar premium.Īgain, your data needs to look like this –Įnter the max profit, max loss, breakeven and profit formulae for the long put and short call as shown in the previous sections. The Collar is basically a combination of a covered call and a protective put. It is meant to prevent excessive losses, but also restricts excessive gains. It is implemented by purchasing a put option, writing a call option, and being long on a stock. Options Trading Excel CollarĪ collar is an options strategy which is protective in nature, which is implemented after a long position in a stock has proved to be profitable. Stock Price = Strike Price – Premium on call – Premium on putįinally, the overall profit is just the sum of profit on call + profit on put. Stock Price = Strike Price + Premium on call + Premium on Put So just enter the formula =C6+F6 into C13 Max Loss = Premium on Call + Premium on Put Notice that there are two break-even stock prices.įirst, enter the same formulas for the Long Call and Long Put as we did in the previous sections. This can be implemented before a major news announcement which is likely to have a substantial impact on the value of a stock.Ĭreate a table-like structure as shown below – This is implemented when you expect the stock to change significantly in the near future, but are unsure of which direction it will swing. Options Trading Excel StraddleĪ Straddle is where you have a long position on both a call option and a put option. So just enter the following formula into cell J12 –Ĭreate similar worksheets for Bull Put Spread, Bear Call Spread and Bear Put Spread. Overall Profit = (Profit for long call) + (Profit for short call). Max Loss = (Premium for long call) – (Premium for short call)īreak-Even Stock Price = (Strike Price for long call) + (Premium for long call) – (Premium for short call) Max Profit = (Strike Price for short call) – (Strike Price for long call) – (Premium for long call) + (Premium for short call) Now, for the third table, where we calculate the overall profit/loss, Implement the same formulas which you implemented for Long Call and Short Call. It is implemented when you are feeling bullish about a stock.Ĭreate a table structure like the one in the image below. Options Trading Excel Bull Call SpreadĪ Bull Call Spread is implemented when a call is bought at a lower strike price and another call is shorted with a higher strike price. Now go ahead and implement Covered Put and Protective Call on your own. Make a similar table in another spreadsheet just as above.Įnter the following formula to calculate profit –Īlternatively you can also use the IF function for this. Profit = Strike Price – Current Stock Price – Premium If Stock Price at Expiration < Strike Price Then Profit = Stock Price at Expiration – Current Stock Price – Premium If Stock Price at expiration > Strike Price Then The Breakeven Price = Current Price + Premium The max loss = Strike Price – Current Stock Price – Premium A protective put is implemented when you are bullish on a stock, but want to protect yourself from losses in case the stock price decreases. Profit = Strike Price – Current Stock Price +PremiumĮlse If Stock Price at expiration C6,C6-C4+C7,C5-C4+C7)Īlternatively, you can also use the formula –Ī protective put involves going long on a stock, and purchasing a put option for the same stock. ![]() Profit = Price at Expiration–Strike Price–Premium The Break-Even price would be equal to the Strike Price plus the Premium.Īnd, if the Price at Expiration > Strike Price Then, If you go buy a call option, then the maximum loss would be equal to the Premium but your maximum profit would be unlimited. 4 Options Trading Excel Bull Call Spread.In this article you will learn how to create your own excel spreadsheet for analysing option strategies. ![]() If you want to analyse the payoff vs risk for each of them, it becomes cumbersome and tiring to calculate the max profit/max loss for each option/strategy. But in any exchange there are many options are available with different prices and different strike rates. are the ways in which you can make money and limit risk. Some of the strategies like covered call, protective put, bull call spread, etc. They provide many ways to protect and hedge your risks against volatility and unexpected movements in the market. Options are sophisticated derivatives of stock/stock indices that constitute a major part in any exchange. His hobbies include maths and music.Options trading Excel calculator gives you Profit/Loss and Payoff analysis of different options strategies. Jayantha has been selected as Campus Ambassador at AlgoJi- 2017.
0 Comments
Leave a Reply. |