Portfolio Optimization

Document Sample
Portfolio Optimization Powered By Docstoc
					                                                              Input



Modify Correlation Matrix Manually                                         Data is entered as           Prices
                                Risk free Rate:   0.36%     Maintain Return Level                Target Return
                               Min Constraint:    10.00%   30.00%       0.00%     0.00%     10.00%         0.00%
                               Max Constraint:    70.00%   90.00%      100.00%   80.00%    100.00%       100.00%
                                Current Units:      1.00     1.00         1.00      1.00         1.00        1.00

                               Product Name:       MSFT       IBM         INTC     GOOG         AMZN
                                        Jan-08       31        103          20       564           78
                                        Feb-08       26        110          19       471           64
                                        Mar-08       27        111          20       440           71
                                        Apr-08       27        117          21       574           79
                                       May-08        27        126          22       586           82
                                        Jun-08       27        115          20       526           73
Historic Observation Periods




                                         Jul-08      25        124          21       474           76
                                        Aug-08       26        119          22       463           81
                                        Sep-08       26        114          18       401           73
                                        Oct-08       22         91          15       359           57
                                        Nov-08       20         80          13       293           43
                                        Dec-08       19         83          14       308           51
                                        Jan-09       17         90          12       339           59
                                        Feb-09       16         91          12       338           65
                                        Mar-09       18         96          15       348           73
                                        Apr-09       20        102          15       396           81
                                       May-09        21        105          15       417           78
                                        Jun-09       24        103          16       422           84
                                         Jul-09      23        117          19       443           86
                                        Aug-09       25        118          20       462           81
                                        Sep-09       26        119          19       496           93
                                        Oct-09       28        120          19       536          119
                                        Nov-09       29        126          19       583          136
                                        Dec-09       30        131          20       620          135
                                        Jan-10       31        132          21       580          127




                                                              Page 1
Input




Page 2
                                      Input



Returns
  2.00%
   0.00%     0.00%     0.00%     0.00%         0.00%    0.00%     0.00%     0.00%
 100.00%    100.00%   100.00%   100.00%   100.00%      100.00%   100.00%   100.00%
     1.00      1.00      1.00      1.00         1.00      1.00      1.00      1.00




                                      Page 3
Input




Page 4
                                    Input




 0.00%     0.00%     0.00%     0.00%         0.00%    0.00%     0.00%     0.00%
100.00%   100.00%   100.00%   100.00%   100.00%      100.00%   100.00%   100.00%
   1.00      1.00      1.00      1.00         1.00      1.00      1.00      1.00




                                    Page 5
Input




Page 6
                                    Input




 0.00%     0.00%     0.00%     0.00%         0.00%    0.00%     0.00%     0.00%
100.00%   100.00%   100.00%   100.00%   100.00%      100.00%   100.00%   100.00%
   1.00      1.00      1.00      1.00         1.00      1.00      1.00      1.00




                                    Page 7
Input




Page 8
                                    Input




 0.00%     0.00%     0.00%     0.00%         0.00%    0.00%     0.00%     0.00%
100.00%   100.00%   100.00%   100.00%   100.00%      100.00%   100.00%   100.00%
   1.00      1.00      1.00      1.00         1.00      1.00      1.00      1.00




                                    Page 9
 Input




Page 10
                                     Input




 0.00%     0.00%     0.00%     0.00%         0.00%    0.00%     0.00%     0.00%
100.00%   100.00%   100.00%   100.00%   100.00%      100.00%   100.00%   100.00%
   1.00      1.00      1.00      1.00         1.00      1.00      1.00      1.00




                                    Page 11
 Input




Page 12
                                     Input




 0.00%     0.00%     0.00%     0.00%         0.00%    0.00%     0.00%     0.00%
100.00%   100.00%   100.00%   100.00%   100.00%      100.00%   100.00%   100.00%
   1.00      1.00      1.00      1.00         1.00      1.00      1.00      1.00




                                    Page 13
 Input




Page 14
                                     Input




 0.00%     0.00%     0.00%     0.00%         0.00%    0.00%     0.00%     0.00%
100.00%   100.00%   100.00%   100.00%   100.00%      100.00%   100.00%   100.00%
   1.00      1.00      1.00      1.00         1.00      1.00      1.00      1.00




                                    Page 15
 Input




Page 16
                                     Input




 0.00%     0.00%     0.00%     0.00%         0.00%    0.00%     0.00%     0.00%
100.00%   100.00%   100.00%   100.00%   100.00%      100.00%   100.00%   100.00%
   1.00      1.00      1.00      1.00         1.00      1.00      1.00      1.00




                                    Page 17
 Input




Page 18
                                     Input




 0.00%     0.00%     0.00%     0.00%         0.00%    0.00%     0.00%     0.00%
100.00%   100.00%   100.00%   100.00%   100.00%      100.00%   100.00%   100.00%
   1.00      1.00      1.00      1.00         1.00      1.00      1.00      1.00




                                    Page 19
 Input




Page 20
                                     Input




 0.00%     0.00%     0.00%     0.00%         0.00%    0.00%     0.00%     0.00%
100.00%   100.00%   100.00%   100.00%   100.00%      100.00%   100.00%   100.00%
   1.00      1.00      1.00      1.00         1.00      1.00      1.00      1.00




                                    Page 21
 Input




Page 22
                                     Input




 0.00%     0.00%     0.00%     0.00%         0.00%    0.00%     0.00%     0.00%
100.00%   100.00%   100.00%   100.00%   100.00%      100.00%   100.00%   100.00%
   1.00      1.00      1.00      1.00         1.00      1.00      1.00      1.00




                                    Page 23
 Input




Page 24
                                     Input




 0.00%     0.00%     0.00%     0.00%         0.00%    0.00%
100.00%   100.00%   100.00%   100.00%   100.00%      100.00%
   1.00      1.00      1.00      1.00         1.00      1.00


                                                               796.35
                                                               690.89
                                                               670.53
                                                               818.22
                                                               842.58
                                                               761.92
                                                               720.38
                                                               711.19
                                                               631.17
                                                               544.28
                                                               448.76
                                                               474.61
                                                               516.39
                                                               521.84
                                                               549.76
                                                               613.58
                                                                636.7
                                                               648.56
                                                               687.93
                                                               705.09
                                                               753.32
                                                               821.58
                                                               893.87
                                                               936.28
                                                               890.58




                                    Page 25
 Input




Page 26
             Input



FALSE   2             25   890.58
TRUE    1              0            0




            Page 27
                                                                       CoVar




CORRELATION             Risk free Rate:          0.36%        No. Products:        Mean         0.89%
MATRIX + -              Iterations:               2500                    5        StdDev       9.31%
Product       Weight       Min         Max      Mean     StdDev MSFT      IBM       INTC     GOOG     AMZN
MSFT            3.47%      10.00%      70.00%    0.28%      8.27% 100.00%    24.15%   60.73%   45.91%   53.58%
IBM            14.80%      30.00%      90.00%    1.29%      7.24%           100.00%   59.57%   49.29%   55.17%
INTC            2.34%       0.00%     100.00%    0.58%      8.89%                    100.00%   45.04%   47.28%
GOOG           65.13%       0.00%      80.00%    0.68%    10.93%                              100.00%   67.99%
AMZN           14.28%      10.00%     100.00%    2.92%    13.14%                                       100.00%




                                                                      Page 28
                                                                                  PORTFOLIO OPTIMIZATION RESULTS
                                     CURRENT PORTFOLIO                                                                                              OPTIMAL PORTFOLIO
                                        MSFT                                                                                                                            MSFT
                                         4%                                                                                                                             16%

                                                        IBM         INTC
                                      AMZN                           2%
                                                        15%
                                       14%



                                                                                                                                                                             IBM
                                                                                                                             AMZN                                            33%
                                                                                                                              45%




                                            GOOG                                                                                                    GOOG              INTC
                                             65%                                                                                                     2%                4%

                                                                                                 Iterations
 Prob
                             Period Return Probability                                                          Prob                        Period Return Probability
10%                                                                                                 2500        10%


                                                                                               Risk-free rate
                                                                                                  0.36%
 5%                                                                                                             5%




 0%                                                                                                             0%
                                                   0%




                                                               9%
                                             -5%




                                                          4%
                                      -9%




                                                                            18%

                                                                                   22%
        -27%

               -23%




                              -14%




                                                                     13%




                                                                                         27%
                      -18%




                                                                                                                                                     -9%

                                                                                                                                                           -5%

                                                                                                                                                                 0%

                                                                                                                                                                       4%

                                                                                                                                                                             9%




                                                                                                                                                                                          18%

                                                                                                                                                                                                22%

                                                                                                                                                                                                      27%
                                                                                                                                     -18%

                                                                                                                                             -14%




                                                                                                                                                                                   13%
                                                                                                                      -27%

                                                                                                                              -23%
                                       Portfolio Returns                                                                                              Portfolio Returns


           Mean                               Std Dev                      Sharpe Ratio                                  Mean                               Std Dev                      Sharpe Ratio
           0.89%                               9.31%                          0.057                                      1.82%                               8.41%                          0.173
      Probability of achieving 2.0% target return :     45.24%                        Probability of achieving 2.0% target return :     49.13%

                                              Current                 Theoretical Change                                      Optimal
Product
                               Weighting                Units    Weighting             Units                   Weighting                Units
                                            Chart of the Efficient Frontier

                                        Efficient Frontier and Portfolio Set
                  2.00%

                  1.80%

                  1.60%

                  1.40%
Expected return




                  1.20%

                  1.00%

                  0.80%

                  0.60%

                  0.40%

                  0.20%

                  0.00%
                     0.00%      1.00%     2.00%   3.00%   4.00%   5.00%    6.00%   7.00%   8.00%   9.00%
                                                      Standard Deviation


                          Optimal Coordinates: Return = 1.8% and Standard Deviation = 8.4%
Mean           SD
       1.18%        6.79%           0       0.0036
       1.24%        7.26%   0.0841313 0.018158611
                            0.0841313 0.018158611

				
DOCUMENT INFO
Shared By:
Stats:
views:101
posted:4/9/2010
language:English
pages:32
Description: The Portfolio Optimization template calculates the optimal capital weightings for a basket of investments that gives the highest return for the least risk. The unique design of the template enables it to be applied to either financial instrument or business portfolios. The ability to apply optimization analysis to a portfolio of businesses represents an excellent framework for driving capital allocation, investment, and divestment decisions.