Calculate the monthly returns for IBM (0.60%) stock as well as the risk of the stocks monthly returns over the five year period (1.23)..

1. Using the risk and return, provide a brief description of the stock. [4 marks]
2. Estimate the monthly returns of the index. [2 marks]
3. Using the regression function in Microsoft Excel, run a regression between Excess monthly returns of the stock market index (i.e S&P 500 ) versus Excess returns from your individual stocks according to the equation below.

Excess Returns: R(s) - rfree = (Market return risk free rate)

Alternatively you could also use the regression equation

R(s) = rfree + (Market return risk free rate)

For your risk free rate, use a long term government bond. For the US market, use the yield on the 10-year Treasury bond. Since you are comparing with monthly returns, you will need to divide the annual yield by 12 to get a monthly equivalent.

For example, the annual yield on 10-year US Treasury bond on 7th October was 2.36%. This means the monthly equivalent shall be 2.36/12 = 0.197% per month or 0.00197 per month.

From the regression results extract the beta for your stock. [4 marks]

4. Generate a scatterplot from the two sets of excess return data with the excess market returns as your x variable and the excess individual stock return as your y variable. Generate the beta of the individual stock by fitting a trendline to the scatterplot.

[3 marks]

5. Explain the significance of the beta value to a lay person. [2 marks]