Excel Exercise: Return and Beta Calculation
Excel Return Exercise
The following video demonstrates how to get started on the Return and Beta Calculation exercise. You should download the Excel template file (file is downloaded and attached) to start working on the assignment.
Watch video for how to accomplish the assignment:
https://www.youtube.com/watch?v=Q7uniN25vno&feature=youtu.be
Analyzing Historical Risk vs Return for a Company
Choose a company that you are using in the investment challenge and complete the following steps.
Follow the 6 steps below.
Step 1: Gather the most recent 61 monthly stock prices for the S&P500 and your company using http://finance.yahoo.com.
a. Go to the above website and enter "^GSPC" in the Enter Symbol box in upper left-hand home page of Yahoo! Finance.
b. Click on Historical Prices on the left-hand side.
c. Choose a start date 5 years prior to today's date, change to monthly, and click Get Historical Data to get 61 months of data.
(You need 61 observations to calculate 60 returns.)
d. Scroll down to the bottom of the page click on Download Spreadsheet Format and copy (DO NOT CUT) the closing prices and dates into the highlighted areas of this spreadsheet under the tab 2.Calc. Returns below
Step 2: Repeat this process of step 1 for a company of your choosing.
NOTE: The price series is converted into a return series by calculating Returnt = (Pt+1-Pt)/Pt + Divt+1/Pt.
When the information is downloaded using adjusted closing prices the Prices are automatically adjusted to include dividend information.
Therefore it is only necessary to calculate the change in Price divided by the beginning monthly Price to calculate returns.
This is automatically done for you in the spreadsheet tab 2.Calc. Returns, by copying the adjusting monthly closing prices to cell D2:D62.
Make sure you have 61 monthly observations with the same starting month for your company and S&P 500 data.
Step 3: Interpretation of Line Graph. (Graph is automatically created hit tab Line Graph at bottom of spreadsheet.)
Refer to the Line Graph spreadsheet to answer the following questions. (Note place cursor on a point to get values.)
The returns for your company and the S&P 500 are shown in reverse chronological order (most recent is first).
Helpful Hint: Placing the cursor on the point in the line graph will display the actual numerical input values for that point.
a. What is the most recent month in which the company returns moves in the opposite direction of the S&P 500 return? (i.e. the monthly return is going up and the market return is going down, or v.v.)
b. In what month was the largest positive monthly return for the company in the past five years?
c. What was the largest positive monthly return?
d. In what month was the largest negative monthly return for the company in the past five years?
e. What was the largest negative monthly return?
f. Based on the line Graph 1 would you say that your company returns and S&P 500 returns ________.
1) always move in opposite directions (i.e. when one is positive the other will be negative).
2) seldom move in the same direction.
3) sometimes move in the same direction.
4) often move in the same direction.
5) always move in the same direction.
Step 4: Explanation of Statistical Calculations
Using the Calculating Statistic spreadsheet and your text book write the formulas and describe the calculations for the following cells:
Cell I68 is shown as an example.
I68 a. Expected Monthly Return (E(R)) for the company:
= C66/60, where C66 is the sum of monthly returns, therefore this is the average monthly return for company
I69 b. Variance for the company:
I70 c. Standard Deviation for the company:
F68 d. Expected Return (E(R)) for the market index:
F69 e. Variance for the market index:
F70 f. Standard Deviation for the market index:
Step 5: Compare the calculated Beta with the reported Beta on Yahoo! Finance.
Covariance is a statistical measurement that caculates how two series move in relationship to each other.
The calculations for covariance are shown in collumn H of the 5.Calc Beta spreadsheet.
Answer the following questions referring to the Calculate Beta spreadsheet.
a. What is the most recent month for which the company return and market return were less than their expected returns? (i.e. both had negative amounts in collumn E and collumn G.)
b. Was the covariance for this month positive or negative?
c. What is the most recent month for which the company return and market return were greater than their expected returns?
d. Was the covariance for this month positive or negative?
e. What is the most recent month for which the company return and market return moved in opposite directions than their expected returns?
f. Was the covariance for this month positive or negative?
g. Look up your company's Beta on Yahoo!Finance by doing the following steps:
1. Enter the ticker symbol under get quotes.
2. Click on Key Statistics on the left-hand side
3. Beta is on the right-hand side of the screen, one of the first statistical measures reported.
Don't be surprised if your Beta is different than the beta reported on yahoo finance.
Regression Instructions
Select Data and then Data Analysis from top options of Excel. (See instructions on Regression tab if Data Analysis option is not there).
Select Regression.
Input the Company Returns (collumn C of 6.Regression spreadsheet for Y variable)
Input the Market Risk Premium (collumn B of 6.Regression spreadsheet for X var.)
Select OK and Regression Output should be created on a new page.
Print out the output that was generated and answer the following questions on the output page NOTE: There is an Example of MLHR Regression Output on Tab below.
1. On the output page identify and circle the following
a. y-intercept
b. measurement of how well regression model estimates company returns.
c. How well does this regression model fit the data?
2. Write the regression equation from the regression output.
Attachment:- Assignment.rar