Home

Minimze standard deviation of portfolio using Solver - no feasible solution

%3CLINGO-SUB%20id%3D%22lingo-sub-885322%22%20slang%3D%22en-US%22%3EMinimze%20standard%20deviation%20of%20portfolio%20using%20Solver%20-%20no%20feasible%20solution%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-885322%22%20slang%3D%22en-US%22%3E%3CP%20class%3D%22%22%3EHi%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CU%3EMinimize%20standard%20deviation%20of%20portfolio%20with%20104%20assets%20-%20no%20solution%20according%20to%20Solver%3C%2FU%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20hope%20someone%20can%20help%20with%20this%20issue.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20created%20in%20Excel%20a%20portfolio%20of%20104%20stocks%20where%20I%20have%20computed%20the%20historical%20annual%20deviation%20and%20return%20for%20each%20stock.%20Also%2C%20I%20have%20created%20a%20separate%20sheet%20using%20the%20analysis%20toolpack%20to%20create%20a%20matrix%20of%20each%20asset's%20correlation%20coefficient%20with%20one%20another.%20On%20the%20basis%20of%20this%2C%20I%20have%20created%20a%20formula%20computing%20the%20standard%20deviation%20of%20the%20portfolio%20depending%20on%20the%20weight%20of%20each%20asset%20in%20the%20portfolio.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWhen%20I%20use%20Solver%20to%20minimize%20the%20standard%20deviation%20of%20the%20portfolio%20by%20adjusting%20the%20variables%20concerning%20the%20weight%20of%20each%20asset%2C%20it%20says%20that%20there%20is%20no%20feasible%20solution.%20In%20any%20case%2C%20there%20must%20be%20a%20standard%20deviation%20that%20is%20the%20minimum%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20cells%20containing%20the%20weights%20is%20%24J%242%3A%24J%24105%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20formula%20I%20use%20to%20calculate%20the%20standard%20deviation%20of%20the%20portfolio%20is%3A%3CBR%20%2F%3E%3DSQRT(MMULT(MMULT(TRANSPOSE(K2%3AK105)%2Ccor_edit!B2%3ADA105)%2CK2%3AK105))%3C%2FP%3E%3CP%3EWhere%20(K2%3AK105)%20is%20the%20standard%20deviation%20multiplied%20with%20the%20weight.%20The%20adjustable%20variables%20in%20Solver%20is%20not%20set%20to%20this%2C%20but%20the%20column%20with%20the%20weights%20only.%26nbsp%3B%3CBR%20%2F%3Ecor_edit!B2%3ADA105%20is%20the%20matrix%20with%20the%20correlation%20coefficients.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSTRONG%3EMy%20constraints%20are%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3E1)%3CBR%20%2F%3E%24J%24107%20%3D%201%3CBR%20%2F%3EThis%20cell%20is%20the%20sum%20of%20all%20the%20weights%20i.e.%20100%25%20must%20be%20invested.%26nbsp%3B%3CBR%20%2F%3EI%20have%20tried%20setting%20it%20to%20the%20following%20but%20with%20no%20improvement%3A%3CBR%20%2F%3E%24J%24107%20%26lt%3B%3D%201%3CBR%20%2F%3E%24J%24107%20%26gt%3B%3D%200%2C9%3C%2FP%3E%3CP%3E2)%3CBR%20%2F%3E%24J%242%3A%24J%24105%20%26lt%3B%3D%200%2C1%3CBR%20%2F%3Ei.e.%20an%20asset%20cannot%20make%20up%20more%20than%2010%25.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E3)%3C%2FP%3E%3CP%3E%24J%242%3A%24J%24105%20%26gt%3B%3D%200%3C%2FP%3E%3CP%3Ei.e.%20an%20asset%20cannot%20have%20a%20negative%20weight.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20tried%20using%20the%20GRG%20in%20Solver%20with%20multistart%20and%26nbsp%3Bevolutionary.%20In%20evolutionary%20I%20have%20tried%20(in%20addition%20to%20the%20default%20settings)%20with%20the%20following%20settings%3A%3CBR%20%2F%3EConvergence%3A%200%2C1%3CBR%20%2F%3EMutation%20speed%3A%200%2C1%3CBR%20%2F%3EPopulation%20size%3A%20200%3C%2FP%3E%3CP%3EMaximum%20time%3A%20Has%20been%20trying%20with%20as%20high%20as%207200%20(two%20hours).%3C%2FP%3E%3CP%3EI%20have%20checked%20the%20box%20requiring%20bounds%20for%20variables%3C%2FP%3E%3CDIV%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSTRONG%3EWhat%20I%20have%20tried%20so%20far%20to%20make%20it%20work%3A%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3E1)%20I%20have%20enabled%20'Set%20precision%20as%20displayed'%20adjusting%20the%20weights%20to%20two%20decimals%20trying%20to%20reduce%20the%20number%20of%20options%20by%20reducing%20decimals.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E2)%20I%20have%20copy%20and%20pasted%20all%20values%20to%20a%20new%20excel%20file%20only%20inserting%20numbers%20and%20not%20the%20references.%20Now%2C%20I%20only%20have%20the%20relevant%20references%20i.e.%20of%20the%20sum%20of%20all%20weights%2C%20standard%20deviaiton%20multiplied%20with%20weight%2C%20the%20historical%20return%20of%20the%20portfolio%20(weight%20multiplied%20with%20annual%20return)%20and%20of%20course%20the%20formula%20for%20calculating%20the%20standard%20deviation.%20I%20figured%20the%20formulas%20calculating%20the%20standard%20deviation%2C%20return%20etc%20might%20be%20increasing%20the%20workload%20of%20excel.%20As%20these%20are%20not%20to%20be%20adjusted%2C%20the%20formulas%20have%20been%20removed%20in%20these%20cells%20preserving%20only%20the%20actual%20value.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIdeally%2C%20I%20would%20like%20to%20create%20a%20chart%20of%20the%20historical%20return%20of%20the%20portfolio%20depending%20on%20each%20value%20of%20standard%20deviation%20of%20the%20portfolio.%20Do%20you%20have%20any%20suggestions%20of%20how%20I%20can%20find%20the%20minimum%20standard%20deviation%20of%20the%20portfolio%20given%20the%20mentioned%20constraints%3F%3C%2FP%3E%3CP%3EFor%20your%20information%2C%20when%20I%20removed%20the%20constraint%20of%20the%20sum%20being%20equal%20to%201%20or%20within%20a%20certain%20range%2C%20the%20Solver%20worked%20but%20only%20resulting%20in%20a%20sum%20of%2040%25%20which%20I%20would%20like%20to%20be%20100%25.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAny%20help%20will%20be%20much%20appreciated!%3C%2FP%3E%3C%2FDIV%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-885322%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3ESolver%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E
TobbenDK
New Contributor

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!

Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
46 Replies
Extentions Synchronization
Deleted in Discussions on
3 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
29 Replies
Security Community Webinars
Valon_Kolica in Security, Privacy & Compliance on
13 Replies