We can use the formulas which we already have in rows 8 and 9, but will have to make a few adjustments. 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. Now we have X-axis ready and can calculate the payoff at each point.
#Add break even analysis in excel 2011 for mac plus
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.Ĭell B12, which will be the first point on the chart’s X-axis, will be set equal to cell I5: =$I$5Ĭell B13, which will be the second point on the chart’s X-axis, will equal the first point plus the increment. 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. To make our spreadsheet good for different underlying securities with different price levels, we should make the range dynamic. One decision we need to make is the range of underlying prices that our payoff diagram will cover. 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 from column B. Let’s put underlying price (the chart’s X-axis) in column B, starting in row 12. We will do this right below our existing P/L calculations. To draw the graph, we need to calculate P/L for different levels of underlying price.
We will use these calculations to create a payoff diagram, which is a graph that shows how an option strategy’s profit or loss (P/L) changes based on underlying price.
0 kommentar(er)
