Markowitz efficient frontier

**thedon_1** · 11-11-2009, 11:52 AM

Hi, i'm trying to plot the Markowitz efficient frontier in Excel. I have returns and other data for 20 stocks.

I use solver to find the optimal weights of the 20 stocks for the the lowest variance portfolio.

I need to then find the weights of the stocks from this point to the highest possible return portfolio.

Finding the portfolio with the smallest variance and with the highest return is fine with solver, i just use the min and max functions.

How do i find the points in between?

For example, the return on my minimum variance portfolio is 1, the return on my max return portfolio is 100. Right now, i would need to run optimizer with minimum variance for the returns of 2,3,4....all the way to 99 to find those points.

Any shortcuts or add ins?

Thanks

**Richard Buttrey** · 11-11-2009, 12:28 PM

Hi,

Does the attached help?

Regards

**thedon_1** · 11-11-2009, 12:51 PM

Due to the file being from the new Office it doesn't let me go out of design mode. I can't really try anything out but yeah i want to do what this sheet is doing.

I assume you or whover worked on the sheet has written a macro or something to make solver run for the different conditions.

Any help for how i can do this?

**Richard Buttrey** · 11-11-2009, 02:27 PM

Hi,

Does this .xls version help. The macros don't use Solver although clearly Yazann Romahi from the Cambridge University Investment Club who wrote the code clearly intended to do so.

The iterative methods builds arrays of values as it iterates through the number of times you plug in, and then plots the xyScattergraph.

....looks like the forum is not accepting attachments just at the moment. I'll attach it later.

Rgds

**thedon_1** · 11-13-2009, 05:10 AM

I suppose what i'm really asking is how can i get solver to go through a list of target values and give me results without me having to manually change the figures after each calculation.

**jerseyguy1996** · 11-13-2009, 04:25 PM

Originally Posted by thedon_1

I suppose what i'm really asking is how can i get solver to go through a list of target values and give me results without me having to manually change the figures after each calculation.

This was literally the first code I ever wrote and as it is a work in progress it hasn't been cleaned up. Being very new to any kind of programming it is also messy as heck but it will hopefully give you some ideas. You will need to set up your worksheet to place the different arrays where they are located in the code but it does a pretty good job of working through all of the constraints. It loops through all of the efficient portfolios with mean returns from min to max in .1% increments.

Please Login or Register  to view this content.

**cloudedthought** · 02-10-2010, 06:42 AM

Hey does any one know how to include transaction i.e brokerage costs to determine the new efficient portfolio .
Assume there are 20 stocks and a brokerage rate is known and common for both buying and selling .

**DonkeyOte** · 02-10-2010, 06:53 AM

cloudedthought,

Your post does not comply with Rule 2 of our Forum RULES.

Don't post a question in the thread of another member -- start your own thread. If you feel it's particularly relevant, provide a link to the other thread.