Delta Hedging in Excel - 3 Examples

What Is Delta Hedging?

In stock markets, delta hedging is used to lower the risk and maximize profit.

Example 1- Calculate Delta Hedging Using Stocks in Excel

The dataset showcases a company’s condition in the Stock Market. It details the company’s option price and the underlying stock between the 1st and 31st of October 2022.

Calculate the delta based on the comparison between the 1st and 31st of October:

Delta is the ratio of the difference between the Option Price and the Underlying Stock .

STEPS:

Use the following format to show Options and Underlying Stocks.
For Option 1, the starting and the final values of Option and Underlying Stock were entered.
Click C8 cell and use the following formula in the formula bar to calculate the Delta:

=(C5-C4)/(C7-C6)

Press Enter to see the result.

This formula indicates (Final- Initial Option Value)/ (Final-Initial Underlying Value).

Enter information for Option 2.
Here, the starting and the final values of Option and Underlying Stock.
Calculate the Delta for Option 2:

Click C16 cell and enter the following formula:

=(C13-C12)/(C15-C14)

Press Enter to see the result.

To calculate the overall Delta for the company:

Sum the Delta values.
Select C17 cell and use the following formula:

=C9+C16

Press Enter.

To calculate the number of stocks you need to buy or sell:

Select C17 and enter the following formula:

=C17*100

Press Enter to exit the editing mode and see the result.

For US stock options: 1 option contract = 100 shares. 1 unit Delta means 100 shares or stocks. The number of stocks will be a multiplication of Delta by 100.

For Delta Hedging, you need to sell your stocks if Delta is positive.
After selling these stocks the resultant Delta will be 0.
You need to buy the calculated amounts of stocks if Delta is negative.
Enter the following formula in D19:

=IF(C17>0,"Sale","Buy")

The IF function determines if the value of C17 is positive or negative. If the value of the cell is greater than 0, it will return Sale. Otherwise, Buy.

Example 2 – Applying Options to Delta Hedging in Excel

If the resultant Delta is 0.6, you have to buy contracts with -0.6. If the resultant Delta is -0.6, you have to buy contracts of +0.6.

STEPS:

The previous dataset was used.
In Option 1, information is unchanged.
In Option 2, information was slightly changed.

To recalculate the value of Delta for Option 2:

Enter the following formula in C16:

=(C13-C12)/(C15-C14)

Press Enter to see the value.

To calculate the total Delta for your company:

Enter the following formula in C17:

=C9+C16

Press Enter.

The Delta value is calculated.

To buy a contract with -0.2, so that the resultant Delta is 0:

Enter the following formula in C19:

=C17*100/(200/100)

Press Enter to see the result.

For US stock options, 1 option contract = 100 shares. 1 unit Delta means 100 shares or stocks. 0.2 means 0.2*100=20 shares. Divide the share by total option value. For the 2 options, the total Option value is 200. The result is 20/2=10, which indicates Contracts.

Example 3 – Manage and Adjust Delta Hedging

STEPS:

This is the dataset.
For Delta Hedging, you need to buy 10 contracts.

Opinion 1 changes rapidly.
The Delta value of Option 1 will be 13.
The Final Value of the Option was supposed to be $120.
But the Final value of the Option changes to $170.
As a result, the value of Delta is changed to 0.3.
The value of Option 2 is unchanged.
The total Delta value is now 0.37.

Calculate the actions needed:

Enter the following formula in F16:

=F14*100/(200/100)-C19

Press Enter.

For US stock options, 1 option contract=100 shares. 1 unit Delta means 100 shares or stocks. 0.37 means 0.37*100=37 shares. Divide the share by the total option value. For the 2 options, the total Option value is 200. The result is 37/2=18.5~19, which indicates Contracts. It was planned to buy 10 contracts, you need to buy 19–10=9 contracts.