Portfolio Optimization - Markowitz Method |
|
|
|
|
|
| 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. |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
