In: Finance
You perform the analysis on EXCEL and write out your analysis result in a separate word or pdf file.
Submit both EXCEL and WORD / PDF file.
Download daily adjusted close price from AAPL and SPY for the last 10 years INTO Spreadsheet. Create daily return.
1. What is the daily mean return and daily stdev for AAPL and SPY return? What is the correlation between the two?
Questions below DO NOT NEED EXCEL (only need numerical values from part 1):
==========================================================
If you run a market model regression:
ret_aapl = intercept + slope * spy_ret + error
2a. What is the value of intercept and slope?
2b What is the regression R-square?
2c. What is the stdev of regression error term?
2d. What is the 95% Confidence interval for the regression slope?
Summary of the regression output is given:
SUMMARY OUTPUT | ||||||||
Regression Statistics | ||||||||
Multiple R | 0.654086609 | |||||||
R Square | 0.427829292 | |||||||
Adjusted R Square | 0.427601698 | |||||||
Standard Error | 0.01307627 | |||||||
Observations | 2516 | |||||||
ANOVA | ||||||||
df | SS | MS | F | Significance F | ||||
Regression | 1 | 0.321424 | 0.321424 | 1879.794 | 3.9405E-307 | |||
Residual | 2514 | 0.429866 | 0.000171 | |||||
Total | 2515 | 0.75129 | ||||||
Coefficients | Standard Error | t Stat | P-value | Lower 95% | Upper 95% | Lower 95.0% | Upper 95.0% | |
Intercept | 0.000528961 | 0.000261 | 2.026562 | 0.042813 | 1.71366E-05 | 0.001040785 | 1.71366E-05 | 0.001040785 |
X Variable 1 | 1.047735944 | 0.024166 | 43.35659 | 3.9E-307 | 1.000349514 | 1.095122374 | 1.000349514 | 1.095122374 |
Part (1)
the daily mean return for AAPL: 0.000536 = 0.0536%
the daily mean return for SPY: 0.001090 = 0.1090%
daily stdev of AAPL: 0.010790 = 1.0790%
daily stdev of SPY: 0.017284 = 1.7284%
the correlation between the two = 0.654087 = 0.6541
Please look at the attached summary for all the answers of part (2)
below. All these figures are available somewhere on the outstput
summary.
Part (2)
ret_aapl = intercept + slope * spy_ret + error
Part (2a) Intercept = 0.000528961 = 0.05290%
Slope = 1.047735944 = 1.0477
Part (2b) Regresion R square = 0.427829292 = 0.4278
Part (2c) The stdev of regression error term = Standard Error = 0.01307627 = 1.3076%
Part (2d) the 95% Confidence interval for the regression slope =
(1.000349514, 1.095122374)