Drawing Option Payoff Diagrams in Excel

This is part 5 of the Option Payoff Excel Tutorial, which will demonstrate how to draw an option strategy payoff diagram in Excel.

In the previous four parts we have explained option profit or loss calculations and created a spreadsheet that calculates aggregate P/L for option strategies involving up to four legs. For example, the screenshot below shows an iron condor made on strikes 45/50/55/60 with 5 contracts each.

We will use these calculations to create a payoff diagram, which is a graph that shows how an option strategy’s profit or loss changes based on underlying price.

In order to be able to draw the graph, we need to calculate the P/L for different levels of underlying price. We will do this right below our existing P/L calculations. Let’s put the underlying price (the chart’s X-axis) in column B, starting in row 12. In columns C-G we will calculate P/L for the individual legs and for the entire position, just as we do in row 9, but this time we will use the different underlying prices in column B.

Underlying Price Range

One decision we need to make is the range of underlying prices that our diagram will cover. To make our spreadsheet good for different underlying securities with different price levels, we should make the range dynamic – for example, for options on a stock trading at 15 it would probably be enough to display a range from 0 to, let’s say, 30, but for another stock trading in the triple digits we will need a much wider range.

We will make the underlying price range easy to change by setting up two cells for user input – chart start in cell I5 and chart increment in cell I6, as the screenshot below shows. Cell B12, which will be the first point on the chart’s X-axis, will be set equal to cell I5:


Cell B13, which will be the second point on the chart’s X-axis, will equal the first point plus the increment. The formula in cell B13 is:


Make sure to use relative reference for the first point (B12) and absolute reference for the increment ($I$6). This will enable us to copy the formula from cell B13 to the cells below it. Each subsequent row will show underlying price higher than the previous one, with the increment set in cell I6.

Copy cell B13 to 48 following rows – cells B14 to B61. Then test your formulas by changing the chart settings in cells I5-I6 and make sure column B is showing the underlying prices that you expect. For example, this is chart start set to 10 and increment set to 2.5:

P/L at Different Underlying Prices

Now we have X-axis ready and can calculate the payoff at each point. Of course, the formulas in all rows will be the same – we will create them in the first row (row 12) and then copy them to the other rows.

We can use the formulas which we already have in rows 8 and 9, but will have to make a few adjustments. The formula in cell C12 (P/L of leg 1 for the first point on the X-axis) is:


You can see that it is almost like the formula we already have in cell C8:


… only with the following differences:

Now we can copy the formula that we have created in cell C12 to all other cells in the C12-F61 range. For each of the four legs, we have calculated profit or loss for all 50 points on the chart’s X-axis.

To calculate total P/L for the entire position for each point, copy the formula from cell G9 (sum of the four legs’ P/L) to cells G12-G61 (no adjustments needed in this case).

Creating the Payoff Chart

Now we can just create a standard line chart with values range G12-G61 and labels range B12-B61. It will show the payoff diagram for our strategy. We can control the underlying price range (effectively zoom in or out) by changing the chart settings in cells I5-I6.

We can also display payoff diagrams for individual legs – in such case the chart series value range will be C12-C61, D12-D61 etc., but labels range is always the same: B12-B61.

Next Steps

There is of course plenty of room for improvement in terms of layout and visual design – you can change the colors or locations of different parts to adjust the spreadsheet to your preferences, you can make the chart bigger and more prominent etc. We will briefly discuss these topics in the final part of the tutorial, but before we get to that, let’s add some more useful features.

In next two parts of the tutorial, we will look at the calculation of maximum profit, maximum loss and risk-reward ratio.

Go to next part: Calculating Option Strategy Maximum Profit and Loss

Related pages

delta gamma theta vegahow to calculate mean deviation in excelcalculating variance excel13f filing requirementsinverse etf s&pcboe indiceslogarithm calculator step by stepnotepad settingsoverbought oversoldrsi conditiondelta of put option formulacall option payoff graphoptions straddle calculatorhow to figure out standard deviation on exceloption straddle strategysharpe ratio rangecalculation of market capitalizationstandard deviation calculator with mean and sample sizersi stock meaningoptions on vixblack scholes options pricing modelvxx optionstandard error of the mean formula excelwhat is a straddle optionhow to calculate implied volatilityformula for finding median in statisticsstrike price exercise pricemeaning of derivatives in share marketoption theta formulatop options trading bookspreferred equity formulablack schole equationvariance equals standard deviationbull put spread strategynikkei index definitioncboe comoptions yahoo financeetf correlation calculatorstandard deviation derivationcalculate standard deviation excelexcel calculate medianformulas on profit and losswhat is percent deviationcalculate median excelprice weighted indexesnotepad color codessharpe ratio calculatorrsi indicator interpretationuvxy tickerformula for calculating average in excelvariance formula financekurtosis skewema or smagamma black scholesformula for standard deviation in excelannualized volatilitywhat does oversold mean in stocksnormsdist calculatorvix optionnormdist function in excelstandard deviation and variance for grouped datakurtosis calculatorhow to calculate averages in excelthinkback thinkorswimgeometric rate of return calculatortvix etnwhats arithmeticdiv yield formularules of multiplying square rootsweighted average cost of capital calculator excelrules of multiplying square rootsoptions calculator spreadsheetcalculate ss variance and standard deviations&p 500 spreadsheetblack scholes formula put optionstock market breadthvix options expirationformula for implied volatility