Find the most profitable outcome - This is giving me a headache!!

Hello,

I am working on a spreadsheet that will ultimately help me determine the most profitable way to take profit using a given trading strategy (trading financial instruments) while remaining within my maximum loss restrictions (5% daily and 10% total of initial balance), for each trade taken I have recorded the maximum potential reward from the trade in relation to the risk.

E.g if your risk for the trade is set to 5 pips and the maximum potential this trade is 10 pips the maximum potential is recorded as 2, a multiplication of the risk (commonly known as your Risk to Reward Ratio, R, RR or RRR.

There could be thousands of different trades recorded, the data will have many unique 'maximum potential' levels 1.3,7.5,13.5,2.3,23.7 and so on, some may duplicate as well.

When I take a trade I can split that trade into a maximum of three different positions, each position will have its own profit target.

E.g I may take a trade where my positions are split in to:

Take Profit 1 at 1.5R
Take Profit 2 at 3.5R
Take Profit 3 at 6.5R

In addition, each position will represent a percentage of the total trade.

E.g The percentage may be distributed as follows:

Position 1 - 20%
Position 2 - 50%
Position 3 - 30%

So what will this look like in a real life scenario?

Let's say I am trading with a balance of $100,000, I open a trade risking 1% of my balance ($1,000), I split this in to three positions with different take profit levels and each representing a % of the risk.

Position 1 - 20% and Take Profit at 1.5R
Position 2 - 50% and Take Profit at 3.5R
Position 3 - 30% and Take Profit at 6.5R

We'll be optimistic and say that this trade reaches a maximum of 10.5R, this means that all of our take profits were hit and the total reward for this trade is 4R ((0.2*1.5+0.5*3.5+0.3*6.5) or (Position 1 - % * Position 1 - Take Profit + Position 2 - % * Position 2 - Take Profit + Position 3 - % * Position 3 - Take Profit))

With this one piece of data we can easily say that the most profitable way to take profit would have been 100% of the position at 10.5R, however as mentioned before we have thousands of trades to refer to and some maximum loss restrictions to stay within.

So let's look at this in a bit more detail, let's say we have 10 trades each with their own maximum reward potential and some that are losses (they didn't move in the direction we anticipated, consequently we made a loss)

Balance: $100,000 Risk: 1%

1 - 10.5R - $10,500
2 - 1.2R - $1,200
3 - -1R - -$1,000
4 - 3.4R - $3,400
5 - -0.43R - -$430
6 - 5.6R - $5,600
7 - -0.2R - -$200
8 - -1R - -$1,000
9 - 4.3R - $4,300
10 - 16.8R - $16,800

You will notice that not all of the "losing" trades lose the same amount either as they may be exited early rather than reaching the full risked amount.

Essentially I require a formula that tells me what the most profitable variation of take profit levels, position % splits and risk % per trade while staying within the daily loss (5%) and maximum loss (10%) restrictions.

I have attached the current version of my spreadsheet with 10 trades inputted for you to play around with. Please don't laugh at my formulas I know they are convoluted but I'm no pro at this :D

*I've just realised that I cannot attach anything yet, if you're able to help I'd love to chat privately and I can always send the sheet over to you.