ExcelBanter - Efficient Frontier/Mean-Variance Optimization

For two assets:

Suppose the asset mean returns are in B3 and C3 respectively.
Similarly, the standard deviations are in B4 and C4 respectively. The
correlation between the two assets is in D4.

Finally, suppose the fraction of asset 1 in the portfolio is in B5.

Then, obviously, the formula in C5 is =1-B5

Let the portfolio mean be in E3 and the portfolio standard deviation in
E4. Then, the formula in E3 is =SUMPRODUCT(B3:C3,B5:C5) and the formula
in E4 is =SQRT((B5*B4)^2+(C5*C4)^2+2*B4*C4*B5*C5*D4)

Now, create a table of values. In E9 through E19 enter the values 0,
0.1, 0.2, etc. In F8 enter =E4 and in G8 enter =E3. Select E8:G19 then
select Data | Table... In the resulting dialog box, click in the column
input cell field then click B5. Click OK. Plot F9:G19 in a XY Scatter
chart.

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Multi-disciplinary business expertise
+ Technology skills
= Optimal solution to your business problem
Recipient Microsoft MVP award 2000-2005

In article ,
says...
Does anybody out there happen to have a Markowitz mean-variance optimizer for
producing an efficient frontier analysis of alternative asset classes -- that
runs in Excel VBA? I need one for an asset-liability study that I'm doing.
Even a simple version would help get me started.