Docstoc

portfolio

Document Sample
portfolio Powered By Docstoc
					                                                          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

				
DOCUMENT INFO
Shared By:
Categories:
Tags:
Stats:
views:13
posted:10/5/2011
language:English
pages:12