Option’s delta is a measure of how much the price of an option changes when the price of the underlying asset changes by $1. It is also a measure of how likely the option is to be in-the-money at expiration. Option’s delta can be positive or negative, depending on whether the option is a call or a put.
A call option gives the buyer the right to buy the underlying asset at a specified price (the strike price) before or on a certain date (the expiration date). A call option has a positive delta if the underlying asset price goes up, and a negative delta if it goes down. The higher the delta, the more sensitive the option is to the underlying asset price movement.
A put option gives the buyer the right to sell the underlying asset at a specified price before or on a certain date. A put option has a negative delta if the underlying asset price goes up, and a positive delta if it goes down. The higher the delta, the more sensitive the option is to the underlying asset price movement.
Option’s delta can also be used as a share equivalency, meaning that one unit of an option represents one unit of shares of stock. For example, if an option has a delta of 0.5, it means that for every $1 increase in stock price, the option value increases by $0.5.
Option’s delta can also be used to estimate how likely an option is to be in-the-money at expiration. For example, if an option has a delta of 0.8 for calls and -0.6 for puts, it means that there is an 80% chance that it will be profitable for calls and an 80% chance that it will be profitable for puts at expiration.
Basic Theory:
Delta is represented as a percentage and ranges from -1 to 1. For call options, delta is positive, indicating
the expected change in the option price given a change in the underlying asset’s price. For put options, delta
is negative, representing the expected change in the option price with changes in the underlying asset’s price.
Procedures:
Calculating delta involves complex mathematical formulas, but Excel makes it easy. The delta of an option can be
estimated using the following formula:
Delta = (ΔOption Price) / (ΔUnderlying Asset Price)
In Excel, this can be implemented using the formula:
Delta = (NORM.S.DIST(ln(Underlying Asset Price/Strike Price) + (Risk-Free Rate + (Volatility^2/2)) * Time to
Expiry)) /
(Underlying Asset Price * Volatility * sqrt(Time to Expiry))
Scenario:
Let’s consider a call option on Stock XYZ. The current stock price is $100, the strike price is $105, the
risk-free rate is 5%, volatility is 20%, and the time to expiry is 1 year.
Calculation in Excel:
| A | B | C | D | E |
|---|
Results:
- Log of (S/X): ln(100/105) ≈ -0.04879
- Annual Variance: (0.2^2)/2 = 0.02
- D1: ((-0.04879 + 0.05 + 0.02/2) * 1) / (0.2 * sqrt(1)) ≈ -0.0062
- NORM.S.DIST(D1): NORM.S.DIST(-0.0062) ≈ 0.4978
- Delta: 105 * 0.2 * 100 * 0.4978 ≈ 1039.7
Alternative Approaches:
- Black-Scholes Model: A widely used formula for option pricing includes delta. Excel provides
functions like NORM.S.DIST and LN to easily implement the Black-Scholes model. - Delta as Derivative: Another approach is to use Excel’s numerical differentiation tools.
You can calculate the option price for two slightly different underlying prices and use the difference to
derive the delta.
Basic Theory of Option Delta:
Delta measures the rate of change of the option price concerning a change in the price of the underlying
asset. It ranges from -1 to 1 for put options and 0 to 1 for call options. A delta of 0.5 indicates that for
every $1 increase in the underlying asset, the option price is expected to increase by $0.50. Delta is often
considered the hedge ratio as it helps traders manage the risk associated with changes in the underlying
asset’s price.
Procedure for Calculating Option Delta in Excel:
- Gather Data: Collect information on the option contract, including the current price of
the underlying asset, the option strike price, time to expiration, risk-free rate, and implied
volatility. - Set Up Excel Spreadsheet: Create an Excel spreadsheet with columns for the relevant
parameters. Label these columns as “Underlying Price,” “Strike Price,” “Time to Expiry,” “Risk-Free
Rate,” “Implied Volatility,” and “Option Delta.” - Use the Delta Formula: Employ the Black-Scholes option pricing model to calculate
delta. The formula for delta in Excel is as follows for a call option:=NORM.S.DIST(d1,TRUE) - For a put option:
=NORM.S.DIST(d1,TRUE) - 1 - Input Parameters: Enter the gathered data into the respective columns in your Excel
spreadsheet.
Scenario with Real Numbers:
Let’s consider a call option with the following parameters:
- Underlying Price: $100
- Strike Price: $95
- Time to Expiry: 0.5 years
- Risk-Free Rate: 5%
- Implied Volatility: 20%
Using the provided formulas, calculate d1 and then input it into the delta formula to find the option delta.
| Underlying Price | Strike Price | Time to Expiry | Risk-Free Rate | Implied Volatility | Option Delta |
|---|---|---|---|---|---|
| $100 | $95 | 0.5 years | 5% | 20% | [Result] |
Calculation:
1. Calculate d1:
d1 = (LN(100/95) + (5% + (20%^2)/2) * 0.5) / (20% * SQRT(0.5))
2. Use the delta formula:
Option Delta = NORM.S.DIST(d1, TRUE)
Input the calculated value of d1 into this formula to find the option delta.
Result:
After performing the calculations, the resulting option delta for the given scenario should be displayed in the
“Option Delta” column of your Excel spreadsheet.
Other Approaches:
- Use Excel Add-ins: Explore Excel add-ins that provide pre-built functions for option
pricing and Greeks calculations. These tools can simplify the process and reduce the risk of errors. - Data Analysis ToolPak: Utilize the Data Analysis ToolPak in Excel, which includes
functions for statistical analysis. This tool can assist in calculating standard normal distribution
probabilities without manual formula entry.
