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 hedging example excel

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

Delta Hedging is a trading strategy to reduce directional risk.

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:

Implement Delta Hedging Using Stocks in Excel

  • 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.

Implement Delta Hedging Using Stocks in Excel

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")

Implement Delta Hedging Using Stocks in Excel

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 for 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.

Apply Options for Delta Hedging in Excel

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 shares1 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.

Utilize Management and Adjustments for Delta hedging

  • 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 1910=9 contracts.

  • If F16 is greater than 0, it indicates that you need to buy.
  • You need to sell the contracts for Delta Hedging.
  • Enter the following formula in G17 cell:

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

  • Press Enter to see the result.

Utilize Management and Adjustments for Delta hedging


Download Practice Workbook

Download the workbook.


 

Related Articles


<< Go Back to Excel DELTA Function | Excel Functions | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!
Sudipta Chandra Sarker
Sudipta Chandra Sarker

Sudipta Chandra Sarker, BSc, Electrical and Electronic Engineering, Bangladesh University of Engineering and Technology, Bangladesh, has worked on the ExcelDemy project for over a year. For ExcelDemy, he has authored 42 articles and reviewed over ten articles. He is employed as a junior software developer at the moment. He aims to create various useful Microsoft Office Add-ins, extending the functionality of Office programs. His interests span Microsoft Office Suites, Data Science, VBA, VB.NET, ASP.NET, C#, Excel, and Desktop... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo