Reference no: EM133121557
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
|
Constant payout and retention ratios
: For a firm with a constant payout and retention (plow back) ratios, the dividend growth rate can be estimated as:
|
|
Constant rate of return that cadar security must earn
: Cadar Security recently paid an annual dividend of $6.60 on its common stock. The price of the Cadar's stock is $42.21 per share and the required return on this
|
|
What is the project payback
: Project L requires an initial outlay at t = 0 of $59,000, its expected cash inflows are $10,000 per year for 7 years, and its WACC is 11%.
|
|
Compare and contrast the typical capital structure
: Compare and contrast the typical capital structure for companies across the following industries: financial (banking), technology (software), airlines (air tran
|
|
Calculate the price of a call and put option
: 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.
|
|
What is the bond yield to maturity
: Tirtirogu Corp.'s bonds currently sell for $1,145.68 and have a $1,000 par value. They have a 7.50% annual coupon rate and a 15-year maturity.
|
|
What is the interest rate on 1-year
: Suppose most investors expect the inflation rate to be 5% next year, 6% the following year, and 7% thereafter. The real risk-free rate is 3.5%.
|
|
What is the change in capital for the fund
: You are running a hedge fund with a long position of 4,000 shares of IBM, and a short position of 6,000 shares of Intel. IBM is currently trading at $190 per sh
|
|
Prepare one summary journal entry at december
: Prepare one summary journal entry at December 31, 2021, to record the cost of replacing the defective game consoles returned during November and December
|