Question
8.6 Reconsider the portfolio selection example, including its spreadsheet model in Figure 8.13, given in Section 8.2. Note in Table 8.2 that Stock 2 has
8.6 Reconsider the portfolio selection example, including its spreadsheet model in Figure 8.13, given in Section 8.2. Note in Table 8.2 that Stock 2 has the highest expected return and stock 3 has by far the lowest. Nevertheless, the changing cells Portfolio (C14:E14) provide an optimal solution that calls for purchasing far more of Stock 3 than of Stock 2. Although purchasing so much of Stock 3 greatly reduces the risk of the portfolio, an aggressive investor may be unwilling to own so much of a stock with such a low expected return.
For the sake of such an investor, add a constraint to the model that specifies that the percentage of Stock 3 in the portfolio can- not exceed the amount specified by the investor. Then compare the expected return and risk (standard deviation of the return) of the optimal portfolio with that in Figure 8.13 when the upper bound on the percentage of Stock 3 allowed in the portfolio is set at the following values.
a)20%
b)0%
c)Generate a parameter analysis report using RSPE to systematically try all the percentages at 5% intervals from 0% to 50%.
| Portfolio Selection Problem (Nonlinear Programming)
| |||||||
|
| Stock 1 | Stock 2 | Stock 3 |
|
|
| |
| Expected Return | 21% | 30% | 8% |
|
|
| |
|
|
|
|
|
|
|
| |
| Risk (Stand. Dev.) | 25% | 45% | 5% |
|
|
| |
|
|
|
|
|
|
|
| |
| Joint Risk (Covar.) | Stock 1 | Stock 2 | Stock 3 |
|
|
| |
| Stock 1 |
| 0.040 | -0.005 |
|
|
| |
| Stock 2 |
|
| -0.010 |
|
|
| |
| Stock 3 |
|
|
|
|
|
| |
|
| Stock 1 | Stock 2 | Stock 3 | Total |
|
| |
| Portfolio | 40.2% | 21.7% | 38.1% | 100% =SUM(Portfolio)
| = | 100% | |
|
|
|
|
|
|
|
| |
|
| Portfolio |
| Minimum expected returns |
|
|
| |
| Expected return | 18% =SUMPRODUCT(StockExpectedReturn,Portfolio)
| => | 18% |
|
|
| |
|
|
|
|
|
|
|
| |
| Risk (variance) | 0.0238 =((SD1*Stock1)^2)+((SD2*Stock2)^2)+((SD3*Stock3)^2)+2*Covar12*Stock1*Stock2+2*Covar13*Stock1*Stock3+2*Covar23*Stock2*Stock3 |
|
|
|
|
| |
|
|
|
|
|
|
|
| |
| Risk (stand. Dev.) | 15.4%
=SQRT(Variance) |
|
|
|
|
| |
| Range Name Cells Covar12 D9 Covar13 E9 Covar23 E10 Covariance C9:E11 ExpectedReturn C19 MinExpectedReturn E19 OneHundredPercent H14 Portfolio C14:E14 SD1 C6 SD2 D6 SD3 E6 StandDev C23 Stock1 C14 Stock2 D14 Stock3 E14 StockExpectedReturn C4:E4 StockStandDev C6:E6 Total F14 Variance C21
| Solver Paramenters Set Objective Cell: Variance To: Min By Changing Variable Cells: Portfolio Subject to the Constraints: ExpectedReturn >= MinExpectedReturn Total = OneHundredPercent
Solver Options: Make Variables Nonnegative Solving Method: GRG Nonlinear or Quadratic (RSPE)
| ||||||
Step by Step Solution
There are 3 Steps involved in it
Step: 1
Get Instant Access with AI-Powered Solutions
See step-by-step solutions with expert insights and AI powered tools for academic success
Step: 2
Step: 3
Ace Your Homework with AI
Get the answers you need in no time with our AI-driven, step-by-step assistance
Get Started