Optimization problem: multiple constraints, can excel solve?

Joeshaggs

New Member
Joined
Jan 20, 2005
Messages
2
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

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.

Forum statistics

Threads
1,147,622
Messages
5,742,199
Members
423,711
Latest member
luisfreitas

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
Top