0

Around 4-5 years ago I have already tried myself with the portfolio theory and was successfully. You can find this Excel here. Now I wanted to do the whole thing with other ETFs, but unfortunately I get from the Excel solver always directly said that no solution could be found. However this exception promts up so fast, as if he would not even begin to calculate.

I tried to find the problem, but did not find it. Then I opened the old Excel and tried to reproduce the results from 4-5 years ago (12%, 12.50%, 13% etc. return) by changing the value in the yellow highlighted field and running the solver. However, I get the same result here as well. The solver immediately tells me that it couldn't find a solution, even though I haven't changed anything in Excel since the last time I used it 4 years ago and the solver found a solution then too....

Can you tell me what the reason is and how to fix the problem again?

I tried it with Excel 2016 and Excel 365! Same problem on both!

Many thanks!

Jan
  • 103

1 Answers1

1

There is no connection between the yellow cell, I12, and anything else. It is simply an entered value which is not used anywhere in either worksheet.

So nothing can affect it and it can affect nothing.

That leaves the solver with absolutely no way to affect it or to have it affect anything else.

So there is absolutely no solution it can ever find.

Separately from that, for general use, the solver runs "X" number of iterations based upon a setting in Excel's general settings: File, Options, Formulas, Calculation options. That is the first area on the Formulas heading. On the right side of the dialog box, Maximum Iterations.

Mine is set at 100. If I were to use Solver and it reported no solution, I would first look at the value reached by the value it is allowed to change. If it is ridiculous, especially, or diverging from seemingly realistic values I might give up, thinking that there is no solution. If not, or if the diverging value had looked to be going back and forth as it iterated, I would change that Maximum Iterations value to a higher value. Perhaps 1,000. Of course, if it took 3 minutes to run through 100, I might not go that high. If it took 6 seconds, then why not? There are actually other ways to accomplish Solver's task which do not consume time, so I'd shift to them.

In your case, Solver cannot ever find a solution though, so you need to work on how you will make it able to do so. The A8:A11 values seem to be the only changeable thing, but the setup does not actually feed through into a value for returns which are in fact based upon historical values on the Data sheet. You can't change those so...

You gots yourselfs some problems.

I honestly don't see how it ever performed in the past.

Jeorje
  • 24