Answered step by step
Verified Expert Solution
Link Copied!

Question

1 Approved Answer

Looking for steps on how to do this in Excel, given 5-year daily prices for a portfolio of 85 stocks as shown in the sample

Looking for steps on how to do this in Excel, given 5-year daily prices for a portfolio of 85 stocks as shown in the sample screenshot:

Optimize a portfolio for the following situations by assigning weights to each of the 85 stocks available:

  • Maximum return
  • Minimum volatility
  • Return equal to that of the S&P 500 with lowest volatility

Shorting is not allowed. All weights must be between 0 and 1 and sum to 1. Your final answer to this problem should be three separate lists of weights for each stock, as well as the return and volatility expected for each of the three portfolios.

image text in transcribed

LY 1/2/15 1/5/15 1/6/15 1/7/15 1/8/15 1/9/15 1/12/15 1/13/15 1/14/15 1/15/15 1/16/15 1/20/15 1/21/15 1/22/15 1/23/15 1/26/15 1/27/15 1/28/15 1/29/15 1/30/15 2/2/15 2/3/15 2/4/15 2/5/15 2/6/15 2/9/15 2/10/15 2/11/15 2/12/15 2/13/15 2/17/15 2/18/15 AAP AAPL AAXN ABBV ABMD ABT ACN ADBE AFL AKAM ALB ALK AMD 38.65205 157.236282 100.4543 26.51 53.263485 37.310001 40.318245 79.861984 72.339996 25.132326 63.25 55.427227 55.299713 37.927803 155.163757 97.624336 25.690001 52.261108 37.07 40.327221 78.513565 71.980003 24.473976 61.939999 53.589149 54.511414 37.336964 155.054657 97.633545 25.57 52.002438 36.130001 39.869267 77.947235 70.529999 24.255899 60.669998 52.541447 53.869781 37.832508 158.386612 99.002556 25.98 54.104195 37.279999 40.192528 79.583305 71.110001 24.408142 60.16 52.330059 54.694733 38.966534 159.774918 102.80648 26.629999 54.670063 38.959999 41.018654 80.796875 72.919998 24.646797 61.57 53.975147 55.638863 38.680637 158.961792 102.916725 26.27 53.174572 38.41 40.587631 80.706978 71.839996 24.124231 60.779999 54.241669 54.749741 38.223221 157.603226 100.380791 2 5.84 53.158409 38.560001 40.928848 80.464272 71.120003 23.984333 60.75 54.866608 55.556358 37.689556 152.476379 101.272049 25.719999 51.627258 38.810001 40.269886 80.75193 70.589996 23.865009 60.790001 56.052174 56.28965 37.222607 149.570847 100.886154 25.91 51.879734 38.25 39.971981 79.808044 69.989998 23.679848 59.709999 54.701183 56.408813 36.222012 146.784271 98.148056 25.23 51.220039 37.110001 39.674084 79.259689 70.290001 23.601671 58.630001 53.377769 56.344658 36.45071 148.747742 97.385445 25.360001 52.563866 37.919998 40.161549 79.906914 71.730003 23.939075 59.220001 53.782143 57.151283 36.145771 151.385574 99.893822 2 6.91 51.676132 37.869999 39.917816 79.664215 71.489998 23.869125 59.650002 53.120445 59.21368 36.364948 152.069778 100.656441 26.799999 5 1.0653 37.810001 39.773392 79.412506 71.540001 23.906153 60.27 53.864864 59.561996 37.784855 155.2332 103.275063 27.440001 51.293339 38.939999 40.161549 80.455284 73.480003 24.338196 60.639999 54.72876 62.275192 36.984375 154.479523 103.807991 27.610001 50.845402 39.48 39.59285 80.203583 74.059998 24.165379 6 1.02 52.238171 62.926014 37.308372 157.900726 103.918228 27.889999 51.171177 39.57 39.854633 80.077721 72.739998 24.284708 61.169998 52.513866 64.566727 36.927181 157.702362 100.279716 27.690001 51.391079 38.630001 39.430359 77.45282 71.459999 24.074852 60.290001 51.806217 63.888428 36.212467 157.970123 105.948822 27.280001 50.37302 49.939999 39.186626 75.834717 70.25 23.601671 58.990002 50.887177 62.971821 36.650833 158.922119 109.247391 27.809999 51.40736 52.25 40.856644 76.985367 71.639999 23.720999 59.950001 43.902481 65.144196 35.993286 157.672638 107.648643 27.01 49.151375 51.740002 40.405277 75.538071 70.129997 23.486464 58.16 44.352806 62.211033 36.870018 154.667953 108.999298 26.9 49.436417 51.860001 40.829559 77.156174 71.110001 23.934961 58.490002 44.040333 61.70689 37.756264 155.758743 109.017654 25.799999 50.210144 52.889999 41.371185 79.03495 71.919998 24.313509 60.119999 45.382137 59.745316 37.27026 155.163757 109.853806 26.42 46.349697 54.150002 40.775394 78.513565 72.279999 24.967741 59.950001 44.895039 61.13858 37.670502 158.822952 110.637886 26.98 47.229294 55.52 41.208698 79.529373 73.099998 25.091177 60.490002 45.722176 61.248573 37.489437 160.647598 109.7062 26.639999 46.34156 57.939999 41.073296 79.241714 72.540001 25.214617 61.130001 46.9445 60.423622 37.203545 159.398117 110.434944 26.280001 45.185051 56.810001 40.188633 78.53154 72.330002 25.157013 60.959999 47.918682 58.214558 37.803917 161.540039 112.556557 26.58 46.333412 57.689999 40.549717 78.675362 72.760002 25.284567 62.209999 47.946255 59.919479 37.479912 161.034302 115.194748 26.27 46.170525 58.900002 40.558746 79.070908 73.010002 25.440924 63.75 48.874485 59.13118 38.137451 150.155945 116.652199 27.379999 46.463722 59.93 40.82053 80.086708 74.589996 25.753466 67.589996 49.591335 57.517937 38.261333 148.906448 117.224121 26.959999 47.278164 60.389999 41.605885 80.715981 76.510002 25.890985 68.769997 50.170322 57.343769 38.613934 148.97583 117.915962 26.870001 47.82383 60.619999 41.894756 80.275482 77.129997 25.982664 70.360001 50.354134 57.041286 39.585953 151.940887 118.736946 26.99 48.247341 60.57 41.894756 80.392357 77.730003 25.732626 70.290001 50.418453 58.507885 2.67 2.66 2.63 2.58 2.61 2.63 2.63 2.66 2.63 2.52 2.39 2.24 2.45 2.47 2.45 2.61 2.7 2.63 2.61 2.57 2.67 2.76 2.85 3.31 3.03 3.04 3.04 3.07 3.12 3.15 3.11 3.08 LY 1/2/15 1/5/15 1/6/15 1/7/15 1/8/15 1/9/15 1/12/15 1/13/15 1/14/15 1/15/15 1/16/15 1/20/15 1/21/15 1/22/15 1/23/15 1/26/15 1/27/15 1/28/15 1/29/15 1/30/15 2/2/15 2/3/15 2/4/15 2/5/15 2/6/15 2/9/15 2/10/15 2/11/15 2/12/15 2/13/15 2/17/15 2/18/15 AAP AAPL AAXN ABBV ABMD ABT ACN ADBE AFL AKAM ALB ALK AMD 38.65205 157.236282 100.4543 26.51 53.263485 37.310001 40.318245 79.861984 72.339996 25.132326 63.25 55.427227 55.299713 37.927803 155.163757 97.624336 25.690001 52.261108 37.07 40.327221 78.513565 71.980003 24.473976 61.939999 53.589149 54.511414 37.336964 155.054657 97.633545 25.57 52.002438 36.130001 39.869267 77.947235 70.529999 24.255899 60.669998 52.541447 53.869781 37.832508 158.386612 99.002556 25.98 54.104195 37.279999 40.192528 79.583305 71.110001 24.408142 60.16 52.330059 54.694733 38.966534 159.774918 102.80648 26.629999 54.670063 38.959999 41.018654 80.796875 72.919998 24.646797 61.57 53.975147 55.638863 38.680637 158.961792 102.916725 26.27 53.174572 38.41 40.587631 80.706978 71.839996 24.124231 60.779999 54.241669 54.749741 38.223221 157.603226 100.380791 2 5.84 53.158409 38.560001 40.928848 80.464272 71.120003 23.984333 60.75 54.866608 55.556358 37.689556 152.476379 101.272049 25.719999 51.627258 38.810001 40.269886 80.75193 70.589996 23.865009 60.790001 56.052174 56.28965 37.222607 149.570847 100.886154 25.91 51.879734 38.25 39.971981 79.808044 69.989998 23.679848 59.709999 54.701183 56.408813 36.222012 146.784271 98.148056 25.23 51.220039 37.110001 39.674084 79.259689 70.290001 23.601671 58.630001 53.377769 56.344658 36.45071 148.747742 97.385445 25.360001 52.563866 37.919998 40.161549 79.906914 71.730003 23.939075 59.220001 53.782143 57.151283 36.145771 151.385574 99.893822 2 6.91 51.676132 37.869999 39.917816 79.664215 71.489998 23.869125 59.650002 53.120445 59.21368 36.364948 152.069778 100.656441 26.799999 5 1.0653 37.810001 39.773392 79.412506 71.540001 23.906153 60.27 53.864864 59.561996 37.784855 155.2332 103.275063 27.440001 51.293339 38.939999 40.161549 80.455284 73.480003 24.338196 60.639999 54.72876 62.275192 36.984375 154.479523 103.807991 27.610001 50.845402 39.48 39.59285 80.203583 74.059998 24.165379 6 1.02 52.238171 62.926014 37.308372 157.900726 103.918228 27.889999 51.171177 39.57 39.854633 80.077721 72.739998 24.284708 61.169998 52.513866 64.566727 36.927181 157.702362 100.279716 27.690001 51.391079 38.630001 39.430359 77.45282 71.459999 24.074852 60.290001 51.806217 63.888428 36.212467 157.970123 105.948822 27.280001 50.37302 49.939999 39.186626 75.834717 70.25 23.601671 58.990002 50.887177 62.971821 36.650833 158.922119 109.247391 27.809999 51.40736 52.25 40.856644 76.985367 71.639999 23.720999 59.950001 43.902481 65.144196 35.993286 157.672638 107.648643 27.01 49.151375 51.740002 40.405277 75.538071 70.129997 23.486464 58.16 44.352806 62.211033 36.870018 154.667953 108.999298 26.9 49.436417 51.860001 40.829559 77.156174 71.110001 23.934961 58.490002 44.040333 61.70689 37.756264 155.758743 109.017654 25.799999 50.210144 52.889999 41.371185 79.03495 71.919998 24.313509 60.119999 45.382137 59.745316 37.27026 155.163757 109.853806 26.42 46.349697 54.150002 40.775394 78.513565 72.279999 24.967741 59.950001 44.895039 61.13858 37.670502 158.822952 110.637886 26.98 47.229294 55.52 41.208698 79.529373 73.099998 25.091177 60.490002 45.722176 61.248573 37.489437 160.647598 109.7062 26.639999 46.34156 57.939999 41.073296 79.241714 72.540001 25.214617 61.130001 46.9445 60.423622 37.203545 159.398117 110.434944 26.280001 45.185051 56.810001 40.188633 78.53154 72.330002 25.157013 60.959999 47.918682 58.214558 37.803917 161.540039 112.556557 26.58 46.333412 57.689999 40.549717 78.675362 72.760002 25.284567 62.209999 47.946255 59.919479 37.479912 161.034302 115.194748 26.27 46.170525 58.900002 40.558746 79.070908 73.010002 25.440924 63.75 48.874485 59.13118 38.137451 150.155945 116.652199 27.379999 46.463722 59.93 40.82053 80.086708 74.589996 25.753466 67.589996 49.591335 57.517937 38.261333 148.906448 117.224121 26.959999 47.278164 60.389999 41.605885 80.715981 76.510002 25.890985 68.769997 50.170322 57.343769 38.613934 148.97583 117.915962 26.870001 47.82383 60.619999 41.894756 80.275482 77.129997 25.982664 70.360001 50.354134 57.041286 39.585953 151.940887 118.736946 26.99 48.247341 60.57 41.894756 80.392357 77.730003 25.732626 70.290001 50.418453 58.507885 2.67 2.66 2.63 2.58 2.61 2.63 2.63 2.66 2.63 2.52 2.39 2.24 2.45 2.47 2.45 2.61 2.7 2.63 2.61 2.57 2.67 2.76 2.85 3.31 3.03 3.04 3.04 3.07 3.12 3.15 3.11 3.08

Step by Step Solution

There are 3 Steps involved in it

Step: 1

blur-text-image

Get Instant Access with AI-Powered Solutions

See step-by-step solutions with expert insights and AI powered tools for academic success

Step: 2

blur-text-image

Step: 3

blur-text-image

Ace Your Homework with AI

Get the answers you need in no time with our AI-driven, step-by-step assistance

Get Started

Students also viewed these Finance questions

Question

=+b) Compute the SD for each decision.

Answered: 1 week ago