Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

- Home
- Microsoft Excel
- Excel
- Minimze standard deviation of portfolio using Solver - no feasible solution

- Subscribe to RSS Feed
- Mark Discussion as New
- Mark Discussion as Read
- Pin this Discussion for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Oct 01 2019 01:59 AM - edited Oct 02 2019 05:27 AM

Hi,

__Minimize standard deviation of portfolio with 104 assets - no solution according to Solver__

I hope someone can help with this issue.

I have created in Excel a portfolio of 104 stocks where I have computed the historical annual deviation and return for each stock. Also, I have created a separate sheet using the analysis toolpack to create a matrix of each asset's correlation coefficient with one another. On the basis of this, I have created a formula computing the standard deviation of the portfolio depending on the weight of each asset in the portfolio.

When I use Solver to minimize the standard deviation of the portfolio by adjusting the variables concerning the weight of each asset, it says that there is no feasible solution. In any case, there must be a standard deviation that is the minimum?

The cells containing the weights is $J$2:$J$105

The formula I use to calculate the standard deviation of the portfolio is:

=SQRT(MMULT(MMULT(TRANSPOSE(K2:K105),cor_edit!B2:DA105),K2:K105))

Where (K2:K105) is the standard deviation multiplied with the weight. The adjustable variables in Solver is not set to this, but the column with the weights only.

cor_edit!B2:DA105 is the matrix with the correlation coefficients.

**My constraints are**

1)

$J$107 = 1

This cell is the sum of all the weights i.e. 100% must be invested.

I have tried setting it to the following but with no improvement:

$J$107 <= 1

$J$107 >= 0,9

2)

$J$2:$J$105 <= 0,1

i.e. an asset cannot make up more than 10%.

3)

$J$2:$J$105 >= 0

i.e. an asset cannot have a negative weight.

I have tried using the GRG in Solver with multistart and evolutionary. In evolutionary I have tried (in addition to the default settings) with the following settings:

Convergence: 0,1

Mutation speed: 0,1

Population size: 200

Maximum time: Has been trying with as high as 7200 (two hours).

I have checked the box requiring bounds for variables

**What I have tried so far to make it work:**

1) I have enabled 'Set precision as displayed' adjusting the weights to two decimals trying to reduce the number of options by reducing decimals.

2) I have copy and pasted all values to a new excel file only inserting numbers and not the references. Now, I only have the relevant references i.e. of the sum of all weights, standard deviaiton multiplied with weight, the historical return of the portfolio (weight multiplied with annual return) and of course the formula for calculating the standard deviation. I figured the formulas calculating the standard deviation, return etc might be increasing the workload of excel. As these are not to be adjusted, the formulas have been removed in these cells preserving only the actual value.

Ideally, I would like to create a chart of the historical return of the portfolio depending on each value of standard deviation of the portfolio. Do you have any suggestions of how I can find the minimum standard deviation of the portfolio given the mentioned constraints?

For your information, when I removed the constraint of the sum being equal to 1 or within a certain range, the Solver worked but only resulting in a sum of 40% which I would like to be 100%.

Any help will be much appreciated!

0 Replies