Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Efficient Frontier/Mean-Variance Optimization
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
production frontier | Excel Worksheet Functions | |||
Pivot Tables - Variance and Variance % | Excel Discussion (Misc queries) | |||
how to plot efficient frontier | Excel Discussion (Misc queries) | |||
How do you graph a PPF (Production Possibilities Frontier) chart . | Charts and Charting in Excel | |||
Pivot Tables - Variance and % Variance fields | Excel Discussion (Misc queries) |