1. The first thing you need to do is to calculate the returns for the market index and your stock. Note: The Treasury Bill yield is a rate of return so you do not need to calculate this (for example, the value of 0.26% in December 2015 means that the monthly yield or rate of return on 3-month Treasury Bills was 0.26%).

To calculate the returns for the stock and index you simply calculate the percentage change in price month by month. Note: This ignores dividends but this is fine for our purposes. The important thing is to calculate these returns on a consistent basis with the Treasury Bill yield which is monthly. Since there are 33 months of price data you should have 32 months of returns. Calculate the average return and standard deviation for the returns of your stock, the index, and the Treasury Bill (using the corresponding 32-month T-bill data) and insert the results in Table 1. Choose four decimal places in order to make the results easy on the eye. Also make sure the decimal places you choose are consistent in all cells.

Table 1

	Market	Your stock	Treasury Bill yield
Average	19.25%	0.65%	0.05%
Standard deviation	156.2	3.60	0.055

2. Complete the correlation matrix in Table 2 below. To do this you might find the Excel function CORREL useful. Present the correlations using two decimal places.

Table 2

Market

Your stock

Market

1

0.851

Your stock

0.851

1

3. Think about the results in Tables 1 and 2 and provide brief comments regarding the following several aspects:

(a) Look at the standard deviation values between your stock and Treasury Bills. What do they indicate?

(b) What is the correlation between the S&P 500 index and your stock?

(c) How do you feel about the sensitivity of your securitys returns to those of the market?

1.4 Estimate the CAPM for your stock. The formula for the CAPM is:

rj=rrf+bjrm-rrf

You can use slope function to calculate the BETA (systematic risk) as discussed in the lecture but you can also use the regression tool in Excel to estimate the complete CAPM. You are advised to use the regression facility in data analysis which you should find on the Data tab. If you have not got data analysis on your Data tab, see this short video for how to enable it:

https://www.youtube.com/watch?v=6nCP65Nbm0E (Excel 2007)

https://youtu.be/DoAckgj-jUk (Excel 2010)

https://www.youtube.com/watch?v=fuhR8tG7QRs (Excel 2013)

https://www.youtube.com/watch?v=mIoS7IRo36c (Excel 2016)

First, you need to rearrange the equation by subtracting the risk-free rate from both sides and allow for an intercept which we call alpha (a) giving the empirical version of the CAPM:

rj-rrf=j+bjrm-rrf

In order to estimate this regression, you first need to construct the variables. You create the y-variable (also known as the dependent variable) by subtracting the risk-free rate (the Treasury Bill yield) from the returns of your stock and you create the x-variable (the independent variable) by subtracting the risk-free rate from the return on the market (the S&P 500). The regression tool will enable you to estimate the values of alpha (the intercept) and the beta (the slope coefficient).

Insert a scatter plot chart below using your stock risk premium as the y-axis and market risk premium as the x-axis (Hint: Option under the Insert tab in Excel). Then add a trendline.

[Insert chart here]

Record the essential information from the regression in Table 3.

Table 3

	Your stock
Intercept (a) coefficient
Intercept standard error
Slope (b) coefficient
Slope standard error
R2
n (sample size)

1.5 Interpret the intercept and slope coefficients what are they telling us? (Max 150 words)

[Write your answer to 1.5 here]

(Max 150 words)

need 1.4 and 1.5