The capital asset pricing model (CAPM) is a widely accepted, though controversial, theory of asset pricing in the capital market. According to CAPM, the expected return of any asset in the capital market is a linear function of the expected return on the whole market and the expected return of the risk-free rate. Mathematically, the model is stated as per Equation 1:

E(R_e) = E(RFR) + *E(R_m - RFR) (1)

WhereE(R_e)is the expected rate of return on a specific asset,E(RFR) is the expected risk-free rate, is the sensitivity of the stock return with respect to the overall market return, andE(R_m - RFR) is the expected capital market risk premium.

Empirical verification of CAPM is done by running a regression model of historical returns of stocks against historical returns of the overall market.

Below is provided data of Apple in stock and the S&P 500 Index https://docs.google.com/spreadsheets/d/1ngogQVkV61SLF5E7Wfq8zBhJPB5CJUF555MLEy7kbPY/edit?usp=sharing https://docs.google.com/spreadsheets/d/1ursCU5kwoJnJrLADV2Nvibo_9_vZ0KkIxWQGTRTFkyc/edit?usp=sharing

1. Use the adjusted closing prices of the Apple stock and the S&P 500 index and calculate the monthly rate of return of each.
2. Consider the stock's monthly return as the dependent variable and the index's monthly return as the independent variable and run the following regression model:

R_e = + *R_m + (2)

Where R_e is the realized monthly rate of return of your stock,R_m is the realized monthly rate of return on the overall capital market, and is the error term.

1. After estimating the regression coefficients of equation (2) through the ordinary least squares (OLS) method, conduct a test of hypothesis and determine if the estimates of and are statistically significant at the 5% level and report theirt statistics andp values.
2. Determine if theF value for the correlation coefficient is statistically significant at the 5% level.
3. What is your interpretation of the R-square value? Explain to what extent your regression estimates can predict future return of your stock against your index's movements.
4. What is the estimate ofRFR?
5. Calculate the value of the error term for each year and construct the histogram of the error terms
6. Using the Excel: Data Analysis, Regression, Normal Probability Plots, conduct a test for normality of the error terms and exhibit the normality plot.
7. Does the result of the test for normality of the error terms affect the validity of your regression model? Explain.

Please provide me a detailed explanation and formula, I don't have excel, I need to do in Google sheets Thanks.