Optimising portfolio with constraints

Pr0sp3cT

New Member
Joined
Dec 15, 2017
Messages
1
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
 

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.
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).
 
Upvote 0

Forum statistics

Threads
1,223,896
Messages
6,175,259
Members
452,626
Latest member
huntinghunter

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top