XYZ Co stock has the price of $30, $32, $28, $31, $35, $34, $40, $38, $42, $45 and $50 from 2010 to 2020, while a stock market index has the value of 100, 105, 102, 110, 125, 120, 135, 130, 145, 155 and 170 from 2010 to 2020. The stock is expected to pay dividend of $3 in one year and $3 in two years. Its expected price in two years is $60. The risk-free rate is 3%. The stock market index has an expected return of 12%.

1. Enter the stock prices and the stock market index into your Excel file column wise. (Two columns and 11 rows), Derive the annual returns of the stock and the stock market index.

2. Use Excel Regression under Data Analysis to run a linear regression using stock return as y variable and stock market index as x variable to estimate the beta of the stock.

3. Find standard deviation of stock and of stock index, and the correlation coefficient of stock return and stock index return to estimate the beta of the stock. Does the stock have a greater risk than the stock market index? Explain.

4. Use the security market line to determine the required rate of return of the stock.

5. Determine the per share value of the stock.