# Optimization problem: multiple constraints, can excel solve?

#### Joeshaggs

##### New Member
i have an optimization problem that i am trying to solve here and i want your advice on what tools to use and what environment to develop the model.

Essentially the problem is to develop a portfolio of securities (mainly fixed income and mortgage backed securities) based on user defined constraints that would represent a Collateralized Debt Obligations.

Now there are various constraints to optimize over - such as
- overall size of portfolio (example - 1 billion)
- max size of single issuance (example 2% or 20M in this ex)
- ratings stratification (for example user will define what % are
AAA vs AA vs noninvestment grade)
- industry concentrations (MAX and MIN limits that user defines
for a group of industries)
- weighted average spread minimum and weighted aver rating
factor

each issuance has an associated spread (bps over libor) associated with it.

I've thought about this issue and here's how i think i'm going to tackle this:
- user input to be done in excel + vba
- this will pass the constraints to a java engine or a c++ engine (any comments on this?)
- the engine will use some sort of heuristic approach to finding the optimal solution (or solutions)...
- the java/c++ engine will pass back to excel the optimal portfolio based on user constraints

now my question is: rather than me trying to build this thing from scratch, is there objects/code i can use for the actual optimization portion of this problem? In general is there literature or example code that i can base myself off and then customize to my own solution.

Am I thinking about this too hard and can Excel help me solve this multiple constraint problem? I don't think a linear solution is possible and as such i need to use heuristic approaches (genetic algorithms) in order to solve this non-linear problem. However, if you think I'm wrong, please let me know and how i can solve this problem.

sincerely,

Azhar

### Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.

Replies
0
Views
155
Replies
0
Views
130
Replies
0
Views
522
Replies
0
Views
700
Replies
6
Views
692

1,181,614
Messages
5,930,937
Members
436,767
Latest member
Langaws

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

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