+ Reply to Thread
Results 1 to 3 of 3

Efficient Frontier/Mean-Variance Optimization

  1. #1
    Stratuser
    Guest

    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. #2
    Tushar Mehta
    Guest

    Re: 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 <[email protected]>,
    [email protected] 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.
    >


  3. #3
    Registered User
    Join Date
    07-30-2010
    Location
    South Pole
    MS-Off Ver
    Excel 2010
    Posts
    99

    Re: Efficient Frontier/Mean-Variance Optimization

    Quote Originally Posted by Stratuser View Post
    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.
    Does this spreadsheet help? Mean variance optimization

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1