Problems using solver (advanced) (for portfolio maximization)

Hi ppl, I am new to this forum, and I was hoping some Excel pro could help me with a problem using solver:

Basically I am using the SAME data set and the SAME objective cell and SAME constraints, but yet I can get HUGELY DIFFERENT results each time. Sometimes I get the message "objective cell values do not converge", other times I just get a stupid result, and sometimes I get a realistic result.

I even have two sheets with almost identical numbers, and identical formulas, which give me vbery different objective cell value results using solver.

I know the formulas are fine becaus eI have recieved the the correct answer with the same formulas nad very similar data. However I would like to know for future reference, how is this kind of thing possible? does anyone have experience.

PS this has happened on Excel 2010 and 2007.

PPS my options are fine, also I did it both accepting and not accepting nt accepting negative values for non-constrained variables (the assignment requires both options).

Thank you so much to any expert who can help.

Here are the formulas I was using:

Sharpe Ratio: = (Rp-Rf)/SD
Rp = SUMPRODUCT B1:K1, B4:K4
Var = MMULT(MMULT(B4:K4, B10:K20),L10:L20
SD = Var^(1/2)
Sum of Weights = SUM B4:K4
Rf = x (changeable)

SOLVER:

Objective: Maximize Sharpe Ratio

By changing cells: Weights (B4:K4)

Constraints:

Sum of Weights = 1
SD >= 0


NB I did not store the unfeasible results for obvious reasons. However I know these formulas are correct from other examples and as they gave me the correct answers eventually.

So can anybody see any logical problem why these values would not always revert to the same result, when I chose to Maximize Sharpe Ratio.

I haven't looked at your specific model in detail, but the usual reason you get different answers is because there are multiple solutions for the equation you've given. Most numerical methods texts illustrate this using polynomials, because they are mathematically simple to work with. When finding the roots (values for x where y = 0) for polynomials using the Newton Raphson method (the usual default option used by Solver), you can readily see that the method will converge on any of the roots depending mostly on what you give the problem for the initial guess for the problem. Your problem is going to be mathematically more complex, but it is conceptually the same idea - there are multiple roots to the equation, and the root it will converge on will depend on what it is given for the initial guesses.

If you aren't familiar with this kind of root finding problem using a simple polynomial, it might be worth a little time to familiarize yourself with basic root finding algorithms so you can see how they work.

Thanks for the reply Mrshorty, I have looked into it and this seems to be a plausible explanation.

OK I have done this, I managed to calculate ti successfully in some cases, then in other cases I have a very strange situation:

almost identical data (I am seeing how returns differ by changing one share), exactly the same formulas, functions, constraints: and sometimes Solver just refuses to change the variables. When I change them manually and try solver again, it jsut keeps the new variables etc. There is no error message as it told me it ofund a solution.

Strangest thing is this happens sometimes, then maybe if I change something and change it back, it stops happening.

Anyone else have experience with this kind of behaviour by solver? It is driving me crazy as my teachers have not taught us this,w e did not have a single Excel lab in the whole term, and our assignment (Asset Fund Management) is totally dependent on Excel. I would appreciate any suggestions, so much.

Thank you,

C