Exhibit 1 (continued) Exhibit 1 VIRGINIA INVESTMENT PARTNERS OPTIMAL PORTFOLIO ALLOCATION VIRGINIA INVESTMENT PARTNERS OPTIMAL PORTFOLIO ALLOCATION Mean-Variance Optimizer annualized standard deviation was 2.60%. Replace the average return, standard deviation, and correlation numbers for the Lehman Brother Aggregate Bond Index with the numbers above for the Lehman Brothers Mortgage Backed Bond Index and run the optimization again with target standard deviations of 2%,6%, and 10%. What allocations do you get? How could such allocations contribute to our current economic climate? What fundamentally went wrong? b. What parameters could change in order to maximize the return of the portfolio? Typically we assume the returns, standard deviations, and correlations of the asset classes in the optimization are constant and cannot be changed by the investment advisor. What the advisor has control over is the weight in each of the asset classes. In order to let the optimizer know what parameters it can change in order to find the portfolio with the optimal return, click on the By Changing Cells option and then highlight the three weights. c. What constraints should be placed on the optimized portfolio? There are two required constraints on the optimization. First, the weights in the three portfolios should sum to 100% or equivalently 1.00 if you are using fractional notation. To input this constraint, click on the Add button next to the Subject to the Constraints dialog box. Then input the sum of the three weights on the left-hand side and 100% or equivalently 1.00 on the right-hand side. Be sure to change the sign to an = sign. The second constraint is to set the portfolio standard deviation equal to the target standard deviation. To set this constraint, first type in the desired standard deviation in the portfolio target standard deviation cell. Then repeat the steps above and set the portfolio annualized standard deviation equal to the target standard deviation. Additional constraints on the portfolio weights or on other aspects of the optimization may also be input. 4. Repeat the exercise in 3 for target standard deviations of 2%,6%,14%, and 20%. To do this, just change the value in the portfolio target standard deviation cell and rerun Solver. Are all of these feasible portfolios? What additional assets or investment strategies would you need to reach these targets on the investment frontier? What impact would including these additional assets or investment strategies have the risk and return of the portfolio? Do these portfolios suggest additional constraints that should be placed on the optimization? 5. Plot the portfolio return and standard deviation for each of the targets above and compare this graphically to the mean and standard deviation of the client's current portfolio. Is this a compelling argument for diversification? 6. Given the current economic climate, the chief economist at VIP has suggested that the expected returns for U.S. and international equities be lowered to 5% and 6.5% respectively. He has also suggested that the correlation between the two should be increased to 0.9 . Using these numbers, change the optimizer parameters and repeat the optimization for a target of 10%. How does this change your portfolio? 7. The Lehman Brothers Mortgage Backed Bond Index has a correlation of -0.1024 and -0.06618 with the MSCI World (excluding the United States) and the S\&P 500 indices respectively. The average annualized return for the index was 6.03% and the average Exhibit 1 (continued) \begin{tabular}{|c|c|c|c|c|} \hline Date & S\&P 500 & MSCIWorldIndex(excludingU.S.) & LehmanBrothersAggregateBondIndex & IBM \\ \hline Jun-02 & 7.12% & 4.06% & 0.87% & 10.50% \\ \hline Jul-02 & 7.79% & 9.94% & 1.21% & 2.22% \\ \hline Aug-02 & 0.65% & 0.15% & 1.69% & 7.29% \\ \hline Sep-02 & 10.80% & 10.60% & 1.62% & 22.65% \\ \hline Oct-02 & 8.79% & 5.25% & 0.46% & 35.38% \\ \hline Nov-02 & 5.88% & 4.60% & 0.03% & 10.30% \\ \hline Dec-02 & 5.87% & 3.23% & 2.07% & 10.84% \\ \hline Jan-03 & 2.62% & 3.81% & 0.09% & 0.90% \\ \hline Feb-03 & 1.50% & 1.97% & 1.38% & 0.13% \\ \hline Mar-03 & 0.97% & 1.93% & 0.08% & 0.62% \\ \hline Apr-03 & 8.24% & 9.60% & 0.83% & 8.25% \\ \hline May-03 & 5.27% & 6.20% & 1.86% & 3.89% \\ \hline Jun-03 & 1.28% & 2.43% & 0.20% & 6.29% \\ \hline Jul-03 & 1.76% & 2.29% & 3.36% & 1.52% \\ \hline Aug-03 & 1.95% & 2.58% & 0.66% & 1.13% \\ \hline Sep-03 & 1.06% & 3.00% & 2.65% & 7.71% \\ \hline Oct-03 & 5.66% & 6.26% & 0.93% & 1.30% \\ \hline Nov-03 & 0.88% & 2.27% & 0.24% & 1.36% \\ \hline Dec-03 & 5.24% & 7.67% & 1.02% & 2.36% \\ \hline Jan-04 & 1.84% & 1.39% & 0.80% & 7.07% \\ \hline Feb-04 & 1.39% & 2.30% & 1.08% & 2.59% \\ \hline Mar-04 & 1.51% & 0.50% & 0.75% & 4.83% \\ \hline Apr-04 & 1.57% & 2.62% & 2.60% & 4.00% \\ \hline May-04 & 1.37% & 0.30% & 0.40% & 0.68% \\ \hline Jun-04 & 1.94% & 2.48% & 0.57% & 0.50% \\ \hline Jul-04 & 3.31% & 3.05% & 0.99% & 1.23% \\ \hline Aug-04 & 0.40% & 0.42% & 1.91% & 2.53% \\ \hline Sep-04 & 1.08% & 2.91% & 0.27% & 1.24% \\ \hline Oct-04 & 1.53% & 3.59% & 0.84% & 4.68% \\ \hline Nov-04 & 4.05% & 6.65% & 0.80% & 5.20% \\ \hline Dec-04 & 3.40% & 4.22% & 0.92% & 4.61% \\ \hline Jan-05 & 2.44% & 1.97% & 0.63% & 5.23% \\ \hline Feb-05 & 2.10% & 4.45% & 0.59% & 0.71% \\ \hline Mar-05 & 1.77% & 2.27% & 0.51% & 1.30% \\ \hline \end{tabular} Gazing out your office window at the Blue Ridge Mountains, you ponder how to proceed with your first client. You have recently joined Virginia Investment Partners (VIP) in Charlottesville, VA, and your first assignment is to suggest an investment plan for a prospective client. The client is an IBM executive whose portfolio consists entirely of his company's stock. VIP typically suggests an overall investment plan that allocates the client's assets across three broad classes: U.S. domestic equity as proxied by the S\&P 500 Index, U.S. fixed income as proxied by the Lehman Brothers Aggregate Bond Index, and foreign equity as proxied by the MSCI World Index (excluding the United States). To prepare the investment plan, you have access to 10 years of monthly return data for three asset classes and IBM1 (Exhibit 1) as well as the company's proprietary mean-variance optimizer (Exhibit 2). 1. Using the return data for IBM in Exhibit 1, calculate the mean and standard deviation for the stock. 2. Assume a portfolio with three equally weighted asset classes. Using the return data in Exhibit 1, calculate the monthly return for this portfolio from January 1997 to December 2006. The formula for this return will simply be the weighted average return (i.e., RPortfolio = Weight SP500RSP500+ Weight MSCIRMSCI+ Weight LehmanAggRLehmanAgg. From these returns, calculate the mean and standard deviation of the portfolio. What arguments could be made for the client to sell IBM and diversify into such a portfolio? What arguments could be made against it? 3. Using the mean-variance optimizer in Exhibit 2, calculate the weights in the three asset classes (Domestic Equity as proxied by the S\&P 500, International Equity as proxied by the MSCI World Index, and U.S. Fixed Income as proxied by the Lehman Brothers Aggregate Bond Index) for the optimal portfolio when the target standard deviation is 10%. In this exercise, the optimal portfolio is the portfolio with the maximum return for a given level of risk as proxied by the standard deviation. To calculate the weights for this optimal portfolio, follow the instructions in the Appendix on enabling and using Solver. Once you have the Solver dialog box on the screen, you need to identify the following: 1 The return calculated is a holding-period return where all dividends and distributions are assumed to be reinvested at NAV. Exhibit 1 (continued) \begin{tabular}{|c|c|c|c|c|} \hline Date & S\&P 500 & MSCIWorldIndex(excludingU.S.) & LehmanBrothersAggregateBondIndex & IBM \\ \hline Aug-99 & 0.49% & 0.28% & 0.05% & 0.80% \\ \hline Sep-99 & 2.74% & 1.08% & 1.16% & 2.86% \\ \hline Oct-99 & 6.33% & 3.86% & 0.37% & 18.80% \\ \hline Nov-99 & 2.03% & 3.50% & 0.00% & 5.02% \\ \hline Dec-99 & 5.89% & 9.19% & 0.48% & 4.67% \\ \hline Jan-00 & 5.02% & 6.07% & 0.33% & 4.06% \\ \hline Feb-00 & 1.89% & 2.83% & 1.21% & 8.36% \\ \hline Mar-00 & 9.78% & 4.10% & 1.32% & 14.84% \\ \hline Apr-00 & 3.01% & 5.18% & 0.29% & 5.51% \\ \hline May-00 & 2.05% & 2.42% & 0.05% & 3.58% \\ \hline Jun-00 & 2.47% & 4.32% & 2.08% & 2.04% \\ \hline Jul-00 & 1.56% & 3.82% & 0.91% & 2.62% \\ \hline Aug-00 & 6.21% & 1.31% & 1.45% & 17.51% \\ \hline Sep-00 & 5.28% & 5.18% & 0.63% & 14.77% \\ \hline Oct-00 & 0.42% & 2.72% & 0.66% & 12.44% \\ \hline Nov-00 & 7.88% & 4.02% & 1.64% & 4.94% \\ \hline Dec-00 & 0.49% & 3.50% & 1.86% & 9.09% \\ \hline Jan-01 & 3.55% & 0.18% & 1.64% & 31.76% \\ \hline Feb-01 & 9.11% & 7.92% & 0.87% & 10.69% \\ \hline Mar-01 & 6.33% & 6.80% & 0.50% & 3.72% \\ \hline Apr-01 & 7.76% & 6.97% & 0.41% & 19.71% \\ \hline May-01 & 0.67% & 3.28% & 0.60% & 2.78% \\ \hline Jun-01 & 2.43% & 4.10% & 0.38% & 1.07% \\ \hline Jul-01 & 0.99% & 1.72% & 2.24% & 6.89% \\ \hline Aug-01 & 6.25% & 2.67% & 1.15% & 4.87% \\ \hline Sep-01 & 8.07% & 10.09% & 1.17% & 8.23% \\ \hline Oct-01 & 1.91% & 2.45% & 2.09% & 17.83% \\ \hline Nov-01 & 7.67% & 3.93% & 1.37% & 7.09% \\ \hline Dec-01 & 0.88% & 0.64% & 0.63% & 4.65% \\ \hline Jan-02 & 1.45% & 5.08% & 0.81% & 10.81% \\ \hline Feb-02 & 1.92% & 0.61% & 0.97% & 8.93% \\ \hline Mar-02 & 3.76% & 5.78% & 1.66% & 5.99% \\ \hline Apr-02 & 6.06% & 0.15% & 1.94% & 19.46% \\ \hline May-02 & 0.73% & 1.31% & 0.85% & 3.77% \\ \hline \end{tabular}