Using the spreadsheet provided, simulate a portfolio with delta hedging. Assume you have sold 100 European call options (each of which is worth 100 shares, for a total of 10,000 shares exposure) short on the stock of Lumberjack Farms. Information about option is given below. Annualized Volatility = 35% Option Expiration 4/30/2023 Risk-Free Rate = 2% Annual Borrowing Cost = 6% Strike Price = 50 Number of Shares = 10,000 No transaction costs and 365 days in a year (for interest and purposes).
Input Data
Exercise Price of Option (EX)
Compounded Risk-Free Interest Rate (rf)
Standard Deviation (annualized s)
Date
Formulas
Annualized Volatility = 35% Option Expiration 4/30/2023 Risk-Free Rate = 2% Annual Borrowing Cost = 6%
Input Data Exercise Price of Option (EX) Compounded Risk-Free Interest Rate (rf) Standard Deviation (annualized s)
50 0.02 0.35
Date Underlying Price (7) Formulas 2/10/2024 49 2/11/2024 48.77 2/12/2024 48.74 2/13/2024 47.81 2/14/2024 48.1 2/15/2024 48.03 2/16/2024 48.2 2/17/2024 47.55 2/18/2024 47.89 2/19/2024 47.43 2/20/2024 46.69 2/21/2024 46.44 2/22/2024 47.43 2/23/2024 47.09 2/24/2024 46.63 2/25/2024 47.09 2/26/2024 47.45 2/27/2024 47.01 2/28/2024 47.55 2/29/2024 48.04 3/1/2024 48.56 3/2/2024 48.14 3/3/2024 48.63 3/4/2024 47.33 3/5/2024 47.46 3/6/2024 47.04
Call Option Price (1)
Call Option Delta (2)
# of Stocks Required (3) = Delta * 10,000
Profit from Option (4) = Profit from Column (1) $
Profit from Stocks (5) = Profit from Column (7) $
Total Payoff (6) = Columns (7) - Column (4) $