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

theta financespx index bloombergstandard deviation formula for grouped datacalculating sample meaninferential statistics calculatorleveraged vixlong straddle exampleinstrict valuehow to calculate coefficient of skewnessarbitrage hedge fundsvix trading strategyimplied volatility definitionlogarithm calculator step by stepfloat-adjusted market capitalizationpositive kurtosisdax index methodologyannualized return excelequity hedge fund strategiesmonospace font listtrading using macdyahoo finance indicesipath etfshortcut standard deviation formulaedgar 13fcboe quotefx option pricersharpe ratio rangeannualized daily returnd2 calchistorical stock return calculatorhow to calculate volatility for black scholesblack scholes gammacboe cfevix index chartvariance formula grouped datahedge fund definition exampledefine backwardationshort put option payoffvariance in grouped datacredit suisse nassau branchmonospace font listhow to calculate exponential moving average in excelexcess kurtosisdaily volatility formulaexponential smoothing calculatorformula black scholesblack scholes calculator with dividend yieldblack scholes paperprotective put payoffgood sharpe ratiodelta hedgingprofit loss formula excelvix futures optionsvix futures quotessharpe ratio formula examplefifo lifo avcorho calculationcustomize notepadstock option intrinsic valuewhats a good sharpe ratioimplied volatility calculator excelstandard deviation excel templateweighted average cost of capital wacc formulasec hedge fund filingsoptions futures hullaverage standard deviation calculatorstock market return calculator historicalvxx barclayswhen do vix options expirewhat is macd histogramvolatility index definitiondefinition of sigma in statisticsrho tradingintrinsic value stockskurtosis meaningblack scholes implied volatilitydefine sharpe ratiooption calculator excel