The spreadsheet allows you to specify a strategy with up to four positions in stocks, bonds, puts, or calls. The first worksheet in the Excel spreadsheet file is named "Payoffs" and demonstrates payoff diagrams only. The second worksheet is named "Profits" and demonstrates both payoff and profit diagrams.

Select the Graph type

First select the graph type using the pop-up menu under the word "Graph" (located low to mid-left side of the spreadsheet). For the "Payoffs" worksheet, your choices are { Payoff A, Payoff B, Payoff C, Payoff D, Payoff All, Payoff Combined, Payoff All + Combined }. Choosing Payoff A will graph only the payoff diagram for position A (in blue on the graph). Choosing Payoff All will graph all positions on the graph at the same time. Choosing Payoff Combined will only graph the payoff diagram on the combined strategy of positions A through D. Similar graph types are available on the "Profits" worksheet.

There are four possible positions (A through D) and the position color will correspond to the color of the curve on the graph. For example, the first instrument (A) is blue, so its payoff diagram on the graph will be blue. For "Profits" worksheet, the payoff diagram curves on the graph are thicker than the profit diagram curves, but are the same color. So, selecting a graph type of {Profit and Payoff All} will show two curves of each color, the thinner curve is the profit diagram and the thicker curve is the payoff diagram for the position.

Select each position

To graph the payoff or profit diagrams, you must specify a position in at least one instrument. Choose the instrument you want from one of the four pop-up menus under the "Instrument" column. Your choices are {Call, Put, Stock, Bond}. Then choose what type of position you want {Long, Short}. For options, the Short position is when you "write" an option. For bonds, the Short position is equivalent to borrowing money (i.e., short selling a bond). If you choose a Call or a Put, then you must specify the strike price {5,...,35} under the "X/FV" column. Likewise, for a Bond, you must specify the face value of the bond under the "X/FV" column. For a position in a Stock, the "X/FV" column is meaningless. The diagram represented on the graph shows the payoff or profit at expiration (or time of exercise) for the options contracts.

Each position is for one share of stock, an option on one share of stock, or one bond with the given face value. So, if you want a position of two of the same call options, for example, then you must specify identical call options for two separate positions (e.g., A and B must have identical call option positions).

To cancel a position, set the pop-up menu for the "Instrument" to blank (the first pop-up menu choice). For example, setting all four positions (A-D) to a blank instrument gives you a blank graph. Using a graph setting of Payoff All and setting instruments C and D to blank will only show positions A and B on the graph (assuming A and B are set to Call, Put, Stock, or Bond).

Profit Diagrams

Profit diagrams assume that the price of the stock when the positions are opened is $20 per share. The option premia are calculated from an option pricing model using certain assumptions about volatility and interest rates (as well as the stock price of $20 per share and the stated strike price for the position). The option premium for a Call or Put position will appear when the position is selected. The present value (or price) of the Bond will appear when the position is selected. The bond price assumes an interest rate of around 11%. All prices are rounded to the nearest 10 cents.

Example

To show the payoff diagram for a strategy where you write one call with a strike price of $30 and buy one put with strike price of $20, you would do the following. For position A (blue), set the instrument to Call, the position to Short, and the X/FV to 30. For position B (red), set the instrument to Put, the position to Long, and the X/FV to 20. Set the graph type to Payoff All to show both payoff diagrams from each position on the same graph. Or, set the graph type to Payoff Combined to see only the payoff diagram of the strategy as a whole.

Download the Option Payoff Diagram Spreadsheet