Using the 2007-2008 data provided1 in your course room, you must include an Excel spreadsheet with the option pricing when the financial crisis started. Use the Black Scholes Merton model and Binomial models to calculate the price of European call and put options.

The current price is the last date of data. You need to calculate the price of call option and put option on the last day of data only.

Link to the Excel file as a Google sheet:

https://www.dropbox.com/scl/fi/noz7rih65wnkmrgspybvj/2007-2208_Data_Group_Work_Submission_3.xlsx?dl=0&rlkey=xlr3dj5mgup44vdactcueb32m

Option pricing in Excel

1. Calculate the price of a call and put option using the Black Scholes model implemented in Excel.

The trader will introduce in the specified cells the model parameters as following:

Current stock price

Exercise price

Risk free rate

Volatility

Time to expiry

Underlying asset:

Microsoft stock price of one year given in the spreadsheet (MSFT share_price)

Volatility calculated in Excel based on the historical daily evolution of Microsoft stock price in the given 360 days.

Risk free rate = 4.2 %

Time to expiry = 1 year

Exercise price = last day stock price in the given data

2. Implement the Binomial Option Pricing Model in Excel using the same option inputs found in #2. The trader will introduce, in the specified cells, the model parameters as following, and compare the option prices for each model used in #1 and #2:

a. Current stock price

b. Exercise price

c. Risk free rate

d. Volatility

e. Time to expiry

f. The calculations must provide:

g. Upward movement (u)

h. Downward movement(d)

i. Probability of increase (Pu)

j. Probability of decrease (Pd)

k. Stock price considering the upward movement

l. Stock price considering the downward movement

m. Payoffs

n. The price of a call option

o. The price of a put option