Forum Discussion
Find optimal percent
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.
JoeUser2004 I think we are getting close. I plugged in your calculations for another group of ten stocks and it didn't quite give me the optimal sell. As you will see in the attached file the Ideal sell comes out as 1.851852, while in reality, the ideal sell is 6.382979. Is there a reason it gave me the wrong sell? I have put the equations I use for total % per highest % and Ideal sell so you can double-check those are right. Thanks again for the amazing help. Also for clarification, column R is the gain or loss each stock sells for and Column S is the total gain or loss. Columns Q, T, and K don't matter in this calculation.
NoaHomeRun wrote: ``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``
On second thought, I think it is better not to rely on that, and to stick with my original design instead, relying on "%portfolio".
Most portfolios do __not__ allocate funds equally to all assets.
-----
But to answer your question ....
Note that this applies only when "%portfolio" is the same for all assets, namely 1/#assets. For example, 1/10 in your example.
What I meant was.... In Solver, instead of entering E13 for "Set objective", enter C13, where C13 has the formula =SUM(C3:C12).
All other Solver remain the same.
Maximizing C13 has the same result in B1 as maximizing E13, because C13 is E13*10 -- or more generally, E13*#assets.
