Excel solver question

J

JohnThainsLimoDriver

New member
Joined
Jun 18, 2026
Messages
0
Reaction score
0
I’m running some optimizations on excel using solver and ran into a “glitch” that I can’t figure out how to correct. I have a portfolio of 30 securities and I am trying to separately optimize for returns, sharpe, and (minimal) volatility. The wacky thing is that, for example, when I find the optimal volatility portfolio, the sharpe ratio of that portfolio happens to be higher than the sharpe for the supposed optimal sharpe portfolio. So that means the optimal sharpe portfolio wasn’t really optimal. I suspect that solver got stuck at a local maximum during the sharpe optimization and assumed it was the correct answer. I tried to increase the number of iterations, fluctuate the initial weightings guesses for each security, etc., but the solver still can’t come up with a solution that matches or exceeds the sharpe I derived from the volatility optimization. Any suggestions on how to fix this issue?
 
The Excel solver sometimes solve locally.
As far as I know and have experienced it’s quite difficult to solve that problem. Ýou already tried well by using new guesses and number of iterations.
Try to avoid using the Excel solver by making your own i vba, although it’s difficult when solving for more than one variable. When only 1 variable use taylor appr ->50 times faster…
Have in mind that you might have made a mistake like forgotten a constraint
Good luck
 
I don’t think the problem is finding a local maximum instead of a global one. Sharpe is a very nice convex function. I can think of a few potential problems:
1) Sharpe is very flat at the maximum, the optimizer just can’t get there.
2) you don’t use enough data (sorry if i’m offending you, but that happens all the time). For example, if you use 20 datapoints for each security and run optimizations, that’s not a good idea.
3) the problem is mis-specified or not set up correctly. Sharpe of min variance portfolio shouldn’t be the optimal one.
If you want, send me an email at [email protected] and I will take a look at your file.
 
My question is did you set this all up yourself? It sounds jolly clever. I have done it for 3 portfolio assets (thanks Benninga), but have yet managed to ramp it up. Is it all done in standard excel or is it in VBA? Do you have somewhere to drop in return data and it automatically calculates the rest? You have all the covariances to calculate before you get onto the optimisation and calc the efficient frontier. I’m just wondering how big this monster is…
 
Muddahudda, it’s not difficult, no VBA knowledge is necessary, but you need to be familiar with two things:
1) excel solver (you can probably google it, read introduction and install the Add-In)
2) get familiar with matrix treatment in Excel (mmult, transpose and maybe minverse).
Assuming you use the same number of securities and the same number of datapoints for each, you can set up a template with Covariance Matrix calculation, constraints, etc as well as set up the problem in solver. Then as you can update the data (if need be) and use Tools/Solver to solve the problem.
 
Thanks martikus. Familiar with the solver. Less so with matrix, unless you simply mean =covar(A1:A12,B1:B12). I probably need to give it more time to go past 3 assets, and build it myself. However, at the moment it is a weekend hobby to go through the Benninga stuff and it only goes up to 3 asset portfolio - it does suggest getting the financial modelling book for more assets. I seem to recall that getting weights in the 3 asset portfolio was a bit clunky/maybe even manual from the top of my head. Would be easier to see a spreadsheet with the end product I guess. I’m not a natural at building stuff in excel.
 
I had a big problem with solver. it is linear modeling so you need to use liner functions….if you have the function “if”, it would bring you a misleading answer because it is not linear. The same happen if it is curve..
 
It would be misleading because there are a lot of squares….it is not linear!!
 
pierovic, I don’t think you are correct about the limitation of solving linear problems only. From what I remember Solver allows using SQP (sequential quadratic programming) algorithm that works for non-linear problems.
 
You can be right. I got a problem with the function “if”. “if” is a point that takes two ways. The error arose becasue “IF” is not linear. My conclusion is that solver only takes linear equations.
 
You must log in or register to reply here.
Back
Top