5 stars based on
This is part 4 of the Option Payoff Excel Tutorial. In the previous parts firstsecondthird we have created a spreadsheet that calculates profit or loss for a single call or put option, given the strike price, initial option price and underlying price.
Now we are going to expand it to also work with positions involving multiple options — strategies such as straddlescondorsbutterflies or spreads. Total profit or loss from an option strategy that involves multiple options also called legs equals the sum of profit options payoff spreadsheet loss of all these individual legs. Knowing this will be very helpful options payoff spreadsheet creating our option strategy payoff calculator.
We will do that by expanding our existing spreadsheet and copying the inputs and formulas from column C to three other columns — D, E, F — to get a total of four possible legs for our option strategies. Because columns E-F are currently occupied by the contract size input and the combo box text options payoff spreadsheet, we must move these to the right to make space for the new legs. You can insert new column right before the existing column E by right clicking the label of column E and then selecting Insert from the menu that pops up.
Do this three times to insert three columns. The contract size input and combo box data have shifted to columns H-I. Now we can simply copy the entire column C to columns D, E, F.
The result should look like this:. We must change the I2 to an absolute reference, so the copied cells in columns D, E, F still point to cell I2, the contract size input, which is the same for all legs. I assume most readers are familiar with the difference between absolute and relative cell references — if not Google has plenty of options payoff spreadsheet explanations.
If you click on the I2 part of the formula in cell C9 and press the F4 key on your keyboard, the I2 cell reference will change options payoff spreadsheet relative to absolute, which you will recognize by the dollar signs:. Now you can copy cell C9 to cells D9, E9 and F9 and all these will show correct results for the options payoff spreadsheet legs. One last thing which requires a little fixing is the new combo boxes in cells Options payoff spreadsheet, E3, F3.
We must make sure options payoff spreadsheet of the new combo boxes controls the correct leg, which quite likely is not the case at the moment. This setting decides where the combo box selection will be stored, which options payoff spreadsheet course must match the particular leg, otherwise your combo box would control a wrong leg and the calculations would be options payoff spreadsheet.
Now all the individual legs should have correct calculations — test this by changing the different inputs and combo box selections.
The last step is to calculate total payoff for the entire position, which is just sum of the four legs. We can calculate it in cell G9, using the formula:. At the moment each column has its options payoff spreadsheet underlying price input row 6but this input will always be the options payoff spreadsheet for all legs.
You can change the hard typed values currently 49 in cells D6, E6, F6 to a formula linking to cell C6 and perhaps make the cells green as a options payoff spreadsheet that these should not be changed. Now underlying price, effective for all legs, will be changed in cell C6 only.
Alternatively, you can move the underlying price input somewhere else like we did with contract size in cell I2 ; in such case you will also need to update the formulas in cells C8-F8 to reflect its new location. Just set the position cells C2-F2 to zero for any unused legs as a result, rows 8 and 9 in these columns should also be showing zero. Having started with a very simple calculation in part onenow in part 4 we have created quite an advanced spreadsheet which can calculate profit or loss for any combination of options payoff spreadsheet to four legs and can be used to model a wide range of option strategies.
You could see that expanding the spreadsheet from single option to four legs was really just a matter of creating additional copies of the same column, but there were a number of small details which we had to check options payoff spreadsheet fix, in order to make sure our calculations are correct. In the next partwe will use our calculations to draw payoff diagrams for our strategies. If you don't agree with any part of this Agreement, please leave the website now. All information is for educational purposes only and may be inaccurate, incomplete, outdated or plain wrong.
Macroption is not liable for any damages resulting from using the content. No financial, investment or trading advice is given at any time.
Home Calculators Tutorials About Contact. Tutorial 1 Tutorial 2 Tutorial 3 Tutorial 4. Calculating Option Options payoff spreadsheet Payoff in Excel. Option Strategy Payoff Calculation Total profit or loss from an option strategy that options payoff spreadsheet multiple options also called legs equals the sum of profit or loss of all these individual legs.
Inserting New Columns Because columns E-F are currently occupied by the contract size input and the combo box text inputs, we must move these to the right to make space for the new legs.
The result should look like this: In cell C9, the original formula is: If you click on the I2 part of the formula in cell C9 and press the F4 key on your keyboard, the I2 cell reference will change from relative to absolute, which you will recognize by the dollar signs: Repeat this with the combo boxes in E3 and F3.
We can calculate it in cell G9, using the formula: Next Steps Having started with a very simple calculation in part onenow in part 4 we have created quite an advanced spreadsheet which can calculate profit or loss for any combination of up to four legs and can be used to model a wide range of option strategies.