Tricky Portfolio Optimisation

Nightboy

New Member
Joined
Sep 14, 2002
Messages
16
Perhaps this question is too difficult. But I’m going to ask it anyway. It is part of a Markowitz mean variance optimisation using Solver. So perhaps only people who do investment stuff will be able to answer it.

Every month I want to form a portfolio of 10 stocks chosen from a market of 50 stocks. I have already chosen the 10 stocks for month 1 based on their expected return. But I don’t want them equally weighted, I want to have them in the proportions that give me the most efficient portfolio, that is the highest return for the lowest possible standard deviation. So I also have a variance covariance matrix that is used in calculating the standard deviation. I also have a constraint that no individual stock can make up more than a certain proportion of the portfolio. This is all pretty straightforward. I have done this before using matrix multiplication in Solver.

However this time I have to deal with an added restriction & I can’t work out how to do it. Each stock belongs to a particular industry. The constraint is that in the efficient portfolio for the month, stocks belonging to a certain industry must have a total weight in the portfolio that is within a boundary set by the proportion of that industry in the entire market.

An example. In the market of 50 stocks there are 7 that belong to “Auto” & together they make up 11% of the market, based on market capitalisation. So in my portfolio, any “Auto” stocks, when totaled, must have a weight that is within 3% of the market proportion. So in this case all the “Auto” stocks in my portfolio must have a combined representation in this months portfolio of between 8% and 14%. No lower & no higher.

My expected return column has 2 columns next to it. The first gives the stocks identifying number (rather than its name, we just label them 1 to 50). The next column gives the industry it belongs to. What I can’t figure out is how to set up Excel so that Solver will apply this constraint. Someone showed me that I can set up 3 rows, where the stocks are divided into their industries. The first row is the stocks in the portfolio, the second is the stocks in the market & the third is the difference between them. But I can’t figure out how Solver can still identify the stocks once I have put them out of order. In the expected return column they have to stay in the order of expected return as this also relates to the variance covariance matrix. I even thought of running the industry restriction as a second run of Solver, but I don’t think that would work. I think it will only be efficient if I run the whole optimisation in one go.

Is anyone game to take this on?
 

Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".

Forum statistics

Threads
1,144,060
Messages
5,722,276
Members
422,420
Latest member
losc

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