# Maximum possible allocation given constraints

#### eddyble

##### New Member
Hey Guys,

I'm not too sure how to approach this but i think it's a VBA problem and i'm having a lot of trouble figuring it out. Apologies in advance... it's a long one lol

TL;DR The problem is i'm trying to find the optimal allocations to result in the highest possible value given some constraints.

Here's the long version;

 Mkt Val Weight BMK Capacity Deploy New Weight 10 10% 12% 15 x1 y1 20 20% 25% 15 x2 y2 20 20% 18% 100 x3 y3 10 10% 5% 50 x4 y4 40 40% 40% 0 x5 y5 100 (total) 180 (total) X (Total)

<tbody>
</tbody>

Mkt Val = Market Value of some asset
Weight = % weighting relative to sum of market value
BMK = Benchmark weight
Deploy = the amount of capacity that is deployed
New Weight = (MKT Val + Deploy)/(Total MKt Val + Total X) where X is the sum of X1, x2 etc.

Here are the constraints;

New Weight - Weight < +-2%
New Weight - BMK < +-5%

So far i've figured out the maximum X value is ~ 5.2631 which was driven by the constraints acting on the 40 Value (This value has no capacity to add and must be at least 38% according to constraint 1) using guessing and checking.

So the problem is that the New Weightings are based on how much you Deploy which impacts the New Weightings which are driven by the constraints.... so it's sort of very circular and besides guessing and checking to see if it breaches constraints i haven't figured out a way to automate this process and solve for Max X.

Hopefully someone has an idea because this is doing my head in

### Excel Facts

Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.

#### i_nth

##### New Member
This is a problem for Solver. You can construct the model in Solver almost exactly as you've described it.
You're right that the optimal solution is x = 5.2632. There are multiple optima that have that value, including allocating 2.6316 to the first two rows, or 1.7543 to each of the first three rows.

Cheers

Replies
1
Views
89
Replies
1
Views
98
Replies
23
Views
1K
Replies
1
Views
54
Replies
0
Views
179