Options Trading Excel Calculator
[box type=”bio”] Jayantha has been selected as Campus Ambassador at AlgoJi- 2017. He is pursuing B.Tech. + M.Tech. (Dual Degree) from IIT BHU. His hobbies include maths and music.[/box]Options trading Excel calculator gives you Profit/Loss and Payoff analysis of different options strategies.
Options are sophisticated derivatives of stock/stock indices that constitute a major part in any exchange. They provide many ways to protect and hedge your risks against volatility and unexpected movements in the market.
Some of the strategies like covered call, protective put, bull call spread, etc. are the ways in which you can make money and limit risk. But in any exchange there are many options are available with different prices and different strike rates. 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.
In this article you will learn how to create your own excel spreadsheet for analysing option strategies.
Contents
Options Trading Excel Long Call
If you go buy a call option, then the maximum loss would be equal to the Premium; but your maximum profit would be unlimited.
The Break-Even price would be equal to the Strike Price plus the Premium.
And, if the Price at Expiration > Strike Price Then,
Profit = Price at Expiration–Strike Price–Premium
If Price at Expiration < Strike Price Then,
Profit = – Premium
Create a table-like structure as shown in the image below –
Since short call, long put and short put are similar, it would be futile to cover that also, so go ahead and implement them on your own in separate spreadsheets.
Options Trading Excel Covered Call
A covered call is when, a call option is shorted along with buying enough stock to cover the call. A covered call is should be employed when you have a short term neutral view on the stock. i.e. if you think that the stock price will not deviate much from the strike price. This way, you will make money on the premium.
A covered call will protect you against rapid increase in stock price. Again make a table similar to the one for Long Call.
Max profit will be realized when the stock price becomes equal to the strike price at the date of expiration of option.
Max Profit = Strike Price – Current Stock Price + Premium
Max Loss occurs when stock price becomes zero at expiration
Max Loss = Current Stock Price – Premium
Breakeven price is the price which is premium less than the current stock price.
Breakeven price = Current Stock Price – Premium
If Stock Price at expiration > Strike Price Then
Profit = Strike Price – Current Stock Price +Premium
Else If Stock Price at expiration < Strike Price Then
Profit = Stock Price at Expiration – Current Stock Price + Premium
So, to calculate the Profit enter the following formula into Cell C12 –
=IF(C5>C6,C6-C4+C7,C5-C4+C7)
Alternatively, you can also use the formula –
=MIN(C6-C4+C7,C5-C4+C7)
Options Trading Excel Protective Put
A protective put involves going long on a stock, and purchasing a put option for the same stock. 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.
The max profit is unlimited.
The max loss = Strike Price – Current Stock Price – Premium
The Breakeven Price = Current 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
Profit = Strike Price – Current Stock Price – Premium
Make a similar table in another spreadsheet just as above.
Enter the following formula to calculate profit –
=MAX(C5-C4-C7,C6-C4-C7)
Alternatively you can also use the IF function for this.
Now go ahead and implement Covered Put and Protective Call on your own.
Options Trading Excel Bull Call Spread
A 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. It is implemented when you are feeling bullish about a stock.
Create a table structure like the one in the image below.
Implement the same formulas which you implemented for Long Call and Short Call.
Now, for the third table, where we calculate the overall profit/loss,
Max Profit = (Strike Price for short call) – (Strike Price for long call) – (Premium for long call) + (Premium for short call)
Max Loss = (Premium for long call) – (Premium for short call)
Break-Even Stock Price = (Strike Price for long call) + (Premium for long call) – (Premium for short call)
Overall Profit = (Profit for long call) + (Profit for short call).
So just enter the following formula into cell J12 –
=SUM(C12,G12)
Create similar worksheets for Bull Put Spread, Bear Call Spread and Bear Put Spread.
Options Trading Excel Straddle
A Straddle is where you have a long position on both a call option and a put option. This is implemented when you expect the stock to change significantly in the near future, but are unsure of which direction it will swing. This can be implemented before a major news announcement which is likely to have a substantial impact on the value of a stock.
Create a table-like structure as shown below –
Notice that there are two break-even stock prices.
First, enter the same formulas for the Long Call and Long Put as we did in the previous sections.
The max profit is infinity/unlimited.
Max Loss = Premium on Call + Premium on Put
So just enter the formula =C6+F6 into C13
We will Break-Even if either
Stock Price = Strike Price + Premium on call + Premium on Put
OR
Stock Price = Strike Price – Premium on call – Premium on put
Finally, the overall profit is just the sum of profit on call + profit on put.
Options Trading Excel Collar
A 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. It is implemented by purchasing a put option, writing a call option, and being long on a stock.
It is meant to prevent excessive losses, but also restricts excessive gains. The Collar is basically a combination of a covered call and a protective put.
It minimizes the cost due to premium by writing a call option of same/similar premium.
Again, your data needs to look like this –
Enter the max profit, max loss, breakeven and profit formulae for the long put and short call as shown in the previous sections.
Maximum profit is realized when the price reaches up to the Call option strike price, this way, there is no loss due to writing of call option, and we realize a profit because we already hold the stock, whose value has increased.
Max Loss occurs when the stock goes to zero, but our losses are cut short due to our put option, so max loss = Current Stock Price – Strike Price of put option
If the stock price remains the same, we neither gain nor lose, therefore our breakeven price is equal to the current stock price itself.
To calculate the profit enter the following formula into cell C15 –
=(C4+MAX(0,C5-C4)-MAX(0,F4-F5))-C3
Conclusion
Now that you have created your own options trading Excel spreadsheet for options analysis, not only is it easier for you to evaluate different strategies, you have also gained a deeper understanding of the different types of strategies.