Forum Discussion
Find optimal percent
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?
Thanks,
Noah
| -32.1786 | 3.571429 |
| -19.6597 | 18.17108 |
| -7.08861 | 1.012658 |
| -43.6093 | 6.104159 |
| -10.3846 | 7.692308 |
| -29.375 | 15.625 |
| -32.008 | 2.584493 |
| -11.6512 | 17.86991 |
| -20.9756 | 5.853659 |
| 8.888889 | 1.851852 |
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:
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.
