VIEWS: 13 PAGES: 12 POSTED ON: 10/5/2011 Public Domain
ef Table of correlations Asset A Asset B Asset C Things in dark grey are input parameters Asset A 1.0000 0.3694 0.1539 Things in light grey are computed intermediate things Asset B 0.3694 1.0000 0.2148 Things in yellow are labels Asset C 0.1539 0.2148 1.0000 Things in red are "Solver" inputs or outputs Table of expected returns and risk E(return) Risk Asset A 20.00% 28.00% Asset B 18.00% 33.00% Asset C 33.00% 40.00% Table of covariances Asset A Asset B Asset C Asset A 7.84% 3.41% 1.72% Asset B 3.41% 10.89% 2.84% Asset C 1.72% 2.84% 16.00% Proporition of wealth invested in Asset A 150.80% Asset B -111.15% Asset C 60.34% Total 1 (this is a CONSTRAINT for "Solver", this cell B23 must be forced to equal one) (it is the constraint that the total proportions of our wealth must equal one) This bit is used to find the risk of the portfolio Asset A Asset B Asset C Proportion 150.80% -111.15% 60.34% These numbers are a table of, for instance Asset A 150.80% 17.8294% -5.7210% 1.5685% proportion of A * proportion of B * Asset B -111.15% -5.7210% 13.4529% -1.9016% covariance of A and B Asset C 60.34% 1.5685% -1.9016% 5.8260% Expected return on the portfolio of all 3 assets 30.07% (it you are minimizing risk for a given return you should tell "Solver" that this cell, B35, is fixed--at whatever level of return you want. If you are maximizing return for a fixed risk, you should tell "Solver" to maximize this cell Risk associated with the portfolio of all 3 assets 50.00% (if you are minimizing risk for a given return you should tell "Solver" to minimize this cell, B40. If you are maximizing return for a fixed risk you should tell "Solver" that this cell is fixed.) Now use "solver" to either minimize the risk for a given return (ie minimize B37 for a prescribed value of B35) or maximize return for a given risk (ie, maximize B35 for a given value of B37). In both cases B23 should be constrained to equal 1, this is the condition that our "total wealth" is invested in the assets. Page 1 ef grey are input parameters rey are computed intermediate things e "Solver" inputs or outputs forced to equal one) must equal one) are a table of, for instance * proportion of B * "Solver" that If you are maximizing "Solver" to minimize Page 2 Table of correlations Asset A Asset B Asset C Things in dark grey are input parameters Asset A 100.00% 36.94% 15.39% Things in light grey are computed intermediate things Asset B 36.94% 100.00% 21.48% Things in yellow are labels Asset C 15.39% 21.48% 100.00% Things in red are "Solver" inputs or outputs Table of expected returns and risk on the risky assets E(return) Risk Asset A 20.00% 28.00% Asset B 18.00% 33.00% Asset C 33.00% 40.00% Riskfree rate 6.00% The return on the risk free asset Table of covariances for the risky assets Asset A Asset B Asset C Construct covariance matrix using formulae Asset A 7.8400% 3.4133% 1.7237% such as covar(A,B)=risk(A)*risk(B)*correl(A,B) Asset B 3.4133% 10.8900% 2.8354% Asset C 1.7237% 2.8354% 16.0000% Proporition of wealth invested in purely risky assets Asset A 0.4261 Normally these values are set by "Solver" but it may be necessary to Asset B 0.0953 reset them to "sensible" values and then re-run "Solver" if "Solver" Asset C 0.4785 fails to find a solution (eg, B23=1/3, B24=1/3, B25=1/3 ) Total 1.0000 (this is a CONSTRAINT for "Solver", this cell B23 must be forced to equal one) (it is the constraint that the sum of the proportions of our wealth must equal one) This bit is used to find the risk of the portfolio of purely risky assets Asset A Asset B Asset C Proportion 42.61% 9.53% 47.85% These numbers are a table of, for instance Asset A 42.61% 1.4237% 0.1386% 0.3515% proportion of A * proportion of B * Asset B 9.53% 0.1386% 0.0989% 0.1293% covariance of A and B Asset C 47.85% 0.3515% 0.1293% 3.6640% Expected return on the portfolio of all 3 risky assets 26.03% Risk associated with the portfolio of all 3 risky assets 25.35% Market Price of Risk ( = Slope of the Capital Market Line) 0.79019019 To find the Market Price of Risk (and hence the Capital Market Line) use "solver" to maximize cell B44 subject to the constraint that cell B26 is equal to one. You access "solver" from the "Tools" menu. grey are input parameters rey are computed intermediate things e "Solver" inputs or outputs iance matrix using formulae A,B)=risk(A)*risk(B)*correl(A,B) t it may be necessary to un "Solver" if "Solver" forced to equal one) ealth must equal one) are a table of, for instance * proportion of B * results Building an efficient frontier using Excel and solver is an inefficient process. You have to either maximize the return given a level of risk (this is best for large risks and returns) or minimize the risk for a given level of return (this is best for small risks and returns) for a lot of possible levels of risk and return. Each specific level of risk or return requires a separate solver run. This takes a great deal of time. Below are the results for a three asset model where the expected returns and variance of returns are E(return) Risk Asset A 20.00% 28.00% Asset B 18.00% 33.00% Asset C 33.00% 40.00% the covariance matrix is Asset A Asset B Asset C market price of risk Asset A 7.84% 3.41% 1.72% risk free rate Asset B 3.41% 10.89% 2.84% Asset C 1.72% 2.84% 16.00% and the correlation matrix is Asset A Asset B Asset C Asset A 100.00% 36.94% 15.39% Asset B 36.94% 100.00% 21.48% Asset C 15.39% 21.48% 100.00% efficient frontier risk return % of asset A % of asset B % of asset C 70.76% 0.00% 100.59% 132.82% -133.41% 65.14% 2.00% 96.14% 123.35% -119.48% 59.59% 4.00% 91.68% 113.88% -105.56% 54.13% 6.00% 87.23% 104.40% -91.63% 48.80% 8.00% 82.77% 94.93% -77.70% 43.62% 10.00% 78.32% 85.46% -63.78% 41.11% 11.00% 76.09% 80.72% -56.81% 38.67% 12.00% 73.86% 75.99% -49.85% short selling 36.31% 13.00% 71.64% 71.25% -42.88% 34.05% 14.00% 69.41% 66.51% -35.92% 31.91% 15.00% 67.18% 61.78% -28.96% 29.92% 16.00% 64.95% 57.04% -21.99% 28.10% 17.00% 62.73% 52.30% -15.03% 26.49% 18.00% 60.50% 47.57% -8.07% 25.13% 19.00% 58.27% 42.83% -1.10% 24.07% 20.00% 56.04% 38.10% 5.86% 23.35% 21.00% 53.82% 33.36% 12.82% 22.99% 22.00% 51.59% 28.62% 19.79% no short selling 23.00% 22.90% 49.60% 24.33% 26.07% 24.00% 24.77% 45.38% 15.51% 39.11% 25.00% 25.75% 43.23% 10.90% 45.87% 26.00% 26.52% 41.58% 7.17% 51.25% 27.00% 27.19% 40.11% 3.97% 55.92% 28.00% 27.80% 38.70% 1.12% 60.17% 29.00% 28.37% 37.53% -1.63% 64.10% 30.00% 28.90% 36.28% -4.11% 67.83% 40.00% 33.40% 26.16% -25.37% 99.21% 50.00% 37.31% 17.43% -43.86% 126.43% Page 5 results 60.00% 41.00% 9.25% -61.38% 152.13% 70.00% 44.59% 1.28% -78.36% 177.08% short selling 80.00% 48.11% -6.46% -95.12% 201.58% 90.00% 51.59% -14.22% -111.60% 225.82% 100.00% 55.04% -21.74% -128.10% 249.85% 110.00% 58.48% -29.41% -144.36% 273.77% 120.00% 61.90% -36.96% -160.61% 297.57% 130.00% 65.31% -44.60% -176.72% 321.32% 140.00% 68.71% -52.13% -192.86% 344.99% 150.00% 72.10% -59.77% -208.87% 368.64% minimized risk for a given return maximized return for a given risk Page 6 results of returns are 0.79019019 6.00% capital market line risk return 0.00% 6.00% 4.00% 9.16% 8.00% 12.32% 12.00% 15.48% 16.00% 18.64% 20.00% 21.80% 24.00% 24.96% 28.00% 28.13% 32.00% 31.29% 36.00% 34.45% 40.00% 37.61% 44.00% 40.77% 48.00% 43.93% 52.00% 47.09% 56.00% 50.25% 60.00% 53.41% 64.00% 56.57% o short selling 68.00% 59.73% 72.00% 62.89% 76.00% 66.05% 80.00% 69.22% 84.00% 72.38% 88.00% 75.54% 92.00% 78.70% 96.00% 81.86% 100.00% 85.02% 104.00% 88.18% 108.00% 91.34% Page 7 results 112.00% 94.50% 116.00% 97.66% 120.00% 100.82% 124.00% 103.98% 128.00% 107.14% 132.00% 110.31% 136.00% 113.47% 140.00% 116.63% 144.00% 119.79% 148.00% 122.95% Page 8 graphs op set border efficient frontier assets 80% 60% return 40% 20% 0% 0% 25% 50% 75% 100% risk op set border efficient frontier cap market line 80% 60% return 40% 20% Page 9 graphs 0% 0% 25% 50% 75% 100% risk Page 10 graphs assets 125% 150% assets Page 11 graphs 125% 150% Page 12