It will be a piece of cake if you know the two-state binomial option pricing model and the Black Scholes model.
Step 1: Gather the most recent 61 monthly stock prices for the Stock Market (S&P 500 ETF) and BOBE.
a. Start by entering the ticker 'SPY' on the home page on yahoo finance in the 'quote lookup' window. Then Click on Historical Data.
b. Change the time period to September 1, 2011 through September 1, 2016 to capture 61 months of data. Set the frequency to 'monthly' and hit Apply.
c. Click on 'Download Data', and open the .csv file.
d. Copy the date column from your downloaded yahoo .csv into the first column (Column B) of the "2.Calc. Returns" worksheet. Start in cell B3.
e. Copy the Adjusted Prices from the yahoo data and paste this data into Column C of the "2.Calc. Returns" worksheet. Start in C3.
f. Download 61 months of data for BOBE and copy the data into Column D of the "2.Calc. Returns" worksheet.
Step 2: Work on the "2.Calc Returns" worksheet to find the various holding period returns.
The price series can be converted into a return series by calculating Returnt = (Pt - Pt-1 + Div)/Pt-1.
However, when the information is downloaded using the Yahoo! Finance .csv file, the prices are automatically adjusted to include dividends. Hence the name, Adjusted Price.
Therefore, it is only necessary to calculate the change in Price divided by the beginning monthly Price to calculate returns. Returnt = (Pt - Pt-1)/Pt-1
a. Open the "2. Calc. Returns" tab of this spreadsheet below.
b. Calculate all 60 Holding Period Returns for both SPY and BOBE by creating formulas in columns 'E' & 'F'
Step 3: Create and interpret a Line Graph of the returns. Refer to the instructions on the "2. Calc. Returns" tab.
Refer to the "3. Line Graph" worksheet to answer the following questions.
Helpful Hint: Place cursor on a point in the graph to display values.
a. In what month was the largest positive monthly return for the company in the past five years?
b. What was the largest positive monthly return?
c. In what month was the largest negative monthly return for the company in the past five years?
d. What was the largest negative monthly return?
e. Based on the line graph that you created, what observations can you make regarding the relationship between BOBE and the market over the past five years?
Step 4: Calculate the Variance and Standard Deviation for both 'SPY' and 'BOBE'. Refer to the instructions on the "4. Calc. Stats" tab.
I have completed this step for you. You will use the standard deviation calculated in this step in order to value the call option in step 6.
Although I have already coded this step, you should look over the formulas and steps that were used as an example of a simple way thet you can calculate these risk measures.
This follows for step 5 as well:
Step 5: Compare the calculated Beta with the reported Beta on Yahoo! Finance.
Covariance is a statistical measure that caculates how two series move in relation to each other.
The calculations for covariance are shown in column H of the '5.Calc Beta" spreadsheet.
I have done all of the calculations for you in this tab, you need to interpret it...
Answer the following questions referring to the "5.Calc. Beta" worksheet.
a. What can you tell about 'BOBE' based upon its calculated beta?
b. Look up BOBE's Beta on Yahoo!Finance using the following steps:
1. Enter the ticker symbol under quote lookup.
2. Click on Key Statistics under COMPANY on the left-hand side of the web page.
The beta is on the right-hand side under Trading Information
• What is the reported Beta on Yahoo!?
• What is the calculated Beta from this spreadsheet?
• What factors can lead to these two betas being different? (you may need to do a little research for this)
Step 6: Calculate the premium on a BOBE 30 Call using the Two-State Binomial Options Pricing Model and the Black-Scholes Model
**All formulas that you need for this step are included in the lesson 4 material in Angel**
a. On the "6.BOPM" tab, you will calculate the premium on a six-month Bob Evans call option with a strike price of $35 using both the risk-free approach and the risk-neutral approach under both continuous AND annual compounding
I have entered the current spot price on BOBE as the closing price on Tuesday, September 6, 2016
I have also entered the strike price and the borrowing rate
You must fill in all cells highlighted in yellow, using formulas where appropriate
b. On the "7.BSOPM" tab, you will calculate the premium on a six-month Bob Evans call option with a strike price of $35 using the Black-Scholes Model
I have entered the current spot price on BOBE as the closing price on Tuesday, September 6, 2016
I have also entered the strike price and the borrowing rate
You must fill in all cells highlighted in yellow, using formulas where appropriate
**Hint: Instead of using the Z-Tables for N(d1) & N(d2), Excel has a function for this, NORMSDIST. Excel is more accurate because it does not round.**
c. Look up the premium on a BOBE 35 call with an expiration of March 17, 2017 on Yahoo!Finance using the following steps:
1. Enter the ticker symbol under quote lookup.
2. Click on Options.
3. Directly above the option quotes is a drop-down menu with dates. Click on March 17, 2017
The BOBE 35 calls are listed underneath
• The 'last' price is the price at which the last trade was made
• The 'bid' price is the highest price that a buyer is willing to pay
• The 'ask' price is the lowest price that a seller is wiling to accept
**Keep in mind that the trading volume of options is very small compared to common shares**
• What is the 'last' price reported on the 35 calls?
• What is the calculated premium from this spreadsheet using the risk-free aproach?
• What is the calculated premium from this spreadsheet using the risk-neutral aproach?
• What is the calculated premium from this spreadsheet using the Black-Scholes Model?
• What factors contribute to the difference between the listed premium and the calculated premiums? (you may need to do a little research for this)
Attachment:- Excel-Project-.rar