Find optimal percent

Copper Contributor

Hi all, I am trying to find an optimal percent to sell a group of ten stocks. Down below I have copied some info. The first column is the final percent change of the stocks and the second column is the highest percent change the stock gets to. I want a command that will find the best percent to sell all the stocks, (one percent for all of them not an optimal percent per stock). Basically, if the percent is higher than the second column it will sell at the first column level, while if the percent is lower than the second column it will sell at the percent chosen. Can anyone help come up with a formula for this? 




12 Replies

@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:


opt sell.jpg


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 ...."


@Joe User 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:




Hope that helps.

@Joe User 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: ``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.

@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.


opt sell curve.jpg


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.


@Joe User 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. 


@Joe User Also there should never be an option for the ideal sell to be 0 because that would mean I never buy the stocks and that messes up my other calculations

@NoaHomeRun  wrote: ``I plugged in your calculations for another group of ten stocks and it didn't quite give me the optimal sell.``


Please always attach an actual Excel file, not an image.  I cannot debug an image.


I see one definite mistake in your formulas, and a possible second mistake, depending on your version of Excel.  But I cannot say whether those mistakes are really in the actual formulas, or just in your re-typing of them ("Equation...").


In the SUMPRODUCT formula, references to ranges like M146:M155, L146:L155 and N146:155 should be absolute references (e.g. $M$146:$M$155), not relative references (without "$").  But references to individual cells like M146 should be relative references, as you wrote them.


Also, the SUMPRODUCT formula must array-entered, or act as if it were.  In earlier versions of Excel, the formula should be surrounded by curly brackets in the Formula Bar.


I'm not sure about recent versions of Excel, where the array-entry might not require ctrl+shift+Enter.  I __think__ (not sure) that a "non-arrayed" formula will have "@" in front of the formula in the Formula Bar.  That would be a second mistake.


Again, I can debug this better if you provide the errant Excel file itself.



But coincidentally, I had put together another example that allows for randomizing the "last%" and "highest%" of the 10 assets -- essentially the change that you want.


See the "rand data" worksheet in the attached Excel file.  Set D1 to FALSE to restore your original "last%" and "highest%" values, but still randomize "%portfolio".

@NoaHomeRun  wrote: ``there should never be an option for the ideal sell to be 0``


With the SUMPRODUCT approach to finding the optimum, it is implicit in the fact that you will never have 0% for "highest%", presumably.  Right?


OTOH, if you do allow for 0% for "highest%", the SUMPRODUCT approach will choose that only if that does indeed maximize the total% return.  Any alternative would result in a "greater" loss (more negative).  Why would you prefer that?



With the Solver approach, a constraint like B1>=0.01 could be added.


No matter: As I already explained, Solver does not always find the optimum "sell at" percentage based on your algorithm.


@Joe User Thank you for your suggestions. I think I figured out the problems with the $'s. The issue now, as you mentioned is that the answer becomes 0 if there is no way to sell the stocks for a gain. I would much rather the formula produce a solution that sells them for the least loss rather then 0. You mentioned that the solver approach could do this. Could you explain that further? I have never used solver before. Thanks. Attached is a copy of my worksheet.

@Joe User actually, never mind, I figured out the solver solution. Thank you so much for all the help you have given me. If there is anything I can do for you don't hesitate to ask. Thanks