PORTFOLIO OPTIMIZATION ALLOWING SHORT SALESFollows Proposition 1, Chapter 9Variance-covariance matrix Means0.10 0.03 -0.08 0.05 8%0.03 0.20 0.02 0.03 9%-0.08 0.02 0.30 0.20 10%0.05 0.03 0.20 0.90 11%c 3.0% - This is the constantOptimal portfolio without short sale restrictions (Chapter 9, Proposition 1)x1 0.6219 - =MMULT(MINVERSE(B3:E6),G3:G6-C8)/SUM(MMULT(MINVERSE(B3:E6),G3:G6-C8)x2 0.0804x3 0.3542x4 -0.0565Total 1 - =SUM(B11:B14)Portfolio mean 8.62% - =MMULT(TRANSPOSE(B11:B14),G3:G6)Portfolio sigma 19.39% - =SQRT(MMULT(TRANSPOSE(B11:B14),MMULT(B3:E6,B11:B14)q = Theta =(mean-constant)/sigma 28.99% - =(B17-C8)/B18A B C D E F G1234567891011121314151617181920212223242526272829303132333435363738394041424344454647A B C D E F G484950515253PORTFOLIO OPTIMIZATION ALLOWING SHORT SALESFollows Proposition 1, Chapter 9- =MMULT(MINVERSE(B3:E6),G3:G6-C8)/SUM(MMULT(MINVERSE(B3:E6),G3:G6-C8)- =SQRT(MMULT(TRANSPOSE(B11:B14),MMULT(B3:E6,B11:B14)H I1234567891011121314151617181920212223242526272829303132333435363738394041424344454647H I484950515253允许卖空下的投资组合最优化根据第9章定理1方差-协方差矩阵 均值0.10 0.03 -0.08 0.05 8%0.03 0.20 0.02 0.03 9%-0.08 0.02 0.30 0.20 10%0.05 0.03 0.20 0.90 11%c 3.0%- 此为常数没有卖空限制的最优化投资组合 (第9章, 定理1)x1 0.6219 - =MMULT(MINVERSE(B3:E6),G3:G6-C8)/SUM(MMULT(MINVERSE(B3:E6),G3:G6-C8)x2 0.0804x3 0.3542x4 -0.0565合计 1 - =SUM(B11:B14)投资组合均值 8.62% - =MMULT(TRANSPOSE(B11:B14),G3:G6)投资组合标准差 19.39% - =SQRT(MMULT(TRANSPOSE(B11:B14),MMULT(B3:E6,B11:B14)q = Theta =(均值-常数)/标准差 28.99% - =(B17-C8)/B18A B C D E F G1234567891011121314151617181920212223242526272829303132333435363738394041424344454647A B C D E F G484950515253允许卖空下的投资组合最优化根据第9章定理1- =MMULT(MINVERSE(B3:E6),G3:G6-C8)/SUM(MMULT(MINVERSE(B3:E6),G3:G6-C8)- =SQRT(MMULT(TRANSPOSE(B11:B14),MMULT(B3:E6,B11:B14)H I1234567891011121314151617181920212223242526272829303132333435363738394041424344454647H I484950515253PORTFOLIO OPTIMIZATION WITHOUT SHORT SALESSolution with Solver, starting from an arbitrary feasible portfolioVariance-covariance matrix Means0.10 0.03 -0.08 0.05 8%0.03 0.20 0.02 0.03 9%-0.08 0.02 0.30 0.20 10%0.05 0.03 0.20 0.90 11%c 3.0% - This is the constantHere we start with an arbitrary feasible portfolio and use Solverx1 0.5856x2 0.0965x3 0.3179x4 0.0000Total 1 - =SUM(B11:B14)Portfolio mean 8.73% - =MMULT(TRANSPOSE(B11:B14),G3:G6)Portfolio sigma 20.32% - =SQRT(MMULT(TRANSPOSE(B11:B14),MMULT(B3:E6,B11:B14)q = Theta =(mean-constant)/sigma 28.21% - =(B17-C8)/B18A B C D E F G1234567891011121314151617181920212223242526272829303132333435363738394041424344454647A B C D E F G484950515253PORTFOLIO OPTIMIZATION WITHOUT SHORT SALESSolution with Solver, starting from an arbitrary feasible portfolio- =SQRT(MMULT(TRANSPOSE(B11:B14),MMULT(B3:E6,B11:B14)H I1234567891011121314151617181920212223242526272829303132333435363738394041424344454647H I484950515253不允许卖空的最优化投资组合使用规划求解，从任意可行的投资组合开始方差-协方差矩阵 均值0.10 0.03 -0.08 0.05 8%0.03 0.20 0.02 0.03 9%-0.08 0.02 0.30 0.20 10%0.05 0.03 0.20 0.90 11%c 3.0%- 此为常数这里我们使用规划求解，可从任意可行的投资组合开始x1 0.5856x2 0.0965x3 0.3179x4 0.0000合计 1 - =SUM(B11:B14)投资组合均值 8.73% - =MMULT(TRANSPOSE(B11:B14),G3:G6)投资组合标准差 20.32% - =SQRT(MMULT(TRANSPOSE(B11:B14),MMULT(B3:E6,B11:B14)q = Theta =(均值-常数)/标准差 28.21% - =(B17-C8)/B18A B C D E F G12345678910111213141516171819202122232425262728293031323334353637383940414243444546A B C D E F G47484950515253不允许卖空的最优化投资组合使用规划求解，从任意可行的投资组合开始- =SQRT(MMULT(TRANSPOSE(B11:B14),MMULT(B3:E6,B11:B14)H I12345678910111213141516171819202122232425262728293031323334353637383940414243444546H I47484950515253PORTFOLIO OPTIMIZATION WITHOUT SHORT SALESSolution with Solver, starting from an arbitrary feasible portfolioVariance-covariance matrix Means0.10 0.03 -0.08 0.05 8%0.03 0.20 0.02 0.03 9%-0.08 0.02 0.30 0.20 10%0.05 0.03 0.20 0.90 11%c 8.5% - This is the constantHere we start with an arbitrary feasible portfolio and use Solverx1 0.0000x2 0.2515x3 0.4885x4 0.2601Total 1 - =SUM(B11:B14)Portfolio mean 10.01% - =MMULT(TRANSPOSE(B11:B14),G3:G6)Portfolio sigma 45.25% - =SQRT(MMULT(TRANSPOSE(B11:B14),MMULT(B3:E6,B11:B14)q = Theta =(mean-constant)/sigma 3.33% - =(B17-C8)/B18A B C D E F G1234567891011121314151617181920212223242526272829303132333435363738394041424344454647A B C D E F G484950515253PORTFOLIO OPTIMIZATION WITHOUT SHORT SALESSolution with Solver, starting from an arbitrary feasible portfolio- =SQRT(MMULT(TRANSPOSE(B11:B14),MMULT(B3:E6,B11:B14)H I1234567891011121314151617181920212223242526272829303132333435363738394041424344454647H I484950515253不允许卖空的最优化投资组合使用规划求解，从任意可行的投资组合开始方差-协方差矩阵 均值0.10 0.03 -0.08 0.05 8%0.03 0.20 0.02 0.03 9%-0.08 0.02 0.30 0.20 10%0.05 0.03 0.20 0.90 11%c 8.5%- 此为常数这里我们使用规划求解，可从任意可行的投资组合开始x1 0.0000x2 0.2515x3 0.4885x4 0.2601合计 1 - =SUM(B11:B14)投资组合均值 10.01% - =MMULT(TRANSPOSE(B11:B14),G3:G6)投资组合标准差 45.25% - =SQRT(MMULT(TRANSPOSE(B11:B14),MMULT(B3:E6,B11:B14)q = Theta =(均值-常数)/标准差 3.33% - =(B17-C8)/B18A B C D E F G12345678910111213141516171819202122232425262728293031323334353637383940414243444546A B C D E F G47484950515253不允许卖空的最优化投资组合使用规划求解，从任意可行的投资组合开始- =SQRT(MMULT(TRANSPOSE(B11:B14),MMULT(B3:E6,B11:B14)H I12345678910111213141516171819202122232425262728293031323334353637383940414243444546H I47484950515253PORTFOLIO OPTIMIZATION WITHOUT SHORT SALESSolution with Solver, starting from an arbitrary feasible portfolioVariance-covariance matrix Means0.10 0.03 -0.08 0.05 8%0.03 0.20 0.02 0.03 9%-0.08 0.02 0.30 0.20 10%0.05 0.03 0.20 0.90 11%c 8.0% - This is the constantHere we start with an arbitrary feasible portfolio and use Solverx1 0.2004x2 0.2587x3 0.4219x4 0.1190Total 1 - =SUM(B11:B14)Portfolio mean 9.46% - =MMULT(TRANSPOSE(B11:B14),G3:G6)Portfolio sigma 31.91% - =SQRT(MMULT(TRANSPOSE(B11:B14),MMULT(B3:E6,B11:B14)q = Theta =(mean-constant)/sigma 4.57% - =(B17-C8)/B18A B C D E F G1234567891011121314151617181920212223242526272829303132333435363738394041424344454647A B C D E F G484950515253PORTFOLIO OPTIMIZATION WITHOUT SHORT SALESSolution with Solver, starting from an arbitrary feasible portfolio- =SQRT(MMULT(TRANSPOSE(B11:B14),MMULT(B3:E6,B11:B14)H I1234567891011121314151617181920212223242526272829303132333435363738394041424344454647H I484950515253不允许卖空的最优化投资组合使用规划求解，从任意可行的投资组合开始方差-协方差矩阵 均值0.10 0.03 -0.08 0.05 8%0.03 0.20 0.02 0.03 9%-0.08 0.02 0.30 0.20 10%0.05 0.03 0.20 0.90 11%c 8.0%- 此为常数这里我们使用规划求解，可从任意可行的投资组合开始x1 0.2004x2 0.2587x3 0.4219x4 0.1190合计 1 - =SUM(B11:B14)投资组合均值 9.46% - =MMULT(TRANSPOSE(B11:B14),G3:G6)投资组合标准差 31.91% - =SQRT(MMULT(TRANSPOSE(B11:B14),MMULT(B3:E6,B11:B14)q = Theta =(均值-常数)/标准差 4.57% - =(B17-C8)/B18A B C D E F G12345678910111213141516171819202122232425262728293031323334353637383940414243444546A B C D E F G47484950515253不允许卖空的最优化投资组合使用规划求解，从任意可行的投资组合开始- =SQRT(MMULT(TRANSPOSE(B11:B14),MMULT(B3:E6,B11:B14)H I12345678910111213141516171819202122232425262728293031323334353637383940414243444546H I47484950515253PORTFOLIO OPTIMIZATION WITHOUT SHORT SALESSolution with Solver, starting from an arbitrary feasible portfolioVariance-covariance matrix Means0.10 0.03 -0.08 0.05 8%0.03 0.20 0.02 0.03 9%-0.08 0.02 0.30 0.20 10%0.05 0.03 0.20 0.90 11%c 11.0% - This is the constantHere we start with an arbitrary feasible portfolio and use Solverx1 0.0000x2 0.0000x3 0.0000x4 1.0000Total 1 - =SUM(B11:B14)Portfolio mean 11.00% - =MMULT(TRANSPOSE(B11:B14),G3:G6)Portfolio sigma 94.87% - =SQRT(MMULT(TRANSPOSE(B11:B14),MMULT(B3:E6,B11:B14)q = Theta =(mean-constant)/sigma 0.00% - =(B17-C8)/B18A B C D E F G1234567891011121314151617181920212223242526272829303132333435363738394041424344454647A B C D E F G484950515253PORTFOLIO OPTIMIZATION WITHOUT SHORT SALESSolution with Solver, starting from an arbitrary feasible portfolio- =SQRT(MMULT(TRANSPOSE(B11:B14),MMULT(B3:E6,B11:B14)H I1234567891011121314151617181920212223242526272829303132333435363738394041424344454647H I484950515253不允许卖空的最优化投资组合使用规划求解，从任意可行的投资组合开始方差-协方差矩阵 均值0.10 0.03 -0.08 0.05 8%0.03 0.20 0.02 0.03 9%-0.08 0.02 0.30 0.20 10%0.05 0.03 0.20 0.90 11%c 11.0%- 此为常数这里我们使用规划求解，可从任意可行的投资组合开始x1 0.0000x2 0.0000x3 0.0000x4 1.0000合计 1 - =SUM(B11:B14)投资组合均值 11.00% - =MMULT(TRANSPOSE(B11:B14),G3:G6)投资组合标准差 94.87% - =SQRT(MMULT(TRANSPOSE(B11:B14),MMULT(B3:E6,B11:B14)q = Theta =(均值-常数)/标准差 0.00% - =(B17-C8)/B18A B C D E F G12345678910111213141516171819202122232425262728293031323334353637383940414243444546A B C D E F G47484950515253不允许卖空的最优化投资组合使用规划求解，从任意可行的投资组合开始- =SQRT(MMULT(TRANSPOSE(B11:B14),MMULT(B3:E6,B11:B14)H I12345678910111213141516171819202122232425262728293031323334353637383940414243444546H I47484950515253Note: To get the two data series (with and without short sales) to chart on the sameset of axes, we copy all the sigmas in one column and then copy the means intwo adjacent columns. The graph is produced with an XY graph format.Sigma No shortsalesShortsalesallowed20.24% 8.70%20.25% 8.70%20.25% 8.70%20.25% 8.71%20.25% 8.71%20.26% 8.71%20.26% 8.71%20.27% 8.71%20.27% 8.71%20.28% 8.72%20.29% 8.72%20.30% 8.72%20.31% 8.73%20.32% 8.73%20.34% 8.74%20.37% 8.74%20.41% 8.75%20.46% 8.76%20.54% 8.78%20.67% 8.80%20.90% 8.82%21.36% 8.87%23.27% 9.01%31.91% 9.46%45.25% 10.01%60.76% 10.44%74.30% 10.70%94.87% 11.00%94.87% 11.00%94.87% 11.00%94.87% 11.00%94.87% 11.00%94.87% 11.00%94.87% 11.00%94.87% 11.00%94.87% 11.00%94.87% 11.00%94.87% 11.00%94.87% 11.00%94.87% 11.00%28.17% 7.78%26.53% 7.86%25.00% 7.95%23.60% 8.03%22.34% 8.12%21.26% 8.20%20.39% 8.28%19.74% 8.37%C o m p a r in g T w o E ffic ie n t F r o n tie r sF o r lo w s ig m a s th e tw o fr o n tie r s c o in c id eF o r h ig h e r s ig n a s , n o r e s tr ic tio n s o n s h o r t s a le s g iv e s h ig h e r r e tu r n s9 8 %8 8 %7 8 %6 8 %5 8 %4 8 %3 8 %2 8 %1 8 %6 %7 %8 %9 %1 0 %1 1 %1 2 %1 3 %S ig m a ( % )MeanReturn(%)N o s h o rt s a le sS h o rt s a le s a llo w e dE ffic ie n t F r o n tie r9 8 %8 8 %7 8 %6 8 %5 8 %4 8 %3 8 %2 8 %1 8 %8 %9 %1 0 %1 1 %S ig m a (% )Mean(%)19.35% 8.45%19.23% 8.54%19.39% 8.62%19.81% 8.70%20.48% 8.79%21.38% 8.87%22.48% 8.96%23.76% 9.04%25.18% 9.12%26.72% 9.21%28.37% 9.29%30.10% 9.38%31.91% 9.46%33.77% 9.54%35.69% 9.63%37.65% 9.71%39.65% 9.80%41.67% 9.88%43.73% 9.96%45.81% 10.05%47.91% 10.13%50.02% 10.22%52.15% 10.30%54.30% 10.38%56.46% 10.47%58.63% 10.55%60.81% 10.64%63.00% 10.72%65.19% 10.80%67.39% 10.89%69.60% 10.97%71.82% 11.06%74.04% 11.14%76.27% 11.22%78.49% 11.31%80.73% 11.39%82.97% 11.48%85.21% 11.56%87.45% 11.64%89.70% 11.73%91.95% 11.81%C o m p a r in g T w o E ffic ie n t F r o n tie r sF o r lo w s ig m a s th e tw o fr o n tie r s c o in c id eF o r h ig h e r s ig n a s , n o r e s tr ic tio n s o n s h o r t s a le s g iv e s h ig h e r r e tu r n s9 8 %8 8 %7 8 %6 8 %5 8 %4 8 %3 8 %2 8 %1 8 %6 %7 %8 %9 %1 0 %1 1 %1 2 %1 3 %S ig m a ( % )MeanReturn(%)N o s h o rt s a le sS h o rt s a le s a llo w e dE ffic ie n t F r o n tie r9 8 %8 8 %7 8 %6 8 %5 8 %4 8 %3 8 %2 8 %1 8 %8 %9 %1 0 %1 1 %S ig m a (% )Mean(%)注意: 为了使得两个系列的数据 (允许卖空和不允许卖空) 在同一个坐标系中显示，我们将所有标准差复制到同一列中，然后把均值复制到相邻的两列中。该图使用的是x-y图，即散点图格式。标准差 不允许卖空 允许卖空20.24% 8.70%20.25% 8.70%20.25% 8.70%20.25% 8.71%20.25% 8.71%20.26% 8.71%20.26% 8.71%20.27% 8.71%20.27% 8.71%20.28% 8.72%20.29% 8.72%20.30% 8.72%20.31% 8.73%20.32% 8.73%20.34% 8.74%20.37% 8.74%20.41% 8.75%20.46% 8.76%20.54% 8.78%20.67% 8.80%20.90% 8.82%21.36% 8.87%23.27% 9.01%31.91% 9.46%45.25% 10.01%60.76% 10.44%74.30% 10.70%94.87% 11.00%94.87% 11.00%94.87% 11.00%94.87% 11.00%94.87% 11.00%94.87% 11.00%94.87% 11.00%94.87% 11.00%94.87% 11.00%94.87% 11.00%94.87% 11.00%94.87% 11.00%94.87% 11.00%28.17% 7.78%26.53% 7.86%25.00% 7.95%23.60% 8.03%22.34% 8.12%21.26% 8.20%20.39% 8.28%19.74% 8.37%19.35% 8.45%19.23% 8.54%C o m p a r in g T w o E ffic ie n t F r o n tie r sF o r lo w s ig m a s th e tw o fr o n tie r s c o in c id eF o r h ig h e r s ig n a s , n o r e s tr ic tio n s o n s h o r t s a le s g iv e s h ig h e r r e tu r n s9 8 %8 8 %7 8 %6 8 %5 8 %4 8 %3 8 %2 8 %1 8 %6 %7 %8 %9 %1 0 %1 1 %1 2 %1 3 %S ig m a ( % )MeanReturn(%)N o s h o rt s a le sS h o rt s a le s a llo w e dE f f ic ie n t F r o n t ie r9 8 %8 8 %7 8 %6 8 %5 8 %4 8 %3 8 %2 8 %1 8 %8 %9 %1 0 %1 1 %S ig m a ( % )Mean(%)比 较 两 条 有 效 前 沿当 标 准 差 较 小 时 ， 两 条 前 沿 重 叠当 标 准 差 较 大 时 ， 允 许 卖 空 将 取 得 更 高 收 益9 8 %8 8 %7 8 %6 8 %5 8 %4 8 %3 8 %2 8 %1 8 %6 %7 %8 %9 %1 0 %1 1 %1 2 %1 3 %标 准 差 ( % )平均收益(%)不 允 许 卖 空允 许 卖 空有 效 前 沿9 8 %8 8 %7 8 %6 8 %5 8 %4 8 %3 8 %2 8 %1 8 %8 %9 %1 0 %1 1 %标 准 差 ( % )均值(%)19.39% 8.62%19.81% 8.70%20.48% 8.79%21.38% 8.87%22.48% 8.96%23.76% 9.04%25.18% 9.12%26.72% 9.21%28.37% 9.29%30.10% 9.38%31.91% 9.46%33.77% 9.54%35.69% 9.63%37.65% 9.71%39.65% 9.80%41.67% 9.88%43.73% 9.96%45.81% 10.05%47.91% 10.13%50.02% 10.22%52.15% 10.30%54.30% 10.38%56.46% 10.47%58.63% 10.55%60.81% 10.64%63.00% 10.72%65.19% 10.80%67.39% 10.89%69.60% 10.97%71.82% 11.06%74.04% 11.14%76.27% 11.22%78.49% 11.31%80.73% 11.39%82.97% 11.48%85.21% 11.56%87.45% 11.64%89.70% 11.73%91.95% 11.81%C o m p a r in g T w o E ffic ie n t F r o n tie r sF o r lo w s ig m a s th e tw o fr o n tie r s c o in c id eF o r h ig h e r s ig n a s , n o r e s tr ic tio n s o n s h o r t s a le s g iv e s h ig h e r r e tu r n s9 8 %8 8 %7 8 %6 8 %5 8 %4 8 %3 8 %2 8 %1 8 %6 %7 %8 %9 %1 0 %1 1 %1 2 %1 3 %S ig m a ( % )MeanReturn(%)N o s h o rt s a le sS h o rt s a le s a llo w e dE f f ic ie n t F r o n t ie r9 8 %8 8 %7 8 %6 8 %5 8 %4 8 %3 8 %2 8 %1 8 %8 %9 %1 0 %1 1 %S ig m a ( % )Mean(%)比 较 两 条 有 效 前 沿当 标 准 差 较 小 时 ， 两 条 前 沿 重 叠当 标 准 差 较 大 时 ， 允 许 卖 空 将 取 得 更 高 收 益9 8 %8 8 %7 8 %6 8 %5 8 %4 8 %3 8 %2 8 %1 8 %6 %7 %8 %9 %1 0 %1 1 %1 2 %1 3 %标 准 差 ( % )平均收益(%)不 允 许 卖 空允 许 卖 空有 效 前 沿9 8 %8 8 %7 8 %6 8 %5 8 %4 8 %3 8 %2 8 %1 8 %8 %9 %1 0 %1 1 %标 准 差 ( % )均值(%)PORTFOLIO OPTIMIZATION ALLOWING SHORT SALESFollows Proposition 1, Chapter 9Variance-covariance matrix Means0.10 0.03 -0.08 0.05 8%0.03 0.20 0.02 0.03 9%-0.08 0.02 0.30 0.20 10%0.05 0.03 0.20 0.90 11%c 8.0% 3.00%Optimal portfolio without short sale restrictions (Chapter 9, Proposition 1)x1 0.2004 0.6219 - =MMULT(MINVERSE(B3:E6),G3:G6-C8)/SUM(MMULT(MINVERSE(B3:E6),G3:G6-C8)x2 0.2587 0.0804x3 0.4219 0.3542x4 0.1190 -0.0565Total 1 1Portfolio mean 9.46% 8.62% - =MMULT(TRANSPOSE(C11:C14),$G$3:$G$6)Portfolio sigma 31.91% 19.39% - =SQRT(MMULT(TRANSPOSE(C11:C14),MMULT($B$3:$E$6,C11:C14)Covariance betweenportfolios 4.32% - =MMULT(MMULT(TRANSPOSE(B11:B14),B3:E6),C11:C14)Proportion of first-1 28.17% 7.78%-0.9 26.53% 7.86%-0.8 25.00% 7.95%-0.7 23.60% 8.03%-0.6 22.34% 8.12%-0.5 21.26% 8.20%-0.4 20.39% 8.28%-0.3