This Solver model uses the QUADPRODUCT function at cell I14 to compute the portfolio variance. It can be solved for the minimum variance using either the GRG nonlinear solver or the Quadratic Solver. Stock 1 Stock 2 Stock 3 Stock 4 Stock 5 Total Portfolio % 20.00% 20.00% 20.00% 20.00% 20.00% 100.00% Expected Return 7.00% 8.00% 9.50% 6.50% 14.00% Linear QP Terms 0 0 0 0 0 Variance/Covariance Matrix Stock 1 Stock 2 Stock 3 Stock 4 Stock 5 Stock 1 2.50% 0.10% 1.00% -0.50% 1.60% Stock 2 0.10% 1.10% -0.10% 1.20% -0.85% Stock 3 1.00% -0.10% 1.20% 0.65% 0.75% Variance 0.68% Stock 4 -0.50% 1.20% 0.65% 0.40% 1.00% Std. Dev. 8.22% Stock 5 1.60% -0.85% 0.75% 1.00% 2.00% Return 9.00% Problem An investor wants to put together a portfolio consisting of up to 5 stocks. Using the Markowitz method, what is the best combination of stocks to minimize risk for a given return? The variances are known for each stock, as are the covariances between all stocks. The returns for all stocks are also known. Solution 1) The variables are the percentage allocations of our funds to invest in each stock. In this worksheet, the variables are given the name Allocations. The sum of the allocations (which must be 100%) is computed in the cell named Total_Portfolio. 2) The constraints are very simple. First there are the logical constraints: Allocations >= 0 via the Assume Non-Negative option Total_Portfolio = 1 Then there is a constraint that the portfolio return should be at least a certain target value (9% in this example). This return is calculated in the cell named Portfolio_return: Portfolio_Return >= 0.09 3) The objective is to minimize portfolio variance, which is calculated according to the Markowitz method in the cell named Portfolio_Variance. Remarks In this worksheet, we use the QUADPRODUCT function to compute the portfolio variance. If you see #NAME? on this worksheet, you need to open the add-in (DOTPRD32.XLL or DOTPROD.XLL) that provides QUADPRODUCT. In the Full Markowitz worksheet, we calculate the portfolio variance 'manually' without using QUADPRODUCT. The Markowitz method can only be used if all the variances of individual stocks, and the covariances between each pair of stocks are known. In this model we assumed that all the terms are given. In the Full Markowitz worksheet we actually calculate the variances and covariances from a history of stock prices.