Option Strategy Payoff Spreadsheet: Further Improvements

This is the final part of the Option Payoff Excel Tutorial. We have learned how to calculate profit or loss for a single option and for strategies with multiple legs, we have learned how to draw option payoff diagrams in Excel, and calculated maximum profit, maximum loss, risk-reward ratio and break-even points.

Our spreadsheet has become quite a powerful tool to analyze option strategies or potential option trades and their P/L at expiration. That said, there are still things we could possibly improve and in this last section we will discuss some of them.

Unlike the previous parts where you could always find very detailed step-by-step instructions to create all the necessary formulas, in this part I will just present the rough ideas and I will leave the implementation to you. After all, if you have followed the tutorial all the way from the beginning up to this point, you should be able to do most of these adjustments and extensions on your own.

So let’s get to the individual points.

Design and Practical Use

The main focus of the tutorial has been on the hard and important things – mainly the underlying logic and the calculations. We haven’t paid that much attention to visual design, such as location of particular parts, colors or borders. At the moment, while our calculations are ready and correct, the spreadsheet may look a bit messy and disorganized.

Just a few problems that come to mind:

These are just some of the things which we can easily fix and make the spreadsheet much cleaner and easier to use. I won’t be providing detailed instructions, because, firstly, layout and visual design are often matters of personal preference, and, secondly, there is nothing too complicated. Just one hint: When moving cells to a different location, it is better to do so by inserting and deleting cells, rows, and columns, than copy and paste the cells’ content – you won’t break the references in formulas.

The screenshot below is just an example how the spreadsheet can look with a bit of cleanup and formatting.

Some things I have done:

Change your own spreadsheet to your liking and you will find it much easier to use.

Covered Calls and Positions in the Underlying

Another shortcoming of our calculator is not just about design – it’s more a matter of scope and functionality.

Our spreadsheet can work with any combination of up to four options and it can be used for modelling many of the common option strategies. However, it can’t be used for covered calls, protective puts, collars, and generally positions which besides call or put options also involve a long or short position in the underlying security. These are some of the most popular option strategies!

There are two main ways to fix this.

The quick fix is to enter a position in the underlying as a call option with strike price set to zero, because mathematically the profit/loss profile is exactly the same. For example, the screenshot below shows a covered call position.

The position includes long 500 shares of the underlying stock, bought for $48 (leg 1) and short 5 contracts of $50 strike calls, sold for $3.50. All the results are correct, including the break-even point at $44.50, but one problem with this solution is that you must enter the long stock position size in option contract equivalents (5 for 500 shares in our example), because the calculator treats it as a call option.

A better solution would be to set up a third security type in addition to “Call” and “Put” – we could name it “Stock” or “Underlying”. It would require more changes to our spreadsheet – particularly to the combo boxes and all the formulas which calculate individual leg P/L in columns C-F (you would need to include some more IF functions there).

Automating the Chart’s X-axis

When using the spreadsheet for different underlyings and different option strategies, it won’t take long until you find it quite annoying that you have to manually change the Chart start and Chart increment setting every time. Although our existing solution is much better than having to change the chart’s X-axis the default Excel way (using Format Axis), it is still far from ideal. It would be nice if the scale changed automatically every time we change some of the input parameters.

Unfortunately, although definitely possible, this is much harder than it would seem, especially when you also want the tick marks and labels on the X-axis to be nice round numbers (so the axis goes something like 45, 50, 55 etc. and not 45.31, 49.18, 53.05 etc.).

It would require quite a lot of new calculations and/or macros. An example of how it can be done is available in the Option Strategy Payoff Calculator, where I have set up an entire sheet for these calculations, named “Auto X” (the screenshot below shows just about a half of all the calculations needed).

What Happens before Expiration?

Our spreadsheet can do a lot of interesting things analyzing option strategies as they would be at expiration. However, in practice many positions are closed long before the options expire. Often a trader sets up a position without the intention of holding until expiration, but with expectations of a particular move in underlying price or implied volatility, and gets out as soon as these expectations materialize (or are proven wrong).

In such cases, there are other things to watch and other statistics to calculate that are much more important than payoff at expiration, such as the Greeks (delta, gamma, theta, vega, and more). You may also want to calculate expected profit or loss and break-even points at some point in the future, but before expiration, when the options still have time value.

These calculations are much more complicated than the ones we have seen in this tutorial. One reason is that we can’t rely on the assumption that the P/L function is linear. We have to worry about things like convexity, implied volatility, probability distributions and option pricing models. These are beyond the scope of this tutorial, but you can find other tutorials on the sesyixo.ru website – a good place to start is the Black-Scholes option pricing model.

The End

That’s it for the Option Payoff Excel Tutorial – I hope you have found it useful.

If you have any feedback, questions or suggestions, please contact me.

You may also want to check the Option Strategy Payoff Calculator, where you can see the ideas and calculations in action, and find some more explanation in the calculator’s user guide.

Go to Option Strategy Payoff Calculator

Related pages

how to calculate arithmetic densitydelta gamma vegastraddle strategy in optionsweighted averages formulawhat is the formula for variance in excelblack scholes implied volatilityblack scholes calculator onlinecoefficient of skewness exampleblack scholes explainedsample variance formula statisticscontango vixpopulation calculator statisticsfutures expiration calendarsquaring numbers in excelvix futures term structurepayoff diagrams for call and put optionsstandard deviation calculator financeformula for variance in excelvix historical prices3x etfs listwhen do vix options expireema excelcboe indeximplied volatility wikicalculating standard deviation in exceletf etchow to calculate using excel formulahow to calculate annual rate of return in excelblack schole option pricing modelpercentile calculator mean sddelta hedging explaineddelta gamma vegasd formula in excelxxv etncosting accounting formulasblack scholes value calculatoroptions delta gamma theta vega rhorsi calculatore-tracsexcel sharpe ratioannualizing volatilitycapitalization weightedexcel calculator formulakurtosis statisticsinstrict valuepopular stock indexesweighted average accounting formulablack-scholes option valuation modelinverse vix etf 2xoptions straddle strategyoptions futures and other derivatives 8thindexation calculationformula for calculating average in exceltvix tickers&p 500 futures bloombergticker vixfutures contract multiplierhistorical volatility calculationeft listetf short s&p 500rsi formulacboe options calculatorcalculating mean excelcovariance formula in excelrsi formula excelmacd crossoversultra etf13f analysisuvyxexcel emadelta hedging put optionfutures vs options differencequote cboevix options chainadvantages of moving averagesexcel formula for variancemethods of measuring skewnessblack & scholes modelcalc standard deviation