Forum Discussion
Find optimal percent
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 ...."
JoeUser2004 Thank you so much for your response it was so so helpful! Sorry for not adding portfolio percent, each one holds 10%. I followed your instructions as best I could, with my limited knowledge of excel, and I wasn't quite sure how you got the "pro rata %" section. Could you explain how you got that? Thanks again for taking the time to help me.
NoaHomeRunwrote: ``Thank you so much for your response it was so so helpful!``
You're welcome. But it was too helpful. You should have told us this is a class assignment. I would have offered guidance, but not a turnkey solution. My bad!
Since all assets comprise an equal share of the portfolio, we do not need "pro rata" calculation. We could just maximize the sum of the assets' returns ("sell%") in column C, or better: the sum of the returns divided by 10 (the equal asset share), which is the total portfolio return.
"Pro rata" is probably not the best term. I just could not think of anything better.
And if you are asking about the derivation in columns H:I, it is nothing that you need to understand. It was just my way of generating a random asset allocation. I probably should have removed it.
But to explain.... In column I, we generate 10 random values. Their sum represents 100% of the portfolio. And each random value divided by the total represents the percentage of the portfolio for that asset.
(BTW, since column H is labeled "%portfolio", it should have been formatted as Percentage, like column D.)
If you are asking about how we use those percentages to calculate the "pro rata%" (again, poor terminology) in column E.... Essentially, we are calculating the weighted average, which is the sum of E3:E12 in E13.
In other words, an asset's contribution to the total portfolio return is the asset's return ("sell%") times its percentage of the portfolio ("%portfolio"). Another way to calculate the weighted average is:
=SUMPRODUCT(C3:C12, D3:D12)
Hope that helps.
JoeUser2004 Thanks again for the detailed response. This is actually not a class assignment but a hypothetical trading algorithm that I am testing to see if it has any actual chance of turning a profit. While I can calculate the optimal "sell%" manually it takes time and has room for user error. Also when I get to bigger sample sizes, hundred of stocks, it will be much harder to calculate manually. When you say, "We could just maximize the sum of the assets' returns ("sell%") in column C" I am not quite sure how to implement this into excel, seeing as I am a relative newbie to the program. Would you mind explaining how to do that or writing a formula for that? I am so grateful for the time you have already spent explaining things to me, it has truly been very helpful.
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.