Question
Essentials of Investments.Estimating the Index Model Using Historical Data.
Reference:ISBN9781259255045.page173
(Some data is calculated by me, if there are any mistakes, please help me to correct it, thank you)
excess return DATE ABC return rate(%) XYZ return rate(%) mkt index return rate(%) risk free(SHIBOR)% ABC XYZ market 2009/12/25 23.17 13.22 3141.35 0.043 2009/12/31 25.52 9.660 13.82 4.439 3277.14 4.232 0.043 9.617 4.395 4.189 2010/1/8 28.13 9.737 13.45 -2.714 3196 -2.507 0.043 9.694 -2.757 -2.551 2010/1/15 28.59 1.622 13.78 2.424 3224.15 0.877 0.044 1.578 2.380 0.833 2010/1/22 27.32 -4.544 13.53 -1.831 3128.59 -3.009 0.045 -4.588 -1.876 -3.053 2010/1/29 26.05 -4.760 13.08 -3.383 2989.29 -4.555 0.045 -4.805 -3.427 -4.600 2010/2/5 26.33 1.069 12.71 -2.870 2939.4 -1.683 0.045 1.024 -2.914 -1.728 2010/2/12 26.56 0.870 13.01 2.333 3018.13 2.643 0.045 0.825 2.288 2.598 2010/2/26 26.58 0.075 13 -0.077 3051.94 1.114 0.045 0.030 -0.122 1.069 2010/3/5 27.31 2.709 12.8 -1.550 3031.07 -0.686 0.045 2.664 -1.596 -0.731 2010/3/12 27.25 -0.220 12.82 0.156 3013.41 -0.584 0.045 -0.265 0.111 -0.630 2010/3/19 27.71 1.674 12.86 0.312 3067.75 1.787 0.045 1.629 0.266 1.742 2010/3/26 26.31 -5.184 12.77 -0.702 3059.72 -0.262 0.045 -5.230 -0.748 -0.307 2010/4/2 26.37 0.228 12.98 1.631 3157.96 3.160 0.045 0.183 1.586 3.115 2010/4/9 26.16 -0.800 12.81 -1.318 3145.35 -0.400 0.045 -0.845 -1.364 -0.445 2010/4/16 26.32 0.610 13.04 1.780 3130.3 -0.480 0.045 0.564 1.734 -0.525 2010/4/23 23.06 -13.223 12.25 -6.250 2983.54 -4.802 0.045 -13.268 -6.295 -4.847 2010/4/30 20.04 -14.037 12.1 -1.232 2870.61 -3.859 0.046 -14.082 -1.278 -3.904 2010/5/7 17.83 -11.685 11.04 -9.168 2688.38 -6.559 0.046 -11.730 -9.214 -6.604 2010/5/14 17.65 -1.015 11.15 0.991 2696.63 0.306 0.046 -1.060 0.946 0.261 2010/5/21 16.24 -8.326 10.84 -2.820 2583.52 -4.285 0.046 -8.371 -2.865 -4.331 2010/5/28 16.65 2.493 10.96 1.101 2655.77 2.758 0.046 2.447 1.055 2.712 2010/6/4 16.28 -2.247 10.61 -3.246 2553.59 -3.923 0.048 -2.295 -3.293 -3.971 2010/6/11 16.37 0.551 10.69 0.751 2569.94 0.638 0.048 0.503 0.703 0.590 2010/6/18 15.77 -3.734 10.58 -1.034 2513.2 -2.233 0.049 -3.783 -1.083 -2.281 2010/6/25 15.91 0.884 10.71 1.221 2552.82 1.564 0.049 0.834 1.172 1.515 2010/7/2 15.23 -4.368 10.33 -3.613 2382.9 -6.888 0.050 -4.418 -3.663 -6.938 2010/7/9 15.46 1.499 10.53 1.918 2470.92 3.627 0.050 1.449 1.867 3.577 2010/7/16 15.03 -2.821 10.21 -3.086 2424.27 -1.906 0.050 -2.871 -3.136 -1.956 2010/7/23 16.32 8.234 10.55 3.276 2572.03 5.917 0.050 8.184 3.226 5.866 2010/7/30 17.18 5.135 10.71 1.505 2637.5 2.514 0.050 5.085 1.455 2.463 2010/8/6 17.21 0.174 10.67 -0.374 2658.39 0.789 0.050 0.124 -0.424 0.739 2010/8/13 15.92 -7.791 10.32 -3.335 2606.7 -1.964 0.050 -7.842 -3.386 -2.014 2010/8/20 15.91 -0.063 10.42 0.964 2642.31 1.357 0.050 -0.113 0.914 1.306 2010/8/27 15.45 -2.934 10.31 -1.061 2610.74 -1.202 0.051 -2.985 -1.112 -1.253 2010/9/3 15.24 -1.369 10.24 -0.681 2655.39 1.696 0.051 -1.419 -0.732 1.645 2010/9/10 15.28 0.262 10.18 -0.588 2663.21 0.294 0.051 0.211 -0.639 0.243 2010/9/17 14.7 -3.870 9.98 -1.984 2598.69 -2.452 0.051 -3.921 -2.035 -2.504 2010/9/21 14.62 -0.546 9.99 0.100 2591.55 -0.275 0.051 -0.597 0.049 -0.326 2010/9/30 14.19 -2.985 10.18 1.884 2655.66 2.444 0.052 -3.037 1.833 2.392 2010/10/8 14.87 4.681 10.47 2.809 2738.74 3.080 0.052 4.629 2.757 3.029 2010/10/15 17.79 17.929 11.55 9.817 2971.16 8.145 0.052 17.877 9.766 8.094 2010/10/22 17.59 -1.131 11.12 -3.794 2975.04 0.131 0.055 -1.186 -3.849 0.075 2010/10/29 17.67 0.454 11.19 0.628 2978.84 0.128 0.056 0.398 0.571 0.071 2010/11/5 18.67 5.505 11.99 6.905 3129.5 4.934 0.056 5.449 6.849 4.877 2010/11/12 16.43 -12.781 12.54 4.485 2985.44 -4.713 0.057 -12.838 4.428 -4.770 2010/11/19 15.6 -5.184 11.28 -10.589 2888.57 -3.299 0.057 -5.241 -10.647 -3.356 2010/11/26 15.25 -2.269 10.98 -2.696 2871.7 -0.586 0.059 -2.328 -2.754 -0.645 2010/12/3 15.03 -1.453 11.24 2.340 2842.43 -1.024 0.060 -1.513 2.280 -1.085 2010/12/10 15.45 2.756 11.45 1.851 2841.04 -0.049 0.061 2.695 1.790 -0.110 2010/12/17 15.71 1.669 11.56 0.956 2893.74 1.838 0.062 1.607 0.894 1.776 2010/12/24 15.28 -2.775 11.42 -1.218 2835.16 -2.045 0.065 -2.841 -1.284 -2.110 2010/12/31 14.95 -2.183 11.22 -1.767 2808.08 -0.960 0.070 -2.253 -1.837 -1.030 Average -0.893 -0.366 -0.266 covariance matrix ABC XYZ market ABC 31.2585 11.6420 13.2647 XYZ 11.2578 7.7192 market 9.0482 SUMMARY OUTPUT EXCEL REGRESSION Regression Statistics Multiple R ? R-Square ? Adj.R-Square ? Standard Error ? Observations ? Coefficients Std. Error t-stat p-value Intercept ? ? ? ? Marker return ? ? ? ?
A direct way to calculate the slope and intercept of the characteristic lines for ABC and XYZ is from the variances and covariances. Here, e use the Data Analysis menu of Excel to obtain the coy ance matrix in the following spreadsheet the covari The slope coefficient for ABC is given by the formula Cos(RABc-Rune.) = 773.31 = 1.156 ADC The intercep or ABC is 0asc = Average(R,sc)-A.DC × Average(e. -15 20-1 . 1 56 × 9.40-4.33 Therefore, the security characteristic line of ABC is given by RABC = 4.334. 1.1 56RMatket This result also can be obtained by using the Regression command from Excel's Data Analys menu, as we show at the bottom of the spreadshee. The minor differences between the direct regression output and our calculations above are due to rounding error, Annualized Rates of Retue Excess Rctues Week ARC 65.13 5104 30,82 Y2 Mk1. Index Risk tree .23 27.759. 24.00 6.22 9492-39.3 70.63 107.92 25.16 50.48 3641 42.20 33.73 59 23.13 8.54 2537 66.20 104.04 29.03 46 33 66.32 467 48.79 S2.63 3.99 20.21 48.02 16 20 7.55 9.40 14 Averme 15.20 16 COVARIANCE MATALK ABC 8 ABC 19 XY2 3020.933 66.923 396.789 9010 442.114 73.306 22 SUMMARY OUTPUT OF EXCEL AEGRESSION 24Regrossion Statistics 25 Multpic R 26 R-Square 27 Ad R Square 28 Standard Eror 29 Observations 30 0296 0.208 32 33 Intercept 34 Market return 35 Coefficients 4.336 1.156 0.800 0.104 0.262 16.564 0.630 Mote: This is the output provided by the Data Analysis tool in Excel. As a technical aside, we should point out that the covariance matrix produced by Excel does not adjust for degrees of freedom. In other words, it divides total quared deviations from the sample average (for variance) or total cross product of deviations from sample aver (for covariance) by total observations, despite the fact that sample averages are estimated parameters. This procedure does not affect regression coefficients, however, because in the formula for beta, both the numerator ue, the covariance) and denominator (i.e., the variance) are affected equally.
Explanation / Answer
excess return DATE ABC return rate(%) XYZ return rate(%) mkt index return rate(%) risk free (SHIBOR)% ABC XYZ market 25-12-2009 23.17 13.22 3141.35 0.043 31-12-2009 25.52 9.660 13.82 4.439 3277.14 4.232 0.043 9.617 4.395 4.189 08-01-2010 28.13 9.737 13.45 -2.714 3196 -2.507 0.043 9.694 -2.757 -2.551 15-01-2010 28.59 1.622 13.78 2.424 3224.15 0.877 0.044 1.578 2.380 0.833 22-01-2010 27.32 -4.544 13.53 -1.831 3128.59 -3.009 0.045 -4.588 -1.876 -3.053 29-01-2010 26.05 -4.760 13.08 -3.383 2989.29 -4.555 0.045 -4.805 -3.427 -4.600 05-02-2010 26.33 1.069 12.71 -2.870 2939.4 -1.683 0.045 1.024 -2.914 -1.728 12-02-2010 26.56 0.870 13.01 2.333 3018.13 2.643 0.045 0.825 2.288 2.598 26-02-2010 26.58 0.075 13 -0.077 3051.94 1.114 0.045 0.030 -0.122 1.069 05-03-2010 27.31 2.709 12.8 -1.550 3031.07 -0.686 0.045 2.664 -1.596 -0.731 12-03-2010 27.25 -0.220 12.82 0.156 3013.41 -0.584 0.045 -0.265 0.111 -0.630 19-03-2010 27.71 1.674 12.86 0.312 3067.75 1.787 0.045 1.629 0.266 1.742 26-03-2010 26.31 -5.184 12.77 -0.702 3059.72 -0.262 0.045 -5.230 -0.748 -0.307 02-04-2010 26.37 0.228 12.98 1.631 3157.96 3.160 0.045 0.183 1.586 3.115 09-04-2010 26.16 -0.800 12.81 -1.318 3145.35 -0.400 0.045 -0.845 -1.364 -0.445 16-04-2010 26.32 0.610 13.04 1.780 3130.3 -0.480 0.045 0.564 1.734 -0.525 23-04-2010 23.06 -13.223 12.25 -6.250 2983.54 -4.802 0.045 -13.268 -6.295 -4.847 30-04-2010 20.04 -14.037 12.1 -1.232 2870.61 -3.859 0.046 -14.082 -1.278 -3.904 07-05-2010 17.83 -11.685 11.04 -9.168 2688.38 -6.559 0.046 -11.730 -9.214 -6.604 14-05-2010 17.65 -1.015 11.15 0.991 2696.63 0.306 0.046 -1.060 0.946 0.261 21-05-2010 16.24 -8.326 10.84 -2.820 2583.52 -4.285 0.046 -8.371 -2.865 -4.331 28-05-2010 16.65 2.493 10.96 1.101 2655.77 2.758 0.046 2.447 1.055 2.712 04-06-2010 16.28 -2.247 10.61 -3.246 2553.59 -3.923 0.048 -2.295 -3.293 -3.971 11-06-2010 16.37 0.551 10.69 0.751 2569.94 0.638 0.048 0.503 0.703 0.590 18-06-2010 15.77 -3.734 10.58 -1.034 2513.2 -2.233 0.049 -3.783 -1.083 -2.281 25-06-2010 15.91 0.884 10.71 1.221 2552.82 1.564 0.049 0.834 1.172 1.515 02-07-2010 15.23 -4.368 10.33 -3.613 2382.9 -6.888 0.050 -4.418 -3.663 -6.938 09-07-2010 15.46 1.499 10.53 1.918 2470.92 3.627 0.050 1.449 1.867 3.577 16-07-2010 15.03 -2.821 10.21 -3.086 2424.27 -1.906 0.050 -2.871 -3.136 -1.956 23-07-2010 16.32 8.234 10.55 3.276 2572.03 5.917 0.050 8.184 3.226 5.866 30-07-2010 17.18 5.135 10.71 1.505 2637.5 2.514 0.050 5.085 1.455 2.463 06-08-2010 17.21 0.174 10.67 -0.374 2658.39 0.789 0.050 0.124 -0.424 0.739 13-08-2010 15.92 -7.791 10.32 -3.335 2606.7 -1.964 0.050 -7.842 -3.386 -2.014 20-08-2010 15.91 -0.063 10.42 0.964 2642.31 1.357 0.050 -0.113 0.914 1.306 27-08-2010 15.45 -2.934 10.31 -1.061 2610.74 -1.202 0.051 -2.985 -1.112 -1.253 03-09-2010 15.24 -1.369 10.24 -0.681 2655.39 1.696 0.051 -1.419 -0.732 1.645 10-09-2010 15.28 0.262 10.18 -0.588 2663.21 0.294 0.051 0.211 -0.639 0.243 17-09-2010 14.7 -3.870 9.98 -1.984 2598.69 -2.452 0.051 -3.921 -2.035 -2.504 21-09-2010 14.62 -0.546 9.99 0.100 2591.55 -0.275 0.051 -0.597 0.049 -0.326 30-09-2010 14.19 -2.985 10.18 1.884 2655.66 2.444 0.052 -3.037 1.833 2.392 08-10-2010 14.87 4.681 10.47 2.809 2738.74 3.080 0.052 4.629 2.757 3.029 15-10-2010 17.79 17.929 11.55 9.817 2971.16 8.145 0.052 17.877 9.766 8.094 22-10-2010 17.59 -1.131 11.12 -3.794 2975.04 0.131 0.055 -1.186 -3.849 0.075 29-10-2010 17.67 0.454 11.19 0.628 2978.84 0.128 0.056 0.398 0.571 0.071 05-11-2010 18.67 5.505 11.99 6.905 3129.5 4.934 0.056 5.449 6.849 4.877 12-11-2010 16.43 -12.781 12.54 4.485 2985.44 -4.713 0.057 -12.838 4.428 -4.770 19-11-2010 15.6 -5.184 11.28 -10.589 2888.57 -3.299 0.057 -5.241 -10.647 -3.356 26-11-2010 15.25 -2.269 10.98 -2.696 2871.7 -0.586 0.059 -2.328 -2.754 -0.645 03-12-2010 15.03 -1.453 11.24 2.340 2842.43 -1.024 0.060 -1.513 2.280 -1.085 10-12-2010 15.45 2.756 11.45 1.851 2841.04 -0.049 0.061 2.695 1.790 -0.110 17-12-2010 15.71 1.669 11.56 0.956 2893.74 1.838 0.062 1.607 0.894 1.776 24-12-2010 15.28 -2.775 11.42 -1.218 2835.16 -2.045 0.065 -2.841 -1.284 -2.110 31-12-2010 14.95 -2.183 11.22 -1.767 2808.08 -0.960 0.070 -2.253 -1.837 -1.030 Average 19.424717 11.570566 2833.382 -0.893 -0.366 -0.266 Covariance matrix ABC XYZ market Use function "Covariance.P" with 2 corresponding arrays as parameters ABC 24.6674513 5.25295205 921.4120091 XYZ 5.25295205 1.33386572 246.3652781 market 921.412009 246.365278 54844.04193 SUMMARY OUTPUT EXCEL REGRESSION Regression Statistics Use data analysis add-in and choose option "regression" with ABC returns array as y-variable or response variable and market index returns as independent or x-variable Multiple R 0.78872273 R-Square 0.62208355 Adj.R-Square 0.61452522 Standard Error 3.53857028 Observations 52 Coefficients Std. Error t-stat p-value Intercept -0.52646162 0.49194757 -1.070157976 0.2896872 Marker return 1.46581045 0.16157209 9.072175888 3.831E-12
