| Assignment 2: Question 4 (Demonstration) |
| | Std. Dev.(%) | Average Return (%) |
| A | 18.00% | 18.20% |
| B | 21.00% | 13.00% |
| C | 23.00% | 10.60% |
| X | 19.00% | 22.40% |
| Y | 23.50% | 15.50% |
| Z | 18.70% | 19.00% |
| | | |
| Simple mean | | 0.1645 |
| | Correlation Matrix |
| | A | B | C | X | Y | Z | |
| A | 1.00 | 0.23 | 0.56 | 0.13 | 0.77 | 0.19 | |
| B | 0.23 | 1.00 | 0.41 | 0.80 | 0.15 | 0.33 | |
| C | 0.56 | 0.41 | 1.00 | 0.10 | 0.08 | 0.17 | |
| X | 0.13 | 0.80 | 0.10 | 1.00 | 0.20 | 0.40 | |
| Y | 0.77 | 0.15 | 0.08 | 0.20 | 1.00 | 0.55 | |
| Z | 0.19 | 0.33 | 0.17 | 0.40 | 0.55 | 1.00 | |
| | | | | | | | |
| | Covariance Matrix |
| | A | B | C | X | Y | Z | |
| A | 0.0324 | 0.0087 | 0.0232 | 0.0044 | 0.0326 | 0.0064 | |
| B | 0.0087 | 0.0441 | 0.0198 | 0.0319 | 0.0074 | 0.0130 | |
| C | 0.0232 | 0.0198 | 0.0529 | 0.0044 | 0.0043 | 0.0073 | |
| X | 0.0044 | 0.0319 | 0.0044 | 0.0361 | 0.0089 | 0.0142 | |
| Y | 0.0326 | 0.0074 | 0.0043 | 0.0089 | 0.0552 | 0.0242 | |
| Z | 0.0064 | 0.0130 | 0.0073 | 0.0142 | 0.0242 | 0.0350 | |
| | | | | | | | |
| | A | B | C | X | Y | Z | |
| Weights | 0.3973 | 0.2665 | 0.1025 | -0.0219 | -0.0864 | 0.3420 | |
| 0.3973 | 0.0051 | 0.0009 | 0.0009 | -0.0000 | -0.0011 | 0.0009 | |
| 0.2665 | 0.0009 | 0.0031 | 0.0005 | -0.0002 | -0.0002 | 0.0012 | |
| 0.1025 | 0.0009 | 0.0005 | 0.0006 | -0.0000 | -0.0000 | 0.0003 | |
| -0.0219 | -0.0000 | -0.0002 | -0.0000 | 0.0000 | 0.0000 | -0.0001 | |
| -0.0864 | -0.0011 | -0.0002 | -0.0000 | 0.0000 | 0.0004 | -0.0007 | |
| 0.3420 | 0.0009 | 0.0012 | 0.0003 | -0.0001 | -0.0007 | 0.0041 | |
| 1.0000 | 0.0067 | 0.0054 | 0.0022 | -0.0003 | -0.0016 | 0.0056 | |
| portfolio variance | 0.0180 |
| portfolio st. dev. | 0.1342 |
| portfolio mean | 0.1645 |
| | The second run of Solver as follows |
| | A | B | C | X | Y | Z |
| Weights | 0.5737 | 0.2709 | 0.0078 | -0.0422 | -0.2265 | 0.4164 |
| 0.5737 | 0.0107 | 0.0014 | 0.0001 | -0.0001 | -0.0042 | 0.0015 |
| 0.2709 | 0.0014 | 0.0032 | 0.0000 | -0.0004 | -0.0005 | 0.0015 |
| 0.0078 | 0.0001 | 0.0000 | 0.0000 | -0.0000 | -0.0000 | 0.0000 | |
| -0.0422 | -0.0001 | -0.0004 | -0.0000 | 0.0001 | 0.0001 | -0.0002 |
| -0.2265 | -0.0042 | -0.0005 | -0.0000 | 0.0001 | 0.0028 | -0.0023 |
| 0.4164 | 0.0015 | 0.0015 | 0.0000 | -0.0002 | -0.0023 | 0.0061 |
| 1.0000 | 0.0093 | 0.0053 | 0.0002 | -0.0006 | -0.0041 | 0.0065 |
| portfolio variance | 0.0167 |
| portfolio st. dev. | 0.1291 |
| portfolio mean | 0.1750 |
| | See the changes in portfolio mean, portfolio variance, and standard deviation. |
| | You many run as many times as needed in order to draw the efficient frontier. I am not aware of "one-push-of-the-button" software just yet. |
| | Click the "tools" bar, and then click the "Solver." |
| | The "Solver" window will pop up. For each run, you need to change the specifications of some parameters. |
| | Try it out yourself! I only did two, you need to run more. |
| | Note: in your own MS-Excel, you need first to activate the Solver under Add-Ins. Click "Tools" and then click "Add-Ins" and then check the little box in front of "Solver." |
| | Then click "Ok" to activate the Solver. |
SolutionGuru
fina2.xlsx
