Calculate the monthly returns for the IBM stock as well as the risk of the stocks monthly returns over the five year period.

i.Using the risk and return, provide a brief description of the stock. [4 marks] ii.Estimate the monthly returns of the index. [2 marks] iii.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] iv.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] v.Explain the significance of the beta value to a lay person. [2 marks]