Solver/Formula- 2 Parameters, Portfolio Optimization

[Keibri]

Hi all,

I am writing to ask whether there can be a solution for a sample of investment I am currently working on. For ease of reference I am attaching a fictitious worksheet to give an idea.

Basically I have a portfolio consisting of a number of bonds and for each bond the amount invested is shown in Column E. As one can see from the excel attached, for each bond the main parameters that interest us are:
-Sector: Whether the bond is "Investment Grade" or "Non-Investment Grade"
-Range Months: the amount of months remaining for each bond to mature

These parameters are summarized in 2 pivot tables on the right hand side.

Currently the total amount invested in a portfolio is EUR 177,893.


Problem:

I would like to remove a total of EUR 35,000 from this portfolio (so that the total amount invested will be around EUR142,893 (it doesn't have to be exactly to the nearest decimal point)).

The only conditions are that both the existing % parameters should if possible be maintained, i.e:
-Investment Grade: approx. 95%; Non-Investment: approx.5 %
-2 to 3 years approx. 28%, 1 to 2 years approx. 25% etc


Not sure whether there can be something (formulas, solver maybe, VBA) which would work out the above, i.e. removing a total of EUR 35,000 and retaining the existing allocation/parameters. Sounds a bit complex so I am not sure whether there can be some solution.

P.S: the percentage doesn't have to be exact

Would really appreciate your kind help

Thanks

Keibri

[Hydraulics]

I'm offering a Solver based solution (actually, two solutions). In the worksheet you can set individual values for upper and lower percentages in each category (sector and duration). Both solutions will try to minimize the absolute value of the difference between the required and calculated sum.

The first solution relies on Evolutionary engine, and is pretty basic. The second adds one variable and two constraints, so that we can use Simplex, and returns on average better results in less time. It is needed only if you are dealing with more than 200 investments (variables).

HTH,

Francesco