Optimising portfolio with constraints

[Pr0sp3cT]

Hi guys,

Hoping you can help me with this problem.

I'm looking at some loan data. There are some concentration limits (3 examples below) in place for the loan portfolio:

1: Sum of outstanding loans larger than 400k, cannot be greater than 5% of the total portfolio
2: Sum of outstanding loans with grade 'B' cannot be greater than 30% of the total portfolio
3: Sum of outstanding loans with 2nd rank pledge cannot be greater than 10% of the total portfolio

The loan tape data shows excess concentrations in (1); namely it is 10% of the total portfolio, and in (3); as it is 15% of the total portfolio. Criteria (2) is fine, as it is 18% of the total portfolio.

The goal is to optimise the portfolio in such a way that the portfolio concentration limits are not breached, by taking out individual loans. Ideally you would want the portfolio to be exactly on the limits, so that you have maximised the ($) amount of loans in the portfolio.

What you probably want to do is to see which loan contributes the most to the excess limits, and take that loan out. This is an iterative process, untill you hit the limits of all concentrations.
I was thinking of using solver for all three concentration limits (and then having a macro run that), but that way it works sequentially, and it must work more simultaneously.

How would you go about this problem? Understand it maybe difficult to understand what I mean. Many thanks.

Kind regards,
Mark

 

[i_nth]

Solver is ideal for this type of problem. I'm not sure what you mean about Solver working sequentially - the whole idea of a mathematical program is that the constraints are solved simultaneously.
The model could be constructed by using a binary variable for each loan, indicating if it should be included in the portfolio (1), or not (0). Multiple the available loan amounts by the binary variable. Calculate the sum of the portfolio loans for each concentration limit. In Solver create constraints for each concentration limit and maximum the size of the portfolio. This should be able to be solved using the Simplex solver, which guarantees an optimal solution (if feasible).