Market Security ri risk "CorrelationA,B" I A 0.22 0.3 0.15 Market Rp Sigma B 0.31 0.32 I 0.261589242 0.234659406 1.114761376 II A 0.26 0.29 -0.06 II 0.295148765 0.211251174 1.397146151 B 0.34 0.33 III 0.224674321 0.183191507 1.226445067 III A 0.18 0.2 0.09 B 0.41 0.38 Market I CM 0.09 0.0144 0.0144 0.1024 VRS 0.18 0.0288 1 0 0.0288 0.2048 1 0 1 1 0 1 wi 3.056234719 -3.056234719 0.537897311 w1 0.537897311 -3.056234719 3.056234719 0.462102689 w1 0.462102689 0.537897311 0.462102689 -0.110130073 lambda1 -0.110130073 proof 1 Rp Sigma(P) 1 0 0 3.056234719 -3.056234719 0.537897311 0.537897311 0.261589242 0.234659406 0 1 0 -3.056234719 3.056234719 0.462102689 0.462102689 0 1.38778E-17 1 0.537897311 0.462102689 -0.110130073 -0.110130073 Market II CM 0.0841 -0.005742 -0.005742 0.1089 VRS 0.1682 -0.011484 1 0 -0.011484 0.2178 1 0 1 1 0 1 wi 2.445179085 -2.445179085 0.560640441 w1 0.560640441 -2.445179085 2.445179085 0.439359559 w1 0.439359559 0.560640441 0.439359559 -0.089254117 lambda1 -0.089254117 proof 1 Rp Sigma(P) 1 -1.11022E-16 0 2.445179085 -2.445179085 0.560640441 0.560640441 0.295148765 0.211251174 -5.55112E-17 1 0 -2.445179085 2.445179085 0.439359559 0.439359559 -1.38778E-17 -1.38778E-17 1 0.560640441 0.439359559 -0.089254117 -0.089254117 Market II CM 0.04 0.00684 0.00684 0.1444 VRS 0.08 0.01368 1 0 0.01368 0.2888 1 0 1 1 0 1 wi 2.928772259 -2.928772259 0.805763824 w1 0.805763824 -2.928772259 2.928772259 0.194236176 w1 0.194236176 0.805763824 0.194236176 -0.067118257 lambda1 -0.067118257 proof 1 Rp Sigma(P) 1 -1.11022E-16 4.44089E-16 2.928772259 -2.928772259 0.805763824 0.805763824 0.224674321 0.183191507 0 1 -4.44089E-16 -2.928772259 2.928772259 0.194236176 0.194236176 0 1.38778E-17 1 0.805763824 0.194236176 -0.067118257 -0.067118257 ##### Sheet/List 2 ##### Company 1 Company 2 Company 3 Correlation m 0.8 0.3 0.6 "s1,2" -0.1 s 1.2 0.8 1.1 "s1,3" -0.5 "s2,3" 0.3 0.351615772 0.31396383 0.334420397 CM 0.351615772 1.44 -0.096 -0.66 1.44 -0.096 -0.66 0.31396383 -0.096 0.64 0.264 -0.096 0.64 0.264 0.334420397 -0.66 0.264 1.21 -0.66 0.264 1.21 VRS 0.178032458 -0.010597885 -0.077607741 2.88 -0.192 -1.32 1 0 -0.010597885 0.063086904 0.02771892 -0.192 1.28 0.528 1 0 -0.077607741 0.02771892 0.135322773 -1.32 0.528 2.42 1 0 wi 0.351615772 0.31396383 0.334420397 1 1 1 0 1 Var(P) 0.255468722 wi Rp Var(p) wT*V*w sum_w+cv 0.222690888 -0.254359486 0.031668598 0.351615772 0.351615772 0.576134005 0.255468722 0.255468722 0.255468722 0.255468722 -0.254359486 0.668746463 -0.414386977 0.31396383 0.31396383 0.505439138 0.255468722 0.031668598 -0.414386977 0.382718379 0.334420397 0.334420397 0.255468722 0.351615772 0.31396383 0.334420397 -0.510937444 -0.510937444 Proof 1 0.222690888 -0.254359486 0.031668598 0.351615772 -0.254359486 0.668746463 -0.414386977 0.31396383 0.031668598 -0.414386977 0.382718379 0.334420397 0.351615772 0.31396383 0.334420397 -0.510937444 0.501876966 0.00125131 0.496871724 Rp=15% VRS 1.44 -0.096 -0.66 2.88 -0.192 -1.32 1 0.8 0 -0.096 0.64 0.264 -0.192 1.28 0.528 1 0.3 0 -0.66 0.264 1.21 -1.32 0.528 2.42 1 0.6 0 1 1 1 0 0 1 0.362707904 -6.02884E-05 -0.164583192 0.8 0.3 0.6 0 0 0.7 -6.02884E-05 1.0021E-06 0.00016414 -0.164583192 0.00016414 0.298726627 wi Rp Var(P) SigmaP 0.076093206 0.050728804 -0.12682201 -0.347289391 1.213094795 w1 0.501876966 0.7 0.332476851 0.576608056 0.050728804 0.033819203 -0.084548006 1.768473739 -2.52460347 w2 0.00125131 -0.12682201 -0.084548006 0.211370016 -0.421184348 1.311508675 w3 0.496871724 -0.347289391 1.768473739 -0.421184348 -3.84297122 5.783435355 lambda1 0.205433528 1.213094795 -2.52460347 1.311508675 5.783435355 -10.03835098 lambda2 -1.243410328 Proof 1 ##### Sheet/List 3 ##### Sec1 Sec 2 Sec 3 Sec 4 Sec 5 Sec 6 Sec 7 ri (%) 2.137199771 0.04443472 -0.290668798 -0.230272848 -0.773325871 -1.12240341 1.235036437 Sec 1 80.5 82.7 85.3 85.1 123.9 22 3.5 1.9 Sec 2 82.7 184.7 131.5 69.4 49.5 58 -9.9 6.1 367.6936402 7.85367765 -52.98983461 -41.8810356 -204.7759573 -52.77360681 9.238318563 Sec 3 85.3 131.5 374.2 384.5 366.5 103.8 343.5 2.9 7.85367765 0.364679863 -1.698425925 -0.710108395 -1.700944652 -2.892673482 -0.543297127 Sec 4 85.1 69.4 384.5 684.8 599.1 51.6 502.7 4 -52.98983461 -1.698425925 31.61554051 25.73578923 82.38249347 33.86450601 -123.311882 Sec 5 123.9 49.5 366.5 599.1 871.4 -21.2 520.4 5.7 -41.8810356 -0.710108395 25.73578923 36.31192041 106.6853023 13.33648596 -142.9655466 Sec 6 22 58 103.8 51.6 -21.2 89.7 74.4 3.4 -204.7759573 -1.700944652 82.38249347 106.6853023 521.1258721 -18.40125221 -497.0265612 Sec 7 3.5 -9.9 343.5 502.7 520.4 74.4 574.6 4.9 -52.77360681 -2.892673482 33.86450601 13.33648596 -18.40125221 113.0031104 -103.1339576 9.238318563 -0.543297127 -123.311882 -142.9655466 -497.0265612 -103.1339576 876.4459992 VRS Min_variance 161 165.4 170.6 170.2 247.8 44 7 1 0 165.4 369.4 263 138.8 99 116 -19.8 1 0 367.6936402 7.85367765 -52.98983461 -41.8810356 -204.7759573 -52.77360681 9.238318563 170.6 263 748.4 769 733 207.6 687 1 0 7.85367765 0.364679863 -1.698425925 -0.710108395 -1.700944652 -2.892673482 -0.543297127 170.2 138.8 769 1369.6 1198.2 103.2 1005.4 1 0 -52.98983461 -1.698425925 31.61554051 25.73578923 82.38249347 33.86450601 -123.311882 247.8 99 733 1198.2 1742.8 -42.4 1040.8 1 0 -41.8810356 -0.710108395 25.73578923 36.31192041 106.6853023 13.33648596 -142.9655466 44 116 207.6 103.2 -42.4 179.4 148.8 1 0 -204.7759573 -1.700944652 82.38249347 106.6853023 521.1258721 -18.40125221 -497.0265612 7 -19.8 687 1005.4 1040.8 148.8 1149.2 1 0 -52.77360681 -2.892673482 33.86450601 13.33648596 -18.40125221 113.0031104 -103.1339576 1 1 1 1 1 1 1 0 1 9.238318563 -0.543297127 -123.311882 -142.9655466 -497.0265612 -103.1339576 876.4459992 wi Rp Var(P) Sigma(P) 0.077541972 -0.006186729 0.008788437 -0.009709852 -0.037687458 -0.077748274 0.045001904 2.137199771 2.137199771 0.395249927 15.14374206 3.891496121 -0.006186729 0.007803096 -0.003616667 0.000167497 0.001056212 -0.000477668 0.001254258 0.04443472 0.04443472 15.14374206 0.008788437 -0.003616667 0.007543944 -0.002286279 -0.004259006 -0.008793788 0.00262336 -0.290668798 -0.290668798 -0.009709852 0.000167497 -0.002286279 0.004278044 0.004071768 0.010660407 -0.007181586 -0.230272848 -0.230272848 -0.037687458 0.001056212 -0.004259006 0.004071768 0.02058011 0.040174616 -0.023936242 -0.773325871 -0.773325871 -0.077748274 -0.000477668 -0.008793788 0.010660407 0.040174616 0.086382111 -0.050197403 -1.12240341 -1.12240341 0.045001904 0.001254258 0.00262336 -0.007181586 -0.023936242 -0.050197403 0.032435709 1.235036437 1.235036437 2.137199771 2.137199771 0.04443472 -0.290668798 -0.230272848 -0.773325871 -1.12240341 1.235036437 -30.28748412 -30.28748412 0.04443472 -0.290668798 -0.230272848 -0.773325871 -1.12240341 1.235036437 -30.28748412 0.393500391 0.437029695 -0.655179483 -0.044328174 0.036014728 0.415333712 0.417629131 E(Rp)=5% VRS 161 165.4 170.6 170.2 247.8 44 7 1 1.9 0 0.393500391 80.5 82.7 85.3 85.1 123.9 22 3.5 165.4 369.4 263 138.8 99 116 -19.8 1 6.1 0 0.437029695 82.7 184.7 131.5 69.4 49.5 58 -9.9 170.6 263 748.4 769 733 207.6 687 1 2.9 0 -0.655179483 85.3 131.5 374.2 384.5 366.5 103.8 343.5 170.2 138.8 769 1369.6 1198.2 103.2 1005.4 1 4 0 -0.044328174 85.1 69.4 384.5 684.8 599.1 51.6 502.7 247.8 99 733 1198.2 1742.8 -42.4 1040.8 1 5.7 0 0.036014728 123.9 49.5 366.5 599.1 871.4 -21.2 520.4 44 116 207.6 103.2 -42.4 179.4 148.8 1 3.4 0 0.415333712 22 58 103.8 51.6 -21.2 89.7 74.4 7 -19.8 687 1005.4 1040.8 148.8 1149.2 1 4.9 0 0.417629131 3.5 -9.9 343.5 502.7 520.4 74.4 574.6 1 1 1 1 1 1 1 0 0 1 1.9 6.1 2.9 4 5.7 3.4 4.9 0 0 5 12.46482586 14.22203112 -21.99148151 -1.484412384 1.755887217 3.595547509 0.575180292 14.22203112 35.27676809 -37.65277498 -1.344467347 0.779105534 10.52776359 -1.806911685 wi Rp Var(p) Sigma(P) -21.99148151 -37.65277498 160.6291498 11.16699892 -8.647974707 -28.24586151 -93.98917008 0.016039416 0.007660609 -0.004068331 -0.00315134 -0.009140957 -0.023510276 0.01617088 2.286870663 -0.378674055 1 0.393500391 5 39.86212538 6.313645966 -1.484412384 -1.344467347 11.16699892 1.3456231 -0.956443464 -0.950006826 -9.306352769 0.007660609 0.00468536 -0.000721958 -0.001309155 -0.00537105 -0.012689387 0.007745581 0.010736227 0.085258694 2 0.437029695 1.755887217 0.779105534 -8.647974707 -0.956443464 1.130258656 -0.317112373 7.827232162 -0.004068331 -0.000721958 0.004856308 -0.000915259 0.001708481 0.002544365 -0.003403606 -0.259380927 -0.079159711 3 -0.655179483 3.595547509 10.52776359 -28.24586151 -0.950006826 -0.317112373 15.47343764 12.90508601 -0.00315134 -0.001309155 -0.000915259 0.003578658 0.001027626 0.004876574 -0.004107102 -0.246233458 0.040381057 4 -0.044328174 0.575180292 -1.806911685 -93.98917008 -9.306352769 7.827232162 12.90508601 100.2183368 -0.009140957 -0.00537105 0.001708481 0.001027626 0.00733021 0.01499997 -0.01055428 -0.842795834 0.175762112 5 0.036014728 -0.023510276 -0.012689387 0.002544365 0.004876574 0.01499997 0.038550595 -0.024771841 -1.254395478 0.333945838 6 0.415333712 0.01617088 0.007745581 -0.003403606 -0.004107102 -0.01055428 -0.024771841 0.018920369 1.305198807 -0.177513935 7 0.417629131 2.286870663 0.010736227 -0.259380927 -0.246233458 -0.842795834 -1.254395478 1.305198807 -30.65171899 0.921530536 lambda1 -26.04406632 -0.378674055 0.085258694 -0.079159711 0.040381057 0.175762112 0.333945838 -0.177513935 0.921530536 -2.331513485 lambda2 -10.73603689 Proof 1 ##### Sheet/List 4 ##### Risky portfolio A B C D 6.20% 4% 7.50% 8.40% 14.50% 9.70% 17% 20% 1. 2. 3. 4. 5. 0.2 0.4 0.5 0.6 0.8 Portfolio 0.8 0.6 0.5 0.4 0.2 rf 0.035 Rp 1. 2. 3. 4. 5. A 0.0566 0.0512 0.0485 0.0458 0.0404 B 0.039 0.038 0.0375 0.037 0.036 C 0.067 0.059 0.055 0.051 0.043 D 0.0742 0.0644 0.0595 0.0546 0.0448 Sigma 1. 2. 3. 4. 5. A 0.116 0.087 0.0725 0.058 0.029 B 0.0776 0.0582 0.0485 0.0388 0.0194 C 0.136 0.102 0.085 0.068 0.034 D 0.16 0.12 0.1 0.08 0.04