problems12

OPTIMIZING WITH SHORT SALE CONSTRAINTSA B C D E FA 0.0100 0.0000 0.0000 0.0000 0.0000 0.0000B 0.0000 0.0400 0.0000 0.0000 0.0000 0.0000C 0.0000 0.0000 0.0900 0.0000 0.0000 0.0000D 0.0000 0.0000 0.0000 0.1500 0.0000 0.0000E 0.0000 0.0000 0.0000 0.0000 0.2000 0.0000F 0.0000 0.0000 0.0000 0.0000 0.0000 0.3000Constant 10.00%Start -5.00%Iterations 15 Press Ctrl + a to run macroJump 0.01Portfolio weights 0A 0.0000B 0.0000C 0.0000D 0.0000E 0.0000F 1.0000Sum 1Portfolio mean 5.50% - =MMULT(TRANSPOSE(B17:B22),I4:I9)Portfolio sigma 54.77% - =SQRT(MMULT(MMULT(TRANSPOSE(B17:B22),B4:G9),B17:B22)Theta -8.22% - =(B25-B11)/B26Two efficient portfolios with shortsalesConstant 0% 1%A 0.3947 0.0000 - =MMULT(MINVERSE($B$4:$G$9),$I$4:$I$9-C32)/SUM(MMULT(MINVERSE($B$4:$G$9),$I$4:$I$9-C32)B 0.1974 0.2446C 0.1316 0.2174D 0.1053 0.1957E 0.0987 0.1957F 0.0724 0.1467Sum 1 1Expected 0.0250 0.0371 - =MMULT(TRANSPOSE(C34:C39),$I$4:$I$9)Sigma 0.0993 0.1628 - =SQRT(MMULT(MMULT(TRANSPOSE(C34:C39),$B$4:$G$9),C34:C39)Covariance 0.0146 - =MMULT(MMULT(TRANSPOSE(B34:B39),$B$4:$G$9),C34:C39)One portfolioProportion of first 0.25Proportion of second 0.75A B C D E F G H12345678910111213141516171819202122232425262728293031323334353637383940414243444546474849Expected return 3.41% - =B47*B42+B48*C42Sigma 14.50% - =SQRT(B472*B432+B482*C432+2*B47*B48*B44)Data tableSigma Mean No shortsProportion 14.50% 3.41%-2 31.98% 6.13%-1.6 28.74% 5.65%-0.6 20.80% 4.44%-0.4 19.27% 4.19%-0.2 17.76% 3.95%0 16.28% 3.71%0.2 14.85% 3.47%0.3 14.15% 3.35%0.6 12.17% 2.98%0.8 10.98% 2.74%1 9.93% 2.50%1.2 9.07% 2.25%1.4 8.46% 2.01%1.6 8.16% 1.77%1.8 8.20% 1.53%2 8.58% 1.28%Data for short sale 0.0831 0.0192restrictions 0.0839 0.01970.0854 0.02050.0888 0.02190.0993 0.02500.1628 0.03710.2175 0.04420.2757 0.04900.3536 0.05250.5477 0.05500.5477 0.05506 0 %5 0 %4 0 %3 0 %2 0 %1 0 %0 %0 %1 %2 %3 %4 %5 %6 %7 %A B C D E F G H505152535455565758596061626364656667686970717273747576777879808182ResultsMeanreturns ConstantPorfoliosigmaPorfoliomean A B C0.0100 -0.04 0.0831 0.0192 0.5829 0.1749 0.09070.0200 -0.03 0.0839 0.0197 0.5660 0.1769 0.09430.0300 -0.02 0.0854 0.0205 0.5400 0.1800 0.10000.0400 -0.01 0.0888 0.0219 0.4945 0.1854 0.10990.0500 0 0.0993 0.0250 0.3947 0.1974 0.13160.0550 0.01 0.1628 0.0371 0.0000 0.2446 0.21740.02 0.2175 0.0442 0.0000 0.0000 0.21740.03 0.2757 0.0490 0.0000 0.0000 0.00000.04 0.3536 0.0525 0.0000 0.0000 0.00000.05 0.5477 0.0550 0.0000 0.0000 0.00000.06 0.5477 0.0550 0.0000 0.0000 0.00000.07 0.5477 0.0550 0.0000 0.0000 0.00000.08 0.5477 0.0550 0.0000 0.0000 0.00000.09 0.5477 0.0550 0.0000 0.0000 0.00000.1 0.5477 0.0550 0.0000 0.0000 0.0000OPTIMIZING WITH SHORT SALE CONSTRAINTS- =SQRT(MMULT(MMULT(TRANSPOSE(B17:B22),B4:G9),B17:B22)- =MMULT(MINVERSE($B$4:$G$9),$I$4:$I$9-C32)/SUM(MMULT(MINVERSE($B$4:$G$9),$I$4:$I$9-C32)- =SQRT(MMULT(MMULT(TRANSPOSE(C34:C39),$B$4:$G$9),C34:C39)- =MMULT(MMULT(TRANSPOSE(B34:B39),$B$4:$G$9),C34:C39)I J K L M N O P Q123456789101112131415161718192021222324252627282930313233343536373839404142434445464748496 0 %5 0 %4 0 %3 0 %2 0 %1 0 %0 %0 %1 %2 %3 %4 %5 %6 %7 %I J K L M N O P Q505152535455565758596061626364656667686970717273747576777879808182D E F0.0622 0.0525 0.03690.0660 0.0566 0.04010.0720 0.0630 0.04500.0824 0.0742 0.05360.1053 0.0987 0.07240.1957 0.1956 0.14670.2609 0.2935 0.22830.2667 0.4000 0.33330.0000 0.5000 0.50000.0000 0.0000 1.00000.0000 0.0000 1.00000.0000 0.0000 1.00000.0000 0.0000 1.00000.0000 0.0000 1.00000.0000 0.0000 1.0000R S T12345678910111213141516171819202122232425262728293031323334353637383940414243444546474849卖空限制下的最优化A B C D E FA 0.0100 0.0000 0.0000 0.0000 0.0000 0.0000B 0.0000 0.0400 0.0000 0.0000 0.0000 0.0000C 0.0000 0.0000 0.0900 0.0000 0.0000 0.0000D 0.0000 0.0000 0.0000 0.1500 0.0000 0.0000E 0.0000 0.0000 0.0000 0.0000 0.2000 0.0000F 0.0000 0.0000 0.0000 0.0000 0.0000 0.3000常数 10.00%开始 -5.00%重复 15 点击 Ctrl + A 运行宏步长 0.01投资组合权重 0A 0.0000B 0.0000C 0.0000D 0.0000E 0.0000F 1.0000合计 1投资组合均值 5.50% - =MMULT(TRANSPOSE(B17:B22),I4:I9)投资组合标准差 54.77% - =SQRT(MMULT(MMULT(TRANSPOSE(B17:B22),B4:G9),B17:B22)q -8.22% - =(B25-B11)/B26允许卖空下的两个有效投资组合常数 0% 1%A 0.3947 0.0000 - =MMULT(MINVERSE($B$4:$G$9),$I$4:$I$9-C32)/SUM(MMULT(MINVERSE($B$4:$G$9),$I$4:$I$9-C32)B 0.1974 0.2446C 0.1316 0.2174D 0.1053 0.1957E 0.0987 0.1957F 0.0724 0.1467合计 1 1预期 0.0250 0.0371 - =MMULT(TRANSPOSE(C34:C39),$I$4:$I$9)标准差 0.0993 0.1628 - =SQRT(MMULT(MMULT(TRANSPOSE(C34:C39),$B$4:$G$9),C34:C39)协方差 0.0146 - =MMULT(MMULT(TRANSPOSE(B34:B39),$B$4:$G$9),C34:C39)一个投资组合比例1 0.25比例2 0.75A B C D E F G H12345678910111213141516171819202122232425262728293031323334353637383940414243444546474849预期收益 3.41% - =B47*B42+B48*C42标准差 14.50% - =SQRT(B472*B432+B482*C432+2*B47*B48*B44)模拟运算表标准差 均值 不允许卖空比例 14.50% 3.41%-2 31.98% 6.13%-1.6 28.74% 5.65%-0.6 20.80% 4.44%-0.4 19.27% 4.19%-0.2 17.76% 3.95%0 16.28% 3.71%0.2 14.85% 3.47%0.3 14.15% 3.35%0.6 12.17% 2.98%0.8 10.98% 2.74%1 9.93% 2.50%1.2 9.07% 2.25%1.4 8.46% 2.01%1.6 8.16% 1.77%1.8 8.20% 1.53%2 8.58% 1.28%卖空限制下的数据 0.0831 0.01920.0839 0.01970.0854 0.02050.0888 0.02190.0993 0.02500.1628 0.03710.2175 0.04420.2757 0.04900.3536 0.05250.5477 0.05500.5477 0.05506 0 %5 0 %4 0 %3 0 %2 0 %1 0 %0 %0 %1 %2 %3 %4 %5 %6 %7 %6 0 %5 0 %4 0 %3 0 %2 0 %1 0 %0 %0 %1 %2 %3 %4 %5 %6 %7 %A B C D E F G H505152535455565758596061626364656667686970717273747576777879808182结果平均收益 常数 投资组合标准差 投资组合均值 A B C0.0100 -0.04 0.0831 0.0192 0.5829 0.1749 0.09070.0200 -0.03 0.0839 0.0197 0.5660 0.1769 0.09430.0300 -0.02 0.0854 0.0205 0.5400 0.1800 0.10000.0400 -0.01 0.0888 0.0219 0.4945 0.1854 0.10990.0500 0 0.0993 0.0250 0.3947 0.1974 0.13160.0550 0.01 0.1628 0.0371 0.0000 0.2446 0.21740.02 0.2175 0.0442 0.0000 0.0000 0.21740.03 0.2757 0.0490 0.0000 0.0000 0.00000.04 0.3536 0.0525 0.0000 0.0000 0.00000.05 0.5477 0.0550 0.0000 0.0000 0.00000.06 0.5477 0.0550 0.0000 0.0000 0.00000.07 0.5477 0.0550 0.0000 0.0000 0.00000.08 0.5477 0.0550 0.0000 0.0000 0.00000.09 0.5477 0.0550 0.0000 0.0000 0.00000.1 0.5477 0.0550 0.0000 0.0000 0.0000卖空限制下的最优化- =SQRT(MMULT(MMULT(TRANSPOSE(B17:B22),B4:G9),B17:B22)- =MMULT(MINVERSE($B$4:$G$9),$I$4:$I$9-C32)/SUM(MMULT(MINVERSE($B$4:$G$9),$I$4:$I$9-C32)- =SQRT(MMULT(MMULT(TRANSPOSE(C34:C39),$B$4:$G$9),C34:C39)- =MMULT(MMULT(TRANSPOSE(B34:B39),$B$4:$G$9),C34:C39)I J K L M N O P Q123456789101112131415161718192021222324252627282930313233343536373839404142434445464748496 0 %5 0 %4 0 %3 0 %2 0 %1 0 %0 %0 %1 %2 %3 %4 %5 %6 %7 %6 0 %5 0 %4 0 %3 0 %2 0 %1 0 %0 %0 %1 %2 %3 %4 %5 %6 %7 %I J K L M N O P Q505152535455565758596061626364656667686970717273747576777879808182D E F0.0622 0.0525 0.03690.0660 0.0566 0.04010.0720 0.0630 0.04500.0824 0.0742 0.05360.1053 0.0987 0.07240.1957 0.1956 0.14670.2609 0.2935 0.22830.2667 0.4000 0.33330.0000 0.5000 0.50000.0000 0.0000 1.00000.0000 0.0000 1.00000.0000 0.0000 1.00000.0000 0.0000 1.00000.0000 0.0000 1.00000.0000 0.0000 1.0000R S T12345678910111213141516171819202122232425262728293031323334353637383940414243444546474849