Formula Generator | Excel

Creating an Options Graph in Excel

This guide provides step-by-step instructions to create an options graph in Excel, illustrating profit and loss for various options trading strategies based on input data such as strike price and premium.

Prompt

Answer

Creating an Options Graph in Excel

Introduction

An options graph typically visualizes the potential outcomes of various options trading strategies, typically illustrating profit and loss against various stock prices at expiration. This guide presents the steps to create an options graph using Excel.

Key Components

Input Data: You need to define parameters such as strike price, premium, and stock price range.
Profit/Loss Calculation: Using formulas to determine profit or loss based on the options strategy.
Graph Creation: Plotting the results using an Excel chart.

Step-by-Step Instructions

Step 1: Prepare Input Data

In your Excel worksheet, set up the following inputs:

Cell	Parameter	Description
A1	Strike Price	The price at which the option can be exercised
A2	Premium	Cost of the option
A3	Stock Price Range	Bottom value of stock price for the graph
A4	Stock Price Max	Top value of stock price for the graph

Step 2: Create Stock Price Range

In cells B6 to B16, create a stock price range using the following formula in B6:

=A3 + (ROW()-ROW($B$6))*((A4-A3)/10)

Drag the formula down to generate prices until cell B16.

Step 3: Calculate Profit or Loss

In cell C6, input the following formula to calculate profit/loss for a call option:

=IF(B6 < A1, -A2, B6 - A1 - A2)

For a put option, the formula would be:

=IF(B6 > A1, -A2, A1 - B6 - A2)

Drag this formula down from C6 to C16, corresponding to your stock price range.

Step 4: Create the Options Graph

Select the Data: Highlight the data range including stock prices and profit/loss values (B6 to C16).
Insert Chart: Go to the 'Insert' tab, choose 'Scatter' chart, and select 'Scatter with Straight Lines'.
Format the Chart: Add titles, axes labels, and customize the graph for better presentation.

Example Summary

Input Example:

Strike Price (A1): 50
Premium (A2): 2
Stock Price Range (A3): 40
Stock Price Max (A4): 60

Profit/Loss Calculation: For a stock price of 48 (cell B11), profit/loss (in C11) is calculated as:

=IF(48 < 50, -2, 48 - 50 - 2) -> -2

Graphing this data will produce a visual representation of the call option profit/loss across different stock prices.

Conclusion

By following the outlined steps, you can effectively create an options graph in Excel, visualizing potential trading outcomes based on input parameters. For deeper learning and advanced techniques, consider exploring courses available on the Enterprise DNA Platform.