# portfolio by zhangyun

VIEWS: 13 PAGES: 12

• pg 1
```									                                                          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

```
To top