CALCULATING THE EFFICIENT FRONTIERVariance-covariance matrix ExpectedreturnsE(r)ExpectedminusconstantE(r) - c0.40 0.03 0.02 0.00 0.06 0.020.03 0.20 0.00 -0.06 0.05 0.010.02 0.00 0.30 0.03 0.07 0.030.00 -0.06 0.03 0.10 0.08 0.04Constant 0.04Computing an envelope portfolio with constant = 0zEnvelopeportfoliox0.1019 - =MMULT(MINVERSE(A3:D6),F3:F6) 0.0540 - =A12/SUM($A$12:$A$15)0.5657 0.29980.1141 0.06051.1052 0.5857Sum 1.0000 - =SUM(F12:F15)Computing an envelope portfolio with constant = 0.04zEnvelopeportfolioy0.0330 - =MMULT(MINVERSE(A3:D6),G3:G6) 0.0423 - =A20/SUM($A$20:$A$23)0.1959 0.25140.0468 0.06010.5035 0.6462Sum 1.0000 - =SUM(F20:F23)E(x) 0.0693 E(x) 0.0710 - =MMULT(TRANSPOSE(F20:F23),F3:F6)Var(x) 0.0367 Var(y) 0.0398 - =MMULT(MMULT(TRANSPOSE(F20:F23),A3:D6),F20:F23)Sigma(x) 0.1917 Sigma(y) 0.1995 - =SQRT(F27)Cov(x,y) 0.0376 - =MMULT(MMULT(TRANSPOSE(F12:F15),A3:D6),F20:F23)Corr(x,y) 0.9842 - =C30/(B28*F28)A single portfolio calculationProportion of x 0.3E(rp) 7.05% - =B35*B26+(1-B35)*F26sp 19.65% - =SQRT(B352*B27+(1-B35)2*F27+2*B35*(1-B35)*C30)Data table: we vary the proportion of x to produce a graph of the frontierProportion of x Sigma Return0.1965 0.0705 - Data table header refers to cells B36 and B35A B C D E F G123456789101112131415161718192021222324252627282930313233343536373839404142-1.400 0.2199 0.0734-1.200 0.2164 0.0730-1.000 0.2131 0.0727-0.800 0.2100 0.0724-0.600 0.2070 0.0720-0.400 0.2043 0.0717-0.200 0.2018 0.07130.000 0.1995 0.07100.100 0.1984 0.07080.200 0.1974 0.07070.300 0.1965 0.07050.400 0.1956 0.07030.500 0.1948 0.07020.600 0.1941 0.07000.700 0.1934 0.06980.800 0.1927 0.06970.900 0.1922 0.06951.000 0.1917 0.06931.200 0.1909 0.06901.400 0.1903 0.06861.600 0.1901 0.06831.800 0.1901 0.06802.000 0.1903 0.06762.200 0.1908 0.06732.400 0.1916 0.06702.600 0.1927 0.06662.800 0.1940 0.06633.000 0.1956 0.0659jumps in x for table 0.20 .2 2 5 00 .2 2 0 00 .2 1 5 00 .2 1 0 00 .2 0 5 00 .2 0 0 00 .1 9 5 00 .1 9 0 00 .1 8 5 00 .1 8 0 00 .1 7 5 00 .0 6 2 00 .0 6 4 00 .0 6 6 00 .0 6 8 00 .0 7 0 00 .0 7 2 00 .0 7 4 0P o rtfo lio xP o rtfo lio yP ro p o rtio n o f x : 2 .8P ro p o rtio n o f y : -1 .8A B C D E F G4344454647484950515253545556575859606162636465666768697071727374757677787980818283- =F3-$B$8- =F4-$B$8- =F5-$B$8- =F6-$B$8CALCULATING THE EFFICIENT FRONTIER- =A12/SUM($A$12:$A$15)- =SUM(F12:F15)- =A20/SUM($A$20:$A$23)- =SUM(F20:F23)- =MMULT(TRANSPOSE(F20:F23),F3:F6)- =MMULT(MMULT(TRANSPOSE(F20:F23),A3:D6),F20:F23)- =SQRT(F27)- =MMULT(MMULT(TRANSPOSE(F12:F15),A3:D6),F20:F23)- =SQRT(B352*B27+(1-B35)2*F27+2*B35*(1-B35)*C30)- Data table header refers to cells B36 and B35H123456789101112131415161718192021222324252627282930313233343536373839404142P ro p o rtio n o f x : -1 .0P ro p o rtio n o f y : 2P o rtfo lio w : 5 0 % in x , 5 0 % in y0 .2 2 5 00 .2 2 0 00 .2 1 5 00 .2 1 0 00 .2 0 5 00 .2 0 0 00 .1 9 5 00 .1 9 0 00 .1 8 5 00 .1 8 0 00 .1 7 5 00 .0 6 2 00 .0 6 4 00 .0 6 6 00 .0 6 8 00 .0 7 0 00 .0 7 2 00 .0 7 4 0P ro p o rtio n o f x : 2 .8P ro p o rtio n o f y : -1 .8H4344454647484950515253545556575859606162636465666768697071727374757677787980818283计算 有效前沿方差-协方差矩阵 期望收益E(r)期望 减常数E(r) - c0.40 0.03 0.02 0.00 0.06 0.020.03 0.20 0.00 -0.06 0.05 0.010.02 0.00 0.30 0.03 0.07 0.030.00 -0.06 0.03 0.10 0.08 0.04常数 0.04计算包络线常数 = 0的投资组合z包络线 投资组合x0.1019 - =MMULT(MINVERSE(A3:D6),F3:F6) 0.0540 - =A12/SUM($A$12:$A$15)0.5657 0.29980.1141 0.06051.1052 0.5857和 1.0000 - =SUM(F12:F15)计算包络线投资组合 ，常数 = 0.04z包络线 投资组合y0.0330 - =MMULT(MINVERSE(A3:D6),G3:G6) 0.0423 - =A20/SUM($A$20:$A$23)0.1959 0.25140.0468 0.06010.5035 0.6462和 1.0000 - =SUM(F20:F23)均值（x） 0.0693 均值（y） 0.0710 - =MMULT(TRANSPOSE(F20:F23),F3:F6)方差（x） 0.0367 方差(y) 0.0398 - =MMULT(MMULT(TRANSPOSE(F20:F23),A3:D6),F20:F23)标准差(x) 0.1917 标准差(y) 0.1995 - =SQRT(F27)协方差(x,y) 0.0376 - =MMULT(MMULT(TRANSPOSE(F12:F15),A3:D6),F20:F23)相关系数(x,y) 0.9842 - =C30/(B28*F28)一个投资组合的计算投资组合x的比例 0.3E(rp) 7.05% - =B35*B26+(1-B35)*F26sp 19.65% - =SQRT(B352*B27+(1-B35)2*F27+2*B35*(1-B35)*C30)模拟运算表: 我们通过变动x的比例去产生一条有效前沿投资组合x的比例 标准差 收益0.1965 0.0705- 模拟运算表表头指向单元格B36和B35A B C D E F G123456789101112131415161718192021222324252627282930313233343536373839404142-1.400 0.2199 0.0734-1.200 0.2164 0.0730-1.000 0.2131 0.0727-0.800 0.2100 0.0724-0.600 0.2070 0.0720-0.400 0.2043 0.0717-0.200 0.2018 0.07130.000 0.1995 0.07100.100 0.1984 0.07080.200 0.1974 0.07070.300 0.1965 0.07050.400 0.1956 0.07030.500 0.1948 0.07020.600 0.1941 0.07000.700 0.1934 0.06980.800 0.1927 0.06970.900 0.1922 0.06951.000 0.1917 0.06931.200 0.1909 0.06901.400 0.1903 0.06861.600 0.1901 0.06831.800 0.1901 0.06802.000 0.1903 0.06762.200 0.1908 0.06732.400 0.1916 0.06702.600 0.1927 0.06662.800 0.1940 0.06633.000 0.1956 0.0659jumps in x for table 0.20 .2 2 5 00 .2 2 0 00 .2 1 5 00 .2 1 0 00 .2 0 5 00 .2 0 0 00 .1 9 5 00 .1 9 0 00 .1 8 5 00 .1 8 0 00 .1 7 5 00 .0 6 2 00 .0 6 4 00 .0 6 6 00 .0 6 8 00 .0 7 0 00 .0 7 2 00 .0 7 4 0P o rtfo lio xP o rtfo lio yP ro p o rtio n o f x : 2 .8P ro p o rtio n o f y : -1 .80 .2 2 5 00 .2 2 0 00 .2 1 5 00 .2 1 0 00 .2 0 5 00 .2 0 0 00 .1 9 5 00 .1 9 0 00 .1 8 5 00 .1 8 0 00 .1 7 5 00 .0 6 2 00 .0 6 4 00 .0 6 6 00 .0 6 8 00 .0 7 0 00 .0 7 2 00 .0 7 4 0投 资 组 合投 资 组 合x的 比 例 : 2 .8y的 比 例 : -1 .8A B C D E F G4344454647484950515253545556575859606162636465666768697071727374757677787980818283- =F3-$B$8- =F4-$B$8- =F5-$B$8- =F6-$B$8计算 有效前沿- =A12/SUM($A$12:$A$15)- =SUM(F12:F15)- =A20/SUM($A$20:$A$23)- =SUM(F20:F23)- =MMULT(TRANSPOSE(F20:F23),F3:F6)- =MMULT(MMULT(TRANSPOSE(F20:F23),A3:D6),F20:F23)- =SQRT(F27)- =MMULT(MMULT(TRANSPOSE(F12:F15),A3:D6),F20:F23)- =SQRT(B352*B27+(1-B35)2*F27+2*B35*(1-B35)*C30)H123456789101112131415161718192021222324252627282930313233343536373839404142P ro p o rtio n o f x : -1 .0P ro p o rtio n o f y : 2P o rtfo lio w : 5 0 % in x , 5 0 % in yx的 比 例 ： -1 .0y的 比 例 ： : 2投 资 组 合 w :5 0 % 于 x , 5 0 %于 y0 .2 2 5 00 .2 2 0 00 .2 1 5 00 .2 1 0 00 .2 0 5 00 .2 0 0 00 .1 9 5 00 .1 9 0 00 .1 8 5 00 .1 8 0 00 .1 7 5 00 .0 6 2 00 .0 6 4 00 .0 6 6 00 .0 6 8 00 .0 7 0 00 .0 7 2 00 .0 7 4 0P ro p o rtio n o f x : 2 .8P ro p o rtio n o f y : -1 .80 .2 2 5 00 .2 2 0 00 .2 1 5 00 .2 1 0 00 .2 0 5 00 .2 0 0 00 .1 9 5 00 .1 9 0 00 .1 8 5 00 .1 8 0 00 .1 7 5 00 .0 6 2 00 .0 6 4 00 .0 6 6 00 .0 6 8 00 .0 7 0 00 .0 7 2 00 .0 7 4 0x的 比 例 : 2 .8y的 比 例 : -1 .8H4344454647484950515253545556575859606162636465666768697071727374757677787980818283CALCULATING THE ENVELOPEAll constants c lead to the same envelopeVariance-covariance matrixExpectedreturns0.40 0.03 0.02 0.00 0.060.03 0.20 0.00 -0.06 0.050.02 0.00 0.30 0.03 0.070.00 -0.06 0.03 0.10 0.08Computing an envelope portfolio with constant = -0.03zEnvelopeportfoliox0.0502 - =MMULT(MINVERSE(A3:D6),F3:F6-0.03) 0.04750.2883 0.27300.0636 0.06030.6539 0.6192Sum 1.0000Computing an envelope portfolio with constant = 0.08zEnvelopeportfolioy-0.0359 - =MMULT(MINVERSE(A3:D6),F3:F6-0.08) 0.1093-0.1740 0.5293-0.0205 0.0625-0.0982 0.2989Sum 1.0000Additional calculation: Fix another constant, and show that the resultingportfolio is a combination of the two above portfoliosConstant 0.01Computing an envelope portfolio with constant = 0.01zEnvelopeportfoliow0.0846 #NAME? 0.05260.4732 0.29400.0973 0.06040.9548 0.5930Sum 1.0000Proportions of x and y which determine wProportion of x 0.9183 - =(F29-F18)/(F10-F18)Proportion of y 0.0817 - =1-B37A B C D E F123456789101112131415161718192021222324252627282930313233343536373839Check: Multiply the above proportions times portfolios x and y to get w0.0526 - =$B$37*F10+$B$38*F180.2940 - =$B$37*F11+$B$38*F190.06040.5930A B C D E F4041424344- =A10/SUM($A$10:$A$13)- =SUM(F10:F13)- =A18/SUM($A$18:$A$21)- =SUM(F18:F21)- =A29:A32/SUM(A29:A32)- =SUM(F29:F32)CALCULATING THE ENVELOPEAll constants c lead to the same envelopeAdditional calculation: Fix another constant, and show that the resultingportfolio is a combination of the two above portfoliosG123456789101112131415161718192021222324252627282930313233343536373839Check: Multiply the above proportions times portfolios x and y to get wG4041424344计算 包络线所有常数c 导致相同的包络线方差-协方差矩阵 期望收益0.40 0.03 0.02 0.00 0.060.03 0.20 0.00 -0.06 0.050.02 0.00 0.30 0.03 0.070.00 -0.06 0.03 0.10 0.08计算包络线投资组合 ，常数 = -0.03z包络线投资组合 x0.0502 - =MMULT(MINVERSE(A3:D6),F3:F6-0.03) 0.04750.2883 0.27300.0636 0.06030.6539 0.6192和 1.0000计算包络线投资组合 ，常数 = 0.08z包络线投资组合 y-0.0359 - =MMULT(MINVERSE(A3:D6),F3:F6-0.08) 0.1093-0.1740 0.5293-0.0205 0.0625-0.0982 0.2989和 1.0000进一步计算: 固定另一个常数,可以看到相应的投资组合为以上两个投资组合的结合常数 0.01计算包络线投资组合 ，常数 = 0.01z包络线投资组合w0.0846 - =MMULT(MINVERSE(A3:D6),F3:F6-B25) 0.05260.4732 0.29400.0973 0.06040.9548 0.5930和 1.0000决定w的投资组合x和y投资组合x 0.9183 - =(F29-F18)/(F10-F18)投资组合y 0.0817 - =1-B37检验：将以上的比例对应乘以投资组合x和y加权得到wA B C D E F123456789101112131415161718192021222324252627282930313233343536373839400.0526 - =$B$37*F10+$B$38*F180.2940 - =$B$37*F11+$B$38*F190.06040.5930A B C D E F41424344- =A10/SUM($A$10:$A$13)- =SUM(F10:F13)- =A18/SUM($A$18:$A$21)- =SUM(F18:F21)- =A29:A32/SUM(A29:A32)- =SUM(F29:F32)计算 包络线所有常数c 导致相同的包络线进一步计算: 固定另一个常数,可以看到相应的投资组合为以上两个投资组合的结合G12345678910111213141516171819202122232425262728293031323334353637383940SOME cs CAN LEAD TO INEFFICIENT PORTFOLIOSThe portfolio x determined by the constant c=0.11 is inefficientVariance-covariance matrixExpectedreturnsE(r)0.40 0.03 0.02 0.00 0.060.03 0.20 0.00 -0.06 0.050.02 0.00 0.30 0.03 0.070.00 -0.06 0.03 0.10 0.08Constant 0.11Computing an envelope portfolio with constant = 0.11zEnvelopeportfoliox-0.0876 - =MMULT(MINVERSE(A3:D6),F3:F6-B8) 0.0755-0.4514 0.3893-0.0710 0.0613-0.5495 0.4739Sum 1.0000E(rx) 0.0662sx 0.19440 .2 2 50 .2 20 .2 1 50 .2 10 .2 0 50 .20 .1 9 50 .1 90 .1 8 50 .1 80 .1 7 50 .0 6 20 .0 6 40 .0 6 60 .0 6 80 .0 70 .0 7 20 .0 7 4A B C D E F1234567891011121314151617181920212223242526272829303132333435363738394041424344A B C D E F45464748- =A12:A15/SUM(A12:A15)- =SUM(F12:F15)SOME cs CAN LEAD TO INEFFICIENT PORTFOLIOSThe portfolio x determined by the constant c=0.11 is inefficient0 .2 2 50 .2 20 .2 1 50 .2 10 .2 0 50 .20 .1 9 50 .1 90 .1 8 50 .1 80 .1 7 50 .0 6 20 .0 6 40 .0 6 60 .0 6 80 .0 70 .0 7 20 .0 7 4G H I J K1234567891011121314151617181920212223242526272829303132333435363738394041424344G H I J K45464748Sigma Return0.219938 0.0733770.216432 0.0730390.213108 0.0727010.209973 0.0723630.207038 0.0720250.204309 0.0716870.201796 0.0713490.199507 0.0710110.198449 0.0708420.19745 0.0706730.19651 0.0705040.195631 0.0703350.194814 0.0701660.194058 0.0699970.193366 0.0698280.192737 0.0696590.192172 0.069490.191672 0.0693210.190869 0.0689830.19033 0.0686450.190058 0.0683070.190053 0.0679690.190317 0.0676310.190847 0.0672930.191642 0.066955L M12345678910111213141516171819202122232425262728293031323334353637383940414243440.192698 0.0666170.19401 0.0662790.195575 0.0659410.1944 0.0662L M45464748一些c可以导致有效投资组合常数 c=0.11的投资组合 x 是非有效的方差-协方差矩阵期望收益E(r)0.40 0.03 0.02 0.00 0.060.03 0.20 0.00 -0.06 0.050.02 0.00 0.30 0.03 0.070.00 -0.06 0.03 0.10 0.08常数 0.11计算包络线投资组合 ，常数 = 0.11z包络线投资组合x-0.0876 - =MMULT(MINVERSE(A3:D6),F3:F6-B8) 0.0755-0.4514 0.3893-0.0710 0.0613-0.5495 0.4739和 1.0000E(rx) 0.0662sx 0.19440 .2 2 50 .2 20 .2 1 50 .2 10 .2 0 50 .20 .1 9 50 .1 90 .1 8 50 .1 80 .1 7 50 .0 6 20 .0 6 40 .0 6 60 .0 6 80 .0 70 .0 7 20 .0 7 40 .2 2 50 .2 20 .2 1 50 .2 10 .2 0 50 .20 .1 9 50 .1 90 .1 8 50 .1 80 .1 7 50 .0 6 20 .0 6 40 .0 6 60 .0 6 80 .0 70 .0 7 20 .0 7 4A B C D E F123456789101112131415161718192021222324252627282930313233343536373839404142434445A B C D E F464748- =A12:A15/SUM(A12:A15)- =SUM(F12:F15)一些c可以导致有效投资组合常数 c=0.11的投资组合 x 是非有效的0 .2 2 50 .2 20 .2 1 50 .2 10 .2 0 50 .20 .1 9 50 .1 90 .1 8 50 .1 80 .1 7 50 .0 6 20 .0 6 40 .0 6 60 .0 6 80 .0 70 .0 7 20 .0 7 40 .2 2 50 .2 20 .2 1 50 .2 10 .2 0 50 .20 .1 9 50 .1 90 .1 8 50 .1 80 .1 7 50 .0 6 20 .0 6 40 .0 6 60 .0 6 80 .0 70 .0 7 20 .0 7 4G H I J K123456789101112131415161718192021222324252627282930313233343536373839404142434445G H I J K464748标准差 收益0.219938 0.0733770.216432 0.0730390.213108 0.0727010.209973 0.0723630.207038 0.0720250.204309 0.0716870.201796 0.0713490.199507 0.0710110.198449 0.0708420.19745 0.0706730.19651 0.0705040.195631 0.0703350.194814 0.0701660.194058 0.0699970.193366 0.0698280.192737 0.0696590.192172 0.069490.191672 0.0693210.190869 0.0689830.19033 0.0686450.190058 0.0683070.190053 0.0679690.190317 0.0676310.190847 0.0672930.191642 0.0669550.192698 0.066617L M1234567891011121314151617181920212223242526272829303132333435363738394041424344450.19401 0.0662790.195575 0.0659410.1944 0.0662L M464748IF c = rf AND THE OPTIMIZING PORTFOLIO IS EFFICIENT, THEN THEENVELOPE PORTFOLIO IS OPTIMALThe portfolio x determined by the constant c= 4% is optimalVariance-covariance matrixExpectedreturns0.40 0.03 0.02 0.00 0.060.03 0.20 0.00 -0.06 0.050.02 0.00 0.30 0.03 0.070.00 -0.06 0.03 0.10 0.08Constant 0.04Computing an envelope portfolio with constant = 0.04zEnvelopeportfoliox0.0330 - =MMULT(MINVERSE(A3:D6),F3:F6-B8) 0.04230.1959 0.25140.0468 0.06010.5035 0.6462Sum 1.0000E(rx) 0.0710sx 0.19950 .2 2 50 .2 1 50 .2 0 50 .1 9 50 .1 8 50 .1 7 50 .0 6 0 90 .0 6 30 .0 6 5 10 .0 6 7 20 .0 6 9 30 .0 7 1 40 .0 7 3 50 .0 7 5 6A B C D E F1234567891011121314151617181920212223242526272829303132333435363738394041424344A B C D E F454647- =A12:A15/SUM(A12:A15)- =SUM(F12:F15)IF c = rf AND THE OPTIMIZING PORTFOLIO IS EFFICIENT, THEN THEENVELOPE PORTFOLIO IS OPTIMALThe portfolio x determined by the constant c= 4% is optimal0 .2 2 50 .2 1 50 .2 0 50 .1 9 50 .1 8 50 .1 7 50 .0 6 0 90 .0 6 30 .0 6 5 10 .0 6 7 20 .0 6 9 30 .0 7 1 40 .0 7 3 50 .0 7 5 6G H I J K1234567891011121314151617181920212223242526272829303132333435363738394041424344G H I J K454647Sigma Return0.219938 0.0733770.216432 0.0730390.213108 0.0727010.209973 0.0723630.207038 0.0720250.204309 0.0716870.201796 0.0713490.199507 0.071011 0.07100.198449 0.0708420.19745 0.0706730.19651 0.0705040.195631 0.0703350.194814 0.0701660.194058 0.0699970.193366 0.0698280.192737 0.0696590.192172 0.069490.191672 0.0693210.190869 0.0689830.19033 0.0686450.190058 0.0683070.190053 0.0679690.190317 0.0676310.190847 0.0672930.191642 0.066955L M N12345678910111213141516171819202122232425262728293031323334353637383940414243440.192698 0.0666170.19401 0.0662790.195575 0.065941L M N454647如果c = rf 及优化配置的投资组合是有效的,那么包络线投资组合是最优的由常数 c= 4%确定的投资组合 x是最优的方差-协方差矩阵 期望收益0.40 0.03 0.02 0.00 0.060.03 0.20 0.00 -0.06 0.050.02 0.00 0.30 0.03 0.070.00 -0.06 0.03 0.10 0.08常数 0.04计算包络线投资组合 ，常数 = 0.04z包络线投资组合x0.0330 - =MMULT(MINVERSE(A3:D6),F3:F6-B8) 0.04230.1959 0.25140.0468 0.06010.5035 0.646