1. The first row must contain ONLY the MI owing info:
Go to the "View" tab-)Freeze Panes-+Freeze top row
2. Insert the table
3. You are required to calculate the required rate of return ( rril for all 3 stocks, market, and risk-free rate. Assume the following:
A. Use the compounded an nual return on the market as the expected market return.
B. Assume the SAI BOR rate (proxy for the risk free rate) is 4% annually and never changed for the past 4 years and it is expected to remain at that level in the near future.
C. fn order to calculate the beta
a. You could use the regression analysis to estimate the beta or use the slope function.
b. Your estimation should he based on the downloaded daily stock prices. Ella might get negative or very low betas. That Wine,
4. Draw the SML and show the following:
A. The security market line itself.
B. The req u i re d rate of return for all S assets
C. The expected return for all S assets jassurne that the calculated compounded annual retort! is Kauai to the expected return!
IX Name the chart and name both axises
5. Identify which stock is over, under, or fairly-valued.
It should be in excel format generating 8 different sheets requirements.
After your Daily data is cleaned, keep only 1 date column and all stock . Copy these data and paste them as values in the sheet.
The order of your stock prices must be in the same order as that shown below, Also date must be in an ascending order
Your final data will look like the following example
| |
Daily Stock Prices |
|
Date
|
TASI |
Your Firm |
firm 1 |
firm 2 |
| 4/14/2013 |
7,195.67 |
45.88 |
93.75 |
25.50 |
|
4/15/2013
|
7,145, 60
|
45.00
|
9350
|
25.10
|
|
4/16/2013
|
7,096.99
|
43.50
|
92.50
|
25.00
|
|
4/17/2013
|
7,060.04
|
44.12
|
91.25
|
25.30
|
|
4/21/2013
|
7,099.17
|
45.00
|
90.50
|
25.60
|
|
4/22/2013
|
7,124.30
|
46.62
|
91.25
|
26.30
|
|
4/6/2015
|
8,851.83
|
104.57
|
80.03
|
23.72
|