Tricky Portfolio Optimisation

Nightboy

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

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.

Replies
2
Views
459
Replies
0
Views
1K
Replies
5
Views
142
Replies
1
Views
1K
Replies
1
Views
1K

1,219,978
Messages
6,151,246
Members
451,019
Latest member
cichli_04

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.

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

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