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

Data for page 156,157 chart Page 157 (中文) Page 157 Page 156 (中文) Page 156 155 (中文) 155 constant constant epsilon getformula Portfolio_mean Portfolio_mean Portfolio_sigma Portfolio_sigma results results Total var_cov x_1 x_1 x_2 x_2 x_3 x_3 x_4 x_4 NO SHORT SALES Variance-covariance matrix Means Optimal portfolio proportions Total Portfolio mean Portfolio sigma Theta c RESULTS Sigma Mean minus c z x y transpose x transpose y mean variance covariance sigma prop. x start step down to here Note: To get the two data series (with and without short sales) to chart on the same set of axes, we copy all the sigmas in one column and then copy the means in two adjacent columns. The graph is produced with an XY graph format. Ctrl+A works the VBA program which calculates efficient portfolios for no-short sales. This program iteratively substitutes a constant ranging from -3.5% till 16% (1/2% jumps) and calculates the optimal portfolio. x1 x2 x3 x4 -- This is the constant -- =(B18-C9)/B19 -- {=MMULT(TRANSPOSE(C12:C15),G4:G7)} -- {=SQRT(MMULT(TRANSPOSE(C12:C15),MMULT(B4:E7,C12:C15)))} Note: Because the formulas in cells B18 and B19 use the TRANSPOSE function, they must be entered as arrays, with [Ctrl]-[Shift]-[Enter] . The curly brackets are entered automatically by Excel. See section 9.4 of Chapter 9 for more details. x1 x2 x4 x3 To compute the values given in pages 201-204: Substitute in the appropriate value for c, and then bring up the Excel Solver: Tools|Solver. In most cases this is enough; however, you may have to load the Solver: Tools|Add-ins, then click the box for the Solver Add-in. 方差-协方差矩阵 均值 不允许卖空 投资组合均值 θ 方差-协方差矩阵 x1 x2 x3 x4 投资组合标准差 注意:因为在单元格B18、B19中的公式使用了TRANSPOSE函数,它们必须用 [Ctrl]-[Shift]-[Enter]输入数组。单元格中的花括号是输入时Excel自动生成的。 详见第9章的第9.4节。 -- 这是常数 标准差 不允许卖空 允许卖空 x1 x2 x3 x4 最优投资组合比例 按Ctrl+A 运行VBA程序 计算不允许卖空的 有效投资组合。 这个程序中常数的取值 从 -3.5% 到16% (1/2% 步长) 。 均值减c 方差－协方差矩阵 转置x 转置y x的比例 标准差 方差 步长 初始 .20 .09 .20 .09 .20 .09 .20 .09 .20 .09 .20 .09 .20 .09 .20 .09 .20 .09