Options Market Calculator in Excel

This document outlines how to create a comprehensive options market calculator using Excel. We will include formulas for calculating call and put options premiums, implementing Black-Scholes model, and obtaining Greeks.

1. Input Variables

To start, identify and set up the following input variables in your Excel sheet:

2. Black-Scholes Formula

2.1 Call Option Premium

The Black-Scholes call option price can be calculated using the formula:

Call Price (C) = S * N(d1) - K * e^(-rT) * N(d2)

Where:

• N(d) is the cumulative distribution function of the standard normal distribution.
• d1 = (ln(S/K) + (r + σ²/2)T) / (σ * sqrt(T))
• d2 = d1 - σ * sqrt(T)

2.2 Excel Formula for Call Price

To calculate the call price, enter the following formula in Cell B1:

=A1*NORM.S.DIST((LN(A1/A2)+(A4+A5^2/2)*A3)/(A5*SQRT(A3)), TRUE) - A2*EXP(-A4*A3)*NORM.S.DIST((LN(A1/A2)+(A4-A5^2/2)*A3)/(A5*SQRT(A3)), TRUE)

2.3 Put Option Premium

The Black-Scholes put option price can be calculated using the formula:

Put Price (P) = K * e^(-rT) * N(-d2) - S * N(-d1)

2.4 Excel Formula for Put Price

To calculate the put price, enter the following formula in Cell B2:

=A2*EXP(-A4*A3)*NORM.S.DIST(-(LN(A1/A2)+(A4-A5^2/2)*A3)/(A5*SQRT(A3)), TRUE) - A1*NORM.S.DIST(-(LN(A1/A2)+(A4+A5^2/2)*A3)/(A5*SQRT(A3)), TRUE)

3. Greeks Calculation

3.1 Delta

Delta measures the rate of change of the option price with respect to changes in the underlying price:

• Call Delta: N(d1)
• Put Delta: N(d1) - 1

3.2 Excel Formulas for Greeks

Delta for Call Option (Cell B3):

=NORM.S.DIST((LN(A1/A2)+(A4+A5^2/2)*A3)/(A5*SQRT(A3)), TRUE)

Delta for Put Option (Cell B4):

=NORM.S.DIST((LN(A1/A2)+(A4+A5^2/2)*A3)/(A5*SQRT(A3)), TRUE) - 1

3.3 Gamma

Gamma indicates the rate of change in delta with respect to the underlying price:

Gamma = N'(d1) / (S * σ * √T)
Where N' is the probability density function of the standard normal distribution.

Gamma Formula (Cell B5):

=NORM.S.DIST((LN(A1/A2)+(A4+A5^2/2)*A3)/(A5*SQRT(A3)), TRUE) / (A1 * A5 * SQRT(A3))

3.4 Theta

Theta measures the sensitivity of the option price to the passage of time:

Theta for Call: (- (S * N'(d1) * σ) / (2 * √T)) - r * K * e^(-rT) * N(d2)
Theta for Put: (- (S * N'(d1) * σ) / (2 * √T)) + r * K * e^(-rT) * N(-d2)

Theta Formula for Call (Cell B6):

=(- (A1 * NORM.S.DIST((LN(A1/A2)+(A4+A5^2/2)*A3)/(A5*SQRT(A3)), TRUE) * A5) / (2 * SQRT(A3))) - A4 * A2 * EXP(-A4*A3) * NORM.S.DIST((LN(A1/A2)+(A4-A5^2/2)*A3)/(A5*SQRT(A3)), TRUE)

Theta Formula for Put (Cell B7):

=(- (A1 * NORM.S.DIST((LN(A1/A2)+(A4+A5^2/2)*A3)/(A5*SQRT(A3)), TRUE) * A5) / (2 * SQRT(A3))) + A4 * A2 * EXP(-A4*A3) * NORM.S.DIST(-(LN(A1/A2)+(A4-A5^2/2)*A3)/(A5*SQRT(A3)), TRUE)

4. Practical Illustration

Assume the following values for inputs:

• Current Price (A1): $100
• Strike Price (A2): $100
• Time to Expiry (A3): 0.5 years
• Risk-Free Rate (A4): 0.05 (5%)
• Volatility (A5): 0.2 (20%)

With these values in the appropriate cells, the formulas provided will return the respective call and put premium, delta, gamma, and theta in the designated output cells.

5. Graphing

To visualize option pricing and Greeks:

1. Create a data series for various stock prices (S) surrounding your current price.
2. Use the formulas to calculate call and put premiums for each stock price.
3. Insert a line chart to depict this relationship and another chart for Greeks.

Conclusion

This comprehensive calculator for options market analysis provides functionalities for pricing options using the Black-Scholes model and calculating key Greeks. By structuring your Excel workbook with the outlined inputs and formulas, you can analyze options effectively for better trading strategies. For further mastering Excel and deeper insights into data analysis, consider exploring courses available on Enterprise DNA Platform.