@NoaHomeRun.... I believe that will depend on the %portfolio allocation of each asset, which you did not provide.
Consider two arbitrary guesses (not necessarily optimum), based on random %portfolio allocations: (1) sell at avg instead of min highest%; and (2) sell at min instead of avg highest%. See the "avg-v-min" worksheet in the attached Excel file.
Note: Check my logic under "sell%" (column C) to be sure that I interpreted your rules correctly.
I would use Solver to find an optimal sell%, based on a random %portfolio allocation. (Of course, you would substitute the real %allocations.) See the "opt" worksheet.
For example:
Solver set-up, starting with empty B1:
Set objective: E13
To: Max
By changing: B1
Uncheck "make unconstrained variables non-negative"
Solve method: GRG Nonlinear
PS.... The value derived in B1 is __not__ rounded to any particular precision. I would __not__ try to force Solver to derive a rounded value; often, that causes Solver to fail because of internal "discontinuities". Instead, I would round the value myself (e.g. to 6 decimal places) __after__ Solver finds solution. Arguably, the rounded value might no longer be the best solution.
PPS.... I have never been confident in Solver to find a truly min or max value, even after adjusting the constraint and convergence precision options. IIRC, sometimes Solver finds very different min or max values, even when starting with the same initial value (e.g. empty B1). Arguably, the fault might be my rudimentary knowledge of Solver. "A word to the wise ...."
@NoaHomeRun wrote: ``This is actually not a class assignment``
My bad! I misinterpreted a behavior that I discovered.
But when I explore the behavior in more detail, I can see that the real "problem" (characteristic) is: your algorithm creates multiple "local maximums", which are the "highest%" values themselves.
As a consequence, Solver does not always find the truly optimum "best sell%". Depending on the initial value in B1 and the %portfolio allocation, Solver might find (almost) any of the "highest%" values in B3:B12.
But aha! This gives rise to a completely different approach for finding the optimum "best sell%". It depends solely on the "highest%" values. And it does not require Solver.
(PS.... The following alternative relies on the behavior of your current algorithm. If you choose a different algorithm later, it might not work so well.)
See the graph below. If it is not readable here, look at the chart in H15 in the "opt" worksheet of the attached file. It depends on the tables starting in B19 and E19.
The blue "curve" represents the "total%" (weighted average return) for "best sell%" values from 0.01 to 19 in increments of 0.01.
The red "dots" represent the "total%" for each of the "highest%" values.
That chart is based on equal allocation to all assets (10%). The details of the chart might look different with a different set %portfolio allocations. But the basic characteristics are the same.
Given the shape of the "curve", namely the local maximums at the "highest%" values, we can find the optimum "best sell%" by finding the "highest%" values that yields the highest "total%".
See the helper cells in H3:H12. The formulas are of the form (in H3):
=SUMPRODUCT(IF(B3>$B$3:$B$12, $A$3:$A$12, B3), $D$3:$D$12)
That must be array-entered in some versions of Excel. That is, press ctrl+shift+Enter instead of just Enter.
And see the formula in B1, namely:
=INDEX(B3:B12, MATCH(MAX(H3:H12), H3:H12, 0))
In the attached file, the "%portfolio" range D3:D12 refers to the random %portfolio in I3:i12. Thus, it changes when we press f9, as well as when other edits cause recalculation.
Press f9 several times to see how the chart and B1 change.
Replace D3:D12 with the constant 10%, if that is the asset allocation that you want.
