Understanding Option Gamma in Excel

Gamma is a measure of how much an option’s price changes when the underlying asset’s price changes by one point. It is also known as the rate of change of delta, which is another option’s greek that measures how much an option’s price changes when the underlying asset’s price changes by one point.

Gamma tells you how sensitive your option position is to small movements in the underlying asset’s price. For example, if you have a call option with a delta of 0.5 and a gamma of 0.1, it means that for every $1 increase in the underlying asset’s price, your option’s delta will increase by 0.1 points. Conversely, for every $1 decrease in the underlying asset’s price, your option’s delta will decrease by 0.1 points.

Gamma is important because it helps you estimate how much your option position will gain or lose when the underlying asset moves in your favor or against you. Gamma also affects your risk exposure and hedging strategies. For instance, if you are long a call option and the underlying asset goes up, your gamma will increase and so will your delta risk. This means that you need to adjust your position size or hedge it with other options or stocks to reduce your risk.

Gamma is highest for at-the-money options (ATM) and lowest for deep in-the-money or out-of-the-money options (ITM or OTM). This is because ATM options have more uncertainty about their moneyness and therefore more potential for large changes in delta when the underlying asset moves slightly. ITM or OTM options have less uncertainty about their moneyness and therefore less potential for large changes in delta when the underlying asset moves slightly.

Gamma also varies depending on the time to expiration of the option. Gamma is usually higher for near-term options than for far-term options, all else equal. This is because near-term options have more time value and therefore more sensitivity to small changes in the underlying asset’s price.

Basic Theory:

Gamma is one of the Greeks used to measure an option’s sensitivity to changes in the underlying asset’s price. Specifically, Gamma represents the rate of change of an option’s delta concerning a one-point move in the underlying asset. In simpler terms, it measures how fast an option’s delta changes as the price of the underlying asset changes.

Excel Formulas:

Delta (Δ): Delta measures the rate of change of an option’s price concerning a one-point move in the underlying asset price. It is the first derivative of the option price with respect to the underlying asset price.
```
=NORM.DIST(X,0,1,TRUE)
```
Gamma (Γ): Gamma is the second derivative of the option price with respect to the underlying asset price. It represents the curvature of the option price curve.
```
=NORM.DIST(X,0,1,FALSE)/(Asset_Price * Option_Price * SQRT(Expiration_Days/365))
```

Procedures:

Enter the relevant data into an Excel table, including the current asset price, option price, and days to expiration.
Use the Delta formula to calculate the option’s delta.
Apply the Gamma formula to find the option’s Gamma.
Modify the asset price slightly and observe how the option delta changes due to Gamma.

Scenario:

Let’s consider a call option on Stock XYZ with the following parameters:

Current Stock Price (S): $100
Call Option Price (C): $5
Days to Expiration (T): 30 days
Volatility (σ): 20%
Risk-Free Rate (r): 2%

Calculation:

1. Calculate Delta:

            d1 = (LN(S/X) + (r + (σ^2)/2) * T) / (σ * SQRT(T))
            Delta (Δ) = NORM.DIST(d1,0,1,TRUE)

Substituting the values:

            d1 = (LN(100/100) + (0.02 + (0.20^2)/2) * (30/365)) / (0.20 * SQRT(30/365))
            Delta (Δ) ≈ NORM.DIST(0.521,0,1,TRUE) ≈ 0.696

1. Calculate Gamma:

            Gamma (Γ) = NORM.DIST(d1,0,1,FALSE) / (S * C * SQRT(T/365))

Substituting the values:

            Gamma (Γ) ≈ NORM.DIST(0.521,0,1,FALSE) / (100 * 5 * SQRT(30/365)) ≈ 0.016

Practical Application:

Now, if the stock price increases by $1, we can use Gamma to estimate the new delta:

        New Delta = Old Delta + (Gamma * Change in Stock Price)
                  ≈ 0.696 + (0.016 * 1)
                  ≈ 0.712