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.

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 < XXXX@XXXXX.COM >,

XXXX@XXXXX.COM says...

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 < XXXX@XXXXX.COM >,

XXXX@XXXXX.COM says...

1. frontier - Rolling efficient frontier

2. Mean Variance optimization with FMINCON

3. Plotting efficient frontier

6. Combining efficient frontiers

7. Pareto / Efficient Frontier / Multiobjective / Nondominated solutions

9. Efficient Moving Average and Moving Variance Calculations

10. Portfolio optimization using minimum-variance (fmincon)

11. uniform random noise with zero mean, variance not equal to 1

12. Variance and Root Mean Square of vectors and quaternions

13. Wiener filtering using mean-squared method: variance problem

14. Plot point only when line begins with special string && calculate mean and variance

15. SIGMA in normrnd and lhsnorm means the same (standard deviation or variance?)

2 post • Page:**1** of **1**