(solution) Analyzing Historical Market Risk and Valuing a Call Option for

(solution) Analyzing Historical Market Risk and Valuing a Call Option for

Please see attached! Problem is not too long, most of it is completed for you already! It will be a piece of cake if you know the two-state binomial option pricing model and the Black Scholes model.

Analyzing Historical Market Risk and Valuing a Call Option for Bob Evans.
We will continue with the same company from the first Excel project, Bob Evans Farms Inc. (BOBE)
Follow all directions for all six steps below and answer all questions.
Type all answers in the spaces provided on this instructions tab.
Navigate to Yahoo! Finance to begin gathering data. 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.
You need 61 observations to calculate 60 returns. The dates of the S&P 500 index data (SPY) and BOBE MUST BE THE SAME. 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 Return t = (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. Return t = (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) Month
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61 Date SPY (S&P
500 SPDR
ETF) BOBE Holding
Period
Return
(Market)
SPY 2.Calc. Returns
Holding
Period
Return
(BOBE)
BOBE Instructions for Inputing Data 1. First, paste the dates from the 61 most recent monthly returns and the
'SPY' adjusted close prices from Yahoo Finance.
2. Second, paste the adjusted prices from the 'BOBE' data.
3. Compute the Holding Period Returns for both the market and 'BOBE'.
NOTE: The dates of the company information in column D and
the S&P 500 data must match. Instructions for the Graphing Step
1. Hide columns C & D by highlighting both columns, then right click and
select "hide"
2. Highlight columns B, E, & F. Begin with Row 2 to capture the labels.
3. With the rows highlighted, go to the "Insert" tab at the top of your
Excel workbook and select a "Line Chart" in the "Charts" section
4. Select the "Line with Markers" chart
5. Select the chart that you have just created and CUT and paste it onto
the tab labeled "3.Line Graph." Then enlarge the graph to be large
enough for you to better see the differences between to two lines
6. Label the graph "SPY vs. BOBE" 4.Calc. Stats.
Date
Dec-99
Dec-99
Dec-99
Dec-99
Dec-99
Dec-99
Dec-99
Dec-99
Dec-99
Dec-99
Dec-99
Dec-99
Dec-99
Dec-99
Dec-99
Dec-99
Dec-99
Dec-99
Dec-99
Dec-99
Dec-99
Dec-99
Dec-99
Dec-99
Dec-99
Dec-99
Dec-99
Dec-99
Dec-99
Dec-99
Dec-99
Dec-99
Dec-99
Dec-99
Dec-99
Dec-99
Dec-99
Dec-99
Dec-99
Dec-99
Dec-99
Dec-99
Dec-99
Dec-99
Dec-99
Dec-99
Dec-99
Dec-99
Dec-99
Dec-99
Dec-99
Dec-99
Dec-99
Dec-99
Dec-99
Dec-99
Dec-99
Dec-99
Dec-99
Dec-99
Sums: SPY
Returns
0.00%
0.00%
0.00%
0.00%
0.00%
0.00%
0.00%
0.00%
0.00%
0.00%
0.00%
0.00%
0.00%
0.00%
0.00%
0.00%
0.00%
0.00%
0.00%
0.00%
0.00%
0.00%
0.00%
0.00%
0.00%
0.00%
0.00%
0.00%
0.00%
0.00%
0.00%
0.00%
0.00%
0.00%
0.00%
0.00%
0.00%
0.00%
0.00%
0.00%
0.00%
0.00%
0.00%
0.00%
0.00%
0.00%
0.00%
0.00%
0.00%
0.00%
0.00%
0.00%
0.00%
0.00%
0.00%
0.00%
0.00%
0.00%
0.00%
0.00%
0.00% Monthly E(R):
Variance:
Standard Deviation: BOBE
Returns
0.00%
0.00%
0.00%
0.00%
0.00%
0.00%
0.00%
0.00%
0.00%
0.00%
0.00%
0.00%
0.00%
0.00%
0.00%
0.00%
0.00%
0.00%
0.00%
0.00%
0.00%
0.00%
0.00%
0.00%
0.00%
0.00%
0.00%
0.00%
0.00%
0.00%
0.00%
0.00%
0.00%
0.00%
0.00%
0.00%
0.00%
0.00%
0.00%
0.00%
0.00%
0.00%
0.00%
0.00%
0.00%
0.00%
0.00%
0.00%
0.00%
0.00%
0.00%
0.00%
0.00%
0.00%
0.00%
0.00%
0.00%
0.00%
0.00%
0.00%
0.00% Variance SPY
(RM – E(RM))
(RM – E(RM))2
0.00000
0.00000
0.00000
0.00000
0.00000
0.00000
0.00000
0.00000
0.00000
0.00000
0.00000
0.00000
0.00000
0.00000
0.00000
0.00000
0.00000
0.00000
0.00000
0.00000
0.00000
0.00000
0.00000
0.00000
0.00000
0.00000
0.00000
0.00000
0.00000
0.00000
0.00000
0.00000
0.00000
0.00000
0.00000
0.00000
0.00000
0.00000
0.00000
0.00000
0.00000
0.00000
0.00000
0.00000
0.00000
0.00000
0.00000
0.00000
0.00000
0.00000
0.00000
0.00000
0.00000
0.00000
0.00000
0.00000
0.00000
0.00000
0.00000
0.00000
0.00000
0.00000
0.00000
0.00000
0.00000
0.00000
0.00000
0.00000
0.00000
0.00000
0.00000
0.00000
0.00000
0.00000
0.00000
0.00000
0.00000
0.00000
0.00000
0.00000
0.00000
0.00000
0.00000
0.00000
0.00000
0.00000
0.00000
0.00000
0.00000
0.00000
0.00000
0.00000
0.00000
0.00000
0.00000
0.00000
0.00000
0.00000
0.00000
0.00000
0.00000
0.00000
0.00000
0.00000
0.00000
0.00000
0.00000
0.00000
0.00000
0.00000
0.00000
0.00000
0.00000
0.00000
0.00000
0.00000
0.00000
0.00000
0.00000
0.00000
0.00000
SPY
0.00%
0.00000
0.00% Variance BOBE
(RC – E(RC))
(RC – E(RC))2
0.00000
0.00000
0.00000
0.00000
0.00000
0.00000
0.00000
0.00000
0.00000
0.00000
0.00000
0.00000
0.00000
0.00000
0.00000
0.00000
0.00000
0.00000
0.00000
0.00000
0.00000
0.00000
0.00000
0.00000
0.00000
0.00000
0.00000
0.00000
0.00000
0.00000
0.00000
0.00000
0.00000
0.00000
0.00000
0.00000
0.00000
0.00000
0.00000
0.00000
0.00000
0.00000
0.00000
0.00000
0.00000
0.00000
0.00000
0.00000
0.00000
0.00000
0.00000
0.00000
0.00000
0.00000
0.00000
0.00000
0.00000
0.00000
0.00000
0.00000
0.00000
0.00000
0.00000
0.00000
0.00000
0.00000
0.00000
0.00000
0.00000
0.00000
0.00000
0.00000
0.00000
0.00000
0.00000
0.00000
0.00000
0.00000
0.00000
0.00000
0.00000
0.00000
0.00000
0.00000
0.00000
0.00000
0.00000
0.00000
0.00000
0.00000
0.00000
0.00000
0.00000
0.00000
0.00000
0.00000
0.00000
0.00000
0.00000
0.00000
0.00000
0.00000
0.00000
0.00000
0.00000
0.00000
0.00000
0.00000
0.00000
0.00000
0.00000
0.00000
0.00000
0.00000
0.00000
0.00000
0.00000
0.00000
0.00000
0.00000
0.00000
BOBE
0.00%
0.00000
0.00% Instructions
1. Calculate the monthly expected returns for both 'SPY'
and 'BOBE' (use the box at the bottom of this page)
2. Calculate columns E, F, H, & I to find the
variance of 'SPY' and of 'BOBE'
3. Using the variance, calculate the standard
deviation for both 'SPY' and 'BOBE' Date
Dec-99
Dec-99
Dec-99
Dec-99
Dec-99
Dec-99
Dec-99
Dec-99
Dec-99
Dec-99
Dec-99
Dec-99
Dec-99
Dec-99
Dec-99
Dec-99
Dec-99
Dec-99
Dec-99
Dec-99
Dec-99
Dec-99
Dec-99
Dec-99
Dec-99
Dec-99
Dec-99
Dec-99
Dec-99
Dec-99
Dec-99
Dec-99
Dec-99
Dec-99
Dec-99
Dec-99
Dec-99
Dec-99
Dec-99
Dec-99
Dec-99
Dec-99
Dec-99
Dec-99
Dec-99
Dec-99
Dec-99
Dec-99
Dec-99
Dec-99
Dec-99
Dec-99
Dec-99
Dec-99
Dec-99
Dec-99
Dec-99
Dec-99
Dec-99
Dec-99
Sums: SPY
Returns
0.00%
0.00%
0.00%
0.00%
0.00%
0.00%
0.00%
0.00%
0.00%
0.00%
0.00%
0.00%
0.00%
0.00%
0.00%
0.00%
0.00%
0.00%
0.00%
0.00%
0.00%
0.00%
0.00%
0.00%
0.00%
0.00%
0.00%
0.00%
0.00%
0.00%
0.00%
0.00%
0.00%
0.00%
0.00%
0.00%
0.00%
0.00%
0.00%
0.00%
0.00%
0.00%
0.00%
0.00%
0.00%
0.00%
0.00%
0.00%
0.00%
0.00%
0.00%
0.00%
0.00%
0.00%
0.00%
0.00%
0.00%
0.00%
0.00%
0.00%
0.00% BOBE
Returns
0.00%
0.00%
0.00%
0.00%
0.00%
0.00%
0.00%
0.00%
0.00%
0.00%
0.00%
0.00%
0.00%
0.00%
0.00%
0.00%
0.00%
0.00%
0.00%
0.00%
0.00%
0.00%
0.00%
0.00%
0.00%
0.00%
0.00%
0.00%
0.00%
0.00%
0.00%
0.00%
0.00%
0.00%
0.00%
0.00%
0.00%
0.00%
0.00%
0.00%
0.00%
0.00%
0.00%
0.00%
0.00%
0.00%
0.00%
0.00%
0.00%
0.00%
0.00%
0.00%
0.00%
0.00%
0.00%
0.00%
0.00%
0.00%
0.00%
0.00%
0.00% Distance
from Mean
(RM – E(RM)) 5.Calc. Beta
Distance
from Mean
Covariance(SPY,BOBE)
(RC – E(RC))
(RM – E(RM))((RC – E(RC)) 0.00000
0.00000
0.00000
0.00000
0.00000
0.00000
0.00000
0.00000
0.00000
0.00000
0.00000
0.00000
0.00000
0.00000
0.00000
0.00000
0.00000
0.00000
0.00000
0.00000
0.00000
0.00000
0.00000
0.00000
0.00000
0.00000
0.00000
0.00000
0.00000
0.00000
0.00000
0.00000
0.00000
0.00000
0.00000
0.00000
0.00000
0.00000
0.00000
0.00000
0.00000
0.00000
0.00000
0.00000
0.00000
0.00000
0.00000
0.00000
0.00000
0.00000
0.00000
0.00000
0.00000
0.00000
0.00000
0.00000
0.00000
0.00000
0.00000
0.00000 0.00000
0.00000
0.00000
0.00000
0.00000
0.00000
0.00000
0.00000
0.00000
0.00000
0.00000
0.00000
0.00000
0.00000
0.00000
0.00000
0.00000
0.00000
0.00000
0.00000
0.00000
0.00000
0.00000
0.00000
0.00000
0.00000
0.00000
0.00000
0.00000
0.00000
0.00000
0.00000
0.00000
0.00000
0.00000
0.00000
0.00000
0.00000
0.00000
0.00000
0.00000
0.00000
0.00000
0.00000
0.00000
0.00000
0.00000
0.00000
0.00000
0.00000
0.00000
0.00000
0.00000
0.00000
0.00000
0.00000
0.00000
0.00000
0.00000
0.00000 0.00000
0.00000
0.00000
0.00000
0.00000
0.00000
0.00000
0.00000
0.00000
0.00000
0.00000
0.00000
0.00000
0.00000
0.00000
0.00000
0.00000
0.00000
0.00000
0.00000
0.00000
0.00000
0.00000
0.00000
0.00000
0.00000
0.00000
0.00000
0.00000
0.00000
0.00000
0.00000
0.00000
0.00000
0.00000
0.00000
0.00000
0.00000
0.00000
0.00000
0.00000
0.00000
0.00000
0.00000
0.00000
0.00000
0.00000
0.00000
0.00000
0.00000
0.00000
0.00000
0.00000
0.00000
0.00000
0.00000
0.00000
0.00000
0.00000
0.00000
0.00000 SPY
0.00000
0.00000
0.00000 BOBE
0.00000
0.00000
0.00000 SPY & BOBE Calculating Beta: Beta = Cov(BOBE,SPY)/Var(SPY) = #DIV/0! E(R):
Variance and Covariance:
Standard Deviation: Note: Beta measures the comovement of the company returns with the market. 0.00000 Two-State Binomial Option Pricing Model
Six-Month BOBE 30 Call:
Input Variables:
Current Spot Price (S0)
Strike Price (X)
Time until Expiration (T)
Volatility (?)
Borrow Rate
u
d Risk-Free Approach:
uS0
dS0
Call Payoff if 'u' (Cu)
Call Payoff if 'd' (Cd)
Hedge Ratio (H)
Amount Borrowed
Call Premium (C0) Risk-Neutral Approach:
Probability of 'u' (Pru)
Expected Payoff
Call Premium (C0) Continuous
$39.53
$35.00 Annual
$39.53
$35.00 3.75% 3.75% Black-Scholes Option Pricing Model
Six-Month BOBE 30 Call:
Input Variables:
Current Spot Price (S0)
Strike Price (X)
Time until Expiration (T)
Volatility (?)
Borrow Rate Black-Scholes:
d1
d2
N(d1)
N(d2)
Call Premium (C0) $39.53
$35.00 3.75%