EXCEL：财政金融建模Chapter 10.xlsVIP

Page 150 (中文) Page 150 Page 149 (中文) Page 149 Page 148 Page 147(中文) 147 (中文) 147 Page 144,(中文) Page 144, Page 143, (中文) Page 143, AMR BS GE HR MO UK SP500 Mean Beta Intercept Slope R-squared SUMMARY OUTPUT Multiple R R Square Adjusted R Square df SS MS Coefficients t Stat X Variable 1 Regressing the means on the betas: F Significance F P-value Lower 95% Upper 95% =COVAR(B4:B13,$H$4:$H$13)/VARP($H$4:$H$13) =SLOPE(B4:B13,$H$4:$H$13) -- =INTERCEPT(B15:G15,B16:G16) -- =SLOPE(B15:G15,B16:G16) -- =RSQ(B15:G15,B16:G16) THE SECURITY MARKET LINE--A SIMPLE EXAMPLE Variance-covariance matrix Means Minus Constant Calculating two efficient portfolios z x y Variance Covariance -- =MMULT(TRANSPOSE(E17:E22),J6:J11) -- =MMULT(MMULT(TRANSPOSE(E17:E22),C6:H11),E17:E22) -- =SQRT(E25) -- =MMULT(MMULT(TRANSPOSE(E17:E22),C6:H11),K17:K22) -- =C19/SUM($C$17:$C$22) Sigma Data for SP500 returns -- =AVERAGE(P4:P13) -- =STDEVP(P4:P13) Calculation for a single portfolio Proportion x Proportion y -- =D30*D24+D31*K24 -- =D30^2*D25+D31^2*K25+2*D30*D31*D27 -- =SQRT(D34) Portfolio proportion mean Step Cell D39 is the change in the portfolio proportion in the data table to the right. -- Data table header means minus constant calculating two efficient portfolios constant variance sigma covariance Calculations for a single portfolio Prop. x Prop. y Market portfolio ANNUAL RETURNS ON SIX ASSETS AND MARKET PORTFOLIO Beta with respect to market =SLOPE(E50:E59,$L$50:$L$59) -- =MMULT(MMULT(K17:P17,C6:H11),I17:I22) -- =SQRT(I25) -- =MMULT(TRANSPOSE(I17:I22),J6:J11) -- =MMULT(MMULT(TRANSPOSE(I17:I22),C6:H11),I17:I22) -- =$D$30*E39+$D$31*F39 Regressing the means on the betas -- =INTERCEPT(E61:J61,E63:J63) -- =RSQ(E61:J61,E63:J63) -- =SLOPE(E61:J61,E63:J63) The Two Efficient Portfolios Proportion Individual Asset Proportions of x Step is the increment in the proportion of x in the table at the left. 均值在贝塔系数上的回归 均值 贝塔系数 回归统计 标准误差 观测值 方差分析 回归分析 残差 总计 下限 95.0% 上限 95.0% 均值 常数 方差 标准差 协方差 方差-协方差