Basic

Basic 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 A B C D Two-Security Portfolios: BASIC Worksheet Parameters for two securities: Er s D 5.00% 8.00% E 7.00% 16.00% E 8.0% F G H I J K L Corr (r) -0.7 -0.008960 Expected return 7.0% 6.0% E 5.0% 4.0% 3.0% 2.0% 1.0% 0.0% 0.0% 5.0% D w(D) 1 0.9 0.8 0.7 0.6 0.5 0.4 0.3 0.2 0.1 0 w(E) 0 0.1 0.2 0.3 0.4 0.5 0.6 0.7 0.8 0.9 1 ERP 5.00% 5.20% 5.40% 5.60% 5.80% 6.00% 6.20% 6.40% 6.60% 6.80% 7.00% sP 8.00% 6.19% 4.75% 4.09% 4.58% 5.93% 7.71% 9.67% 11.74% 13.85% 16.00% 10.0% Standard Deviation 15.0% 20.0% Optimal Portfolio Desired rate of return: w(D) w(E) ERC sC 0.12 -2.50000 3.50000 12.0000% 71.4423% 0.00 0.01 0.02 0.03 0.04 0.05 0.06 0.07 0.08 0.09 0.10 0.11 0.12 0.13 0.14 0.15 0.16 0.17 0.18 0.19 0.20 0.21 0.22 0.23 0.24 0.25 0.26 0.27 0.28 0.29 0.30 0.31 0.32 0.33 0.34 0.35 0.36 0.37 0.38 0.39 0.40 0.41 0.42 0.43 0.44 0.45 0.46 0.47 0.48 0.49 0.50 0.51 0.52 0.53 0.54 0.55 0.56 0.57 0.58 0.59 0.60 0.61 0.62 0.63 0.64 0.65 0.66 0.67 0.68 0.69 0.70 0.71 0.72 0.73 0.74 0.75 0.76 0.77 0.78 0.79 0.80 0.81 0.82 0.83 0.84 0.85 0.86 0.87 0.88 0.89 0.90 0.91 0.92 0.93 0.94 0.95 0.96 0.97 0.98 0.99 1.00 Weight Bonds 1.00 0.99 0.98 0.97 0.96 0.95 0.94 0.93 0.92 0.91 0.90 0.89 0.88 0.87 0.86 0.85 0.84 0.83 0.82 0.81 0.80 0.79 0.78 0.77 0.76 0.75 0.74 0.73 0.72 0.71 0.70 0.69 0.68 0.67 0.66 0.65 0.64 0.63 0.62 0.61 0.60 0.59 0.58 0.57 0.56 0.55 0.54 0.53 0.52 0.51 0.50 0.49 0.48 0.47 0.46 0.45 0.44 0.43 0.42 0.41 0.40 0.39 0.38 0.37 0.36 0.35 0.34 0.33 0.32 0.31 0.30 0.29 0.28 0.27 0.26 0.25 0.24 0.23 0.22 0.21 0.20 0.19 0.18 0.17 0.16 0.15 0.14 0.13 0.12 0.11 0.10 0.09 0.08 0.07 0.06 0.05 0.04 0.03 0.02 0.01 0.00 Weight Expected Standard Stocks Return Deviation 0.00 7.00% 16.00% 0.01 6.98% 15.78% 0.02 6.96% 15.57% 0.03 6.94% 15.35% 0.04 6.92% 15.14% 0.05 6.90% 14.92% 0.06 6.88% 14.71% 0.07 6.86% 14.49% 0.08 6.84% 14.28% 0.09 6.82% 14.07% 0.10 6.80% 13.85% 0.11 6.78% 13.64% 0.12 6.76% 13.43% 0.13 6.74% 13.21% 0.14 6.72% 13.00% 0.15 6.70% 12.79% 0.16 6.68% 12.58% 0.17 6.66% 12.37% 0.18 6.64% 12.16% 0.19 6.62% 11.95% 0.20 6.60% 11.74% 0.21 6.58% 11.53% 0.22 6.56% 11.32% 0.23 6.54% 11.11% 0.24 6.52% 10.90% 0.25 6.50% 10.70% 0.26 6.48% 10.49% 0.27 6.46% 10.28% 0.28 6.44% 10.08% 0.29 6.42% 9.88% 0.30 6.40% 9.67% 0.31 6.38% 9.47% 0.32 6.36% 9.27% 0.33 6.34% 9.07% 0.34 6.32% 8.87% 0.35 6.30% 8.67% 0.36 6.28% 8.48% 0.37 6.26% 8.28% 0.38 6.24% 8.09% 0.39 6.22% 7.90% 0.40 6.20% 7.71% 0.41 6.18% 7.52% 0.42 6.16% 7.33% 0.43 6.14% 7.15% 0.44 6.12% 6.97% 0.45 6.10% 6.79% 0.46 6.08% 6.61% 0.47 6.06% 6.44% 0.48 6.04% 6.26% 0.49 6.02% 6.10% 0.50 6.00% 5.93% 0.51 5.98% 5.77% 0.52 5.96% 5.62% 0.53 5.94% 5.47% 0.54 5.92% 5.32% 0.55 5.90% 5.18% 0.56 5.88% 5.05% 0.57 5.86% 4.92% 0.58 5.84% 4.80% 0.59 5.82% 4.69% 0.60 5.80% 4.58% 0.61 5.78% 4.49% 0.62 5.76% 4.40% 0.63 5.74% 4.32% 0.64 5.72% 4.25% 0.65 5.70% 4.20% 0.66 5.68% 4.15% 0.67 5.66% 4.12% 0.68 5.64% 4.10% 0.69 5.62% 4.09% 0.70 5.60% 4.09% 0.71 5.58% 4.11% 0.72 5.56% 4.14% 0.73 5.54% 4.18% 0.74 5.52% 4.23% 0.75 5.50% 4.29% 0.76 5.48% 4.36% 0.77 5.46% 4.44% 0.78 5.44% 4.54% 0.79 5.42% 4.64% 0.80 5.40% 4.75% 0.81 5.38% 4.86% 0.82 5.36% 4.99% 0.83 5.34% 5.12% 0.84 5.32% 5.26% 0.85 5.30% 5.40% 0.86 5.28% 5.55% 0.87 5.26% 5.70% 0.88 5.24% 5.86% 0.89 5.22% 6.02% 0.90 5.20% 6.19% 0.91 5.18% 6.36% 0.92 5.16% 6.53% 0.93 5.14% 6.70% 0.94 5.12% 6.88% 0.95 5.10% 7.06% 0.96 5.08% 7.25% 0.97 5.06% 7.43% 0.98 5.04% 7.62% 0.99 5.02% 7.81% 1.00 5.00% 8.00% MVP A B C D E 1 Two-Security Portfolios: MVP Worksheet 8.0% 2 3 Parameters for two securities: 7.0% Corr (r) 4 Er s 5 D 5.00% 8.00% -0.7 6 E 7.00% 16.00% -0.008960 6.0% 7 8 5.0% 9 10 4.0% 11 Min Var Portfolio Short Sales No Short 12 Allowed Sales 3.0% 13 w(D) 0.69231 0.69231 14 w(E) 0.30769 0.30769 2.0% ERP 15 5.6154% 5.6154% sP 16 4.0913% 4.0913% 1.0% 17 18 0.0% 19 0.0% 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 Optimal Portfolio w/o a Risk Free Asset 35 36 Desired rate of return: 0.12 37 38 w(D) -2.50000 39 w(E) 3.50000 ERC 40 12.0000% sC 41 71.4423% 42 43 44 Weight Weight Expected Standard 45 Bonds Stocks Return Deviation 46 0.00 1.00 0.00 7.00% 16.00% 47 0.01 0.99 0.01 6.98% 15.78% 48 0.02 0.98 0.02 6.96% 15.57% 49 0.03 0.97 0.03 6.94% 15.35% 50 0.04 0.96 0.04 6.92% 15.14% 51 0.05 0.95 0.05 6.90% 14.92% 52 0.06 0.94 0.06 6.88% 14.71% 53 0.07 0.93 0.07 6.86% 14.49% 54 0.08 0.92 0.08 6.84% 14.28% 55 0.09 0.91 0.09 6.82% 14.07% 56 0.10 0.90 0.10 6.80% 13.85% 57 0.11 0.89 0.11 6.78% 13.64% 58 0.12 0.88 0.12 6.76% 13.43% 59 0.13 0.87 0.13 6.74% 13.21% 60 0.14 0.86 0.14 6.72% 13.00% 61 0.15 0.85 0.15 6.70% 12.79% 62 0.16 0.84 0.16 6.68% 12.58% 63 0.17 0.83 0.17 6.66% 12.37% 64 0.18 0.82 0.18 6.64% 12.16% 65 0.19 0.81 0.19 6.62% 11.95% 66 0.20 0.80 0.20 6.60% 11.74% 67 0.21 0.79 0.21 6.58% 11.53% 68 0.22 0.78 0.22 6.56% 11.32% 69 0.23 0.77 0.23 6.54% 11.11% 70 0.24 0.76 0.24 6.52% 10.90% 71 0.25 0.75 0.25 6.50% 10.70% 72 0.26 0.74 0.26 6.48% 10.49% 73 0.27 0.73 0.27 6.46% 10.28% 74 0.28 0.72 0.28 6.44% 10.08% 75 0.29 0.71 0.29 6.42% 9.88% 76 0.30 0.70 0.30 6.40% 9.67% 77 0.31 0.69 0.31 6.38% 9.47% 78 0.32 0.68 0.32 6.36% 9.27% 79 0.33 0.67 0.33 6.34% 9.07% 80 0.34 0.66 0.34 6.32% 8.87% 81 0.35 0.65 0.35 6.30% 8.67% 82 0.36 0.64 0.36 6.28% 8.48% 83 0.37 0.63 0.37 6.26% 8.28% 84 0.38 0.62 0.38 6.24% 8.09% 85 0.39 0.61 0.39 6.22% 7.90% 86 0.40 0.60 0.40 6.20% 7.71% 87 0.41 0.59 0.41 6.18% 7.52% 88 0.42 0.58 0.42 6.16% 7.33% 89 0.43 0.57 0.43 6.14% 7.15% 90 0.44 0.56 0.44 6.12% 6.97% 91 0.45 0.55 0.45 6.10% 6.79% 92 0.46 0.54 0.46 6.08% 6.61% 93 0.47 0.53 0.47 6.06% 6.44% 94 0.48 0.52 0.48 6.04% 6.26% 95 0.49 0.51 0.49 6.02% 6.10% 96 0.50 0.50 0.50 6.00% 5.93% 97 0.51 0.49 0.51 5.98% 5.77% 98 0.52 0.48 0.52 5.96% 5.62% 99 0.53 0.47 0.53 5.94% 5.47% 100 0.54 0.46 0.54 5.92% 5.32% 101 0.55 0.45 0.55 5.90% 5.18% 102 0.56 0.44 0.56 5.88% 5.05% 103 0.57 0.43 0.57 5.86% 4.92% 104 0.58 0.42 0.58 5.84% 4.80% 105 0.59 0.41 0.59 5.82% 4.69% 106 0.60 0.40 0.60 5.80% 4.58% 107 0.61 0.39 0.61 5.78% 4.49% 108 0.62 0.38 0.62 5.76% 4.40% 109 0.63 0.37 0.63 5.74% 4.32% 110 0.64 0.36 0.64 5.72% 4.25% 111 0.65 0.35 0.65 5.70% 4.20% 112 0.66 0.34 0.66 5.68% 4.15% 113 0.67 0.33 0.67 5.66% 4.12% 114 0.68 0.32 0.68 5.64% 4.10% 115 0.69 0.31 0.69 5.62% 4.09% 116 0.70 0.30 0.70 5.60% 4.09% 117 0.71 0.29 0.71 5.58% 4.11% 118 0.72 0.28 0.72 5.56% 4.14% 119 0.73 0.27 0.73 5.54% 4.18% 120 0.74 0.26 0.74 5.52% 4.23% 121 0.75 0.25 0.75 5.50% 4.29% 122 0.76 0.24 0.76 5.48% 4.36% 123 0.77 0.23 0.77 5.46% 4.44% 124 0.78 0.22 0.78 5.44% 4.54% 125 0.79 0.21 0.79 5.42% 4.64% 126 0.80 0.20 0.80 5.40% 4.75% 127 0.81 0.19 0.81 5.38% 4.86% 128 0.82 0.18 0.82 5.36% 4.99% 129 0.83 0.17 0.83 5.34% 5.12% 130 0.84 0.16 0.84 5.32% 5.26% 131 0.85 0.15 0.85 5.30% 5.40% 132 0.86 0.14 0.86 5.28% 5.55% 133 0.87 0.13 0.87 5.26% 5.70% 134 0.88 0.12 0.88 5.24% 5.86% 135 0.89 0.11 0.89 5.22% 6.02% 136 0.90 0.10 0.90 5.20% 6.19% 137 0.91 0.09 0.91 5.18% 6.36% 138 0.92 0.08 0.92 5.16% 6.53% 139 0.93 0.07 0.93 5.14% 6.70% 140 0.94 0.06 0.94 5.12% 6.88% 141 0.95 0.05 0.95 5.10% 7.06% 142 0.96 0.04 0.96 5.08% 7.25% 143 0.97 0.03 0.97 5.06% 7.43% 144 0.98 0.02 0.98 5.04% 7.62% 145 0.99 0.01 0.99 5.02% 7.81% 146 1.00 0.00 1.00 5.00% 8.00% Expected return F G H I J K L M N E MVP D 5.0% 10.0% Standard Deviation 15.0% 20.0% w(D) 1 0.9 0.8 0.7 0.6 0.5 0.4 0.3 0.2 0.1 0 w(E) 0 0.1 0.2 0.3 0.4 0.5 0.6 0.7 0.8 0.9 1 ERP 5.00% 5.20% 5.40% 5.60% 5.80% 6.00% 6.20% 6.40% 6.60% 6.80% 7.00% sP 8.00% 6.19% 4.75% 4.09% 4.58% 5.93% 7.71% 9.67% 11.74% 13.85% 16.00% +RiskFree A B C D 1 Two-Security Portfolios: +RiskFree Worksheet 2 3 Asset Allocation Analysis: Risk and Return Corr (r) 4 Er s 5 D 5.00% 8.00% -0.7 6 E 7.00% 16.00% -0.008960 rf 7 5.00% 0 8 9 10 Optimal Risky Port Short Sales No Short 11 Allowed Sales 12 w(D) 0.58333 0.58333 13 w(E) 0.41667 0.41667 ERP 14 5.8333% 5.8333% sP 15 4.7610% 4.7610% 16 17 Reward to variability 0.17504 0.17504 18 19 20 21 22 23 24 25 26 27 28 29 Optimal Portfolio with a Risk Free Asset 30 Short Sales No Short 31 Allowed Sales 32 Desired rate of return: 0.12 33 34 Weight OP 8.40000 8.40000 35 Weight RF -7.40000 -7.40000 ERC 36 12.0000% 12.0000% sC 37 39.9920% 39.9920% 38 39 40 41 42 43 44 45 46 47 48 49 Optimal Portfolio w/o a Risk Free Asset 50 51 Desired rate of return: 0.12 52 53 w(D) -2.50000 54 w(E) 3.50000 ERC 55 12.0000% sC 56 71.4423% 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 Weight Bonds 1.00 0.99 0.98 0.97 0.96 0.95 0.94 0.93 0.92 0.91 0.90 0.89 0.88 0.87 0.86 0.85 0.84 0.83 0.82 0.81 0.80 0.79 0.78 0.77 0.76 0.75 0.74 0.73 0.72 0.71 0.70 0.69 0.68 0.67 0.66 0.65 0.64 0.63 0.62 0.61 0.60 0.59 0.58 0.57 0.56 0.55 0.54 0.53 0.52 0.51 0.50 0.49 0.48 0.47 0.46 0.45 0.44 0.43 0.42 0.41 0.40 0.39 0.38 0.37 0.36 0.35 0.34 0.33 0.32 0.31 0.30 0.29 0.28 0.27 0.26 0.25 0.24 0.23 0.22 0.21 0.20 0.19 0.18 0.17 0.16 0.15 0.14 0.13 0.12 0.11 0.10 0.09 0.08 0.07 0.06 0.05 0.04 0.03 0.02 0.01 0.00 E 8.0% F G H I J K L 7.0% 6.0% 5.0% 0.07 D 0.05 D rf E rf 5.00% E Expected return 4.0% 3.0% 2.0% 1.0% 0.0% 0.0% 5.0% 10.0% Standard Deviation 15.0% 20.0% w(D) 1 0.9 0.8 0.7 0.6 0.5 0.4 0.3 0.2 0.1 0 Min Var Portfolio CAL(MV) w(E) 0 0.1 0.2 0.3 0.4 0.5 0.6 0.7 0.8 0.9 1 ERP 5.00% 5.20% 5.40% 5.60% 5.80% 6.00% 6.20% 6.40% 6.60% 6.80% 7.00% sP 8.00% 6.19% 4.75% 4.09% 4.58% 5.93% 7.71% 9.67% 11.74% 13.85% 16.00% w(D) w(E) ERP sP Short SalesNo Short Allowed Sales 0.69231 0.69231 0.30769 0.30769 5.6154% 5.6154% 4.0913% 4.0913% 0.00 0.01 0.02 0.03 0.04 0.05 0.06 0.07 0.08 0.09 0.10 0.11 0.12 0.13 0.14 0.15 0.16 0.17 0.18 0.19 0.20 0.21 0.22 0.23 0.24 0.25 0.26 0.27 0.28 0.29 0.30 0.31 0.32 0.33 0.34 0.35 0.36 0.37 0.38 0.39 0.40 0.41 0.42 0.43 0.44 0.45 0.46 0.47 0.48 0.49 0.50 0.51 0.52 0.53 0.54 0.55 0.56 0.57 0.58 0.59 0.60 0.61 0.62 0.63 0.64 0.65 0.66 0.67 0.68 0.69 0.70 0.71 0.72 0.73 0.74 0.75 0.76 0.77 0.78 0.79 0.80 0.81 0.82 0.83 0.84 0.85 0.86 0.87 0.88 0.89 0.90 0.91 0.92 0.93 0.94 0.95 0.96 0.97 0.98 0.99 1.00 Weight Expected Standard Stocks Return Deviation 0.00 7.00% 16.00% 0.01 6.98% 15.78% 0.02 6.96% 15.57% 0.03 6.94% 15.35% 0.04 6.92% 15.14% 0.05 6.90% 14.92% 0.06 6.88% 14.71% 0.07 6.86% 14.49% 0.08 6.84% 14.28% 0.09 6.82% 14.07% 0.10 6.80% 13.85% 0.11 6.78% 13.64% 0.12 6.76% 13.43% 0.13 6.74% 13.21% 0.14 6.72% 13.00% 0.15 6.70% 12.79% 0.16 6.68% 12.58% 0.17 6.66% 12.37% 0.18 6.64% 12.16% 0.19 6.62% 11.95% 0.20 6.60% 11.74% 0.21 6.58% 11.53% 0.22 6.56% 11.32% 0.23 6.54% 11.11% 0.24 6.52% 10.90% 0.25 6.50% 10.70% 0.26 6.48% 10.49% 0.27 6.46% 10.28% 0.28 6.44% 10.08% 0.29 6.42% 9.88% 0.30 6.40% 9.67% 0.31 6.38% 9.47% 0.32 6.36% 9.27% 0.33 6.34% 9.07% 0.34 6.32% 8.87% 0.35 6.30% 8.67% 0.36 6.28% 8.48% 0.37 6.26% 8.28% 0.38 6.24% 8.09% 0.39 6.22% 7.90% 0.40 6.20% 7.71% 0.41 6.18% 7.52% 0.42 6.16% 7.33% 0.43 6.14% 7.15% 0.44 6.12% 6.97% 0.45 6.10% 6.79% 0.46 6.08% 6.61% 0.47 6.06% 6.44% 0.48 6.04% 6.26% 0.49 6.02% 6.10% 0.50 6.00% 5.93% 0.51 5.98% 5.77% 0.52 5.96% 5.62% 0.53 5.94% 5.47% 0.54 5.92% 5.32% 0.55 5.90% 5.18% 0.56 5.88% 5.05% 0.57 5.86% 4.92% 0.58 5.84% 4.80% 0.59 5.82% 4.69% 0.60 5.80% 4.58% 0.61 5.78% 4.49% 0.62 5.76% 4.40% 0.63 5.74% 4.32% 0.64 5.72% 4.25% 0.65 5.70% 4.20% 0.66 5.68% 4.15% 0.67 5.66% 4.12% 0.68 5.64% 4.10% 0.69 5.62% 4.09% 0.70 5.60% 4.09% 0.71 5.58% 4.11% 0.72 5.56% 4.14% 0.73 5.54% 4.18% 0.74 5.52% 4.23% 0.75 5.50% 4.29% 0.76 5.48% 4.36% 0.77 5.46% 4.44% 0.78 5.44% 4.54% 0.79 5.42% 4.64% 0.80 5.40% 4.75% 0.81 5.38% 4.86% 0.82 5.36% 4.99% 0.83 5.34% 5.12% 0.84 5.32% 5.26% 0.85 5.30% 5.40% 0.86 5.28% 5.55% 0.87 5.26% 5.70% 0.88 5.24% 5.86% 0.89 5.22% 6.02% 0.90 5.20% 6.19% 0.91 5.18% 6.36% 0.92 5.16% 6.53% 0.93 5.14% 6.70% 0.94 5.12% 6.88% 0.95 5.10% 7.06% 0.96 5.08% 7.25% 0.97 5.06% 7.43% 0.98 5.04% 7.62% 0.99 5.02% 7.81% 1.00 5.00% 8.00%