Hello, dear friend, you can consult us at any time if you have any questions, add WeChat: daixieit

MFIN6002 Session ABCDE

Assignment 2

(Due by the end of January 31, 2023)

Download Excel file “HW2.xlsm” and finish the question in this file. Submit your Excel file to the “Assignment2” section on Moodle.

Q1. In the Black-Scholes-Merton model, the European call and put option prices are given by

where

The Greeks of the option also have analytical solutions. Gamma representshow delta changes with respect tostock prices. It is given by

where N′ (d1) is the probability density function of standard normal distribution, that is,

Vega represents the sensitivity of option price tostock volatility. It is given by

Rho represents the sensitivity of option price to risk-free rate.

Write VBA function procedures BSGamma, BSVega, BSRho that return the Gamma, Vega and Rho  of European options, respectively, based on the Black-Scholes model. You determine the arguments of functions.

Q2. This exercise aims to calculate Nike, Inc. (NYSE: NKE) stock option price, Greek letters and implied volatility.

(1) Download NKE stock market data up to the date when you work on this exercise. You decide the sample period and data frequency of stock price data you want to download. You may also need other data to estimate the inputs of option pricing models. Store all the raw data in worksheet “Q2- Raw Data” .

(2) Calculate the price of a call option on NKE with strike price K = $125, and expiration date February 20, 2023 using 100-period CRR Binomial Tree (based on the parameters that you estimate) .

(3) Calculate delta, gamma, vega and rho of the call based on Black-Scholes model using VBA functions BSDelta, BSGamma, BSVega, BSRho.

(4) Calculate implied volatility of NKE stock based on the call option we picked in part (2).

Store parameter estimations and calculations in worksheet “Q2-Answer” .