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
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