This free workbook contains nine example models from investment and portfolio management.  Click the model names to display each worksheet model in your browser.

You can use the worksheet that most closely models your situation as a starting point.  Solving your real problem may require "scaling up" the model to include more variables and constraints—but rest assured that you can solve your full-size problem with the power of our Excel Solver upgrades, even if it requires hundreds of thousands of variables!

To download and save the workbook if you are using Excel 2007 or later:

To download and save the workbook if you are using Excel 2003 or earlier:

You can then actually solve these small example models in Excel, using the standard Excel Solver, Analytic Solver Upgrade or Analytic Solver Optimization.

Or, if you would prefer to view the examples in your browser, rather than in Excel, click the links below.
  • Theory - Display this worksheet for a brief discussion of the theory of efficient portfolios and the principles of duration matching in bond portfolios
     
  • Markowitz - Portfolio Optimization - Markowitz Model:  Allocate funds to stocks to minimize risk for a target rate of return - assumes that variances and covariances are known
     
  • Full Markowitz - Portfolio Optimization - Markowitz Model:  Allocate funds to stocks to minimize risk for a target rate of return - calculates variances and covariances from historical stock prices
     
  • Efficient Frontier - Stock Portfolio Management:  Uses a VBA program to optimize several scenarios for minimum risk at different target rates of return, then draws a graph of the efficient frontier
     
  • Sharpe - Portfolio Optimization - Sharpe Model (CAPM):  Uses Excel's regression functions to calculate alphas and betas for stocks relative to a market index, then uses these to find an efficient portfolio.
     
  • Bond1 and Bond2 - Bond Portfolio Management:  Allocate funds to bonds to maximize return while ensuring that the portfolio duration equals the investment horizon for maturity - Bond1 assumes that bond durations are known, Bond2 calculates bond durations from yields, coupons and face values
     
  • Bond3 and Bond4 - Bond Portfolio Exact Matching:  Allocate funds to bonds to maximize portfolio return while ensuring that periodic liabilities (cash outflows) are met - Bond3 uses bond coupons alone, Bond4 allows for reinvestment of interest in excess of periodic liabilities

On each example worksheet, read the description at the bottom, then select Solver on the Data tab in Excel 2007, or Tools Solver... from the menu in Excel 2003.  When you see the Solver Parameters dialog box, click the Solve button to find the optimal solution.

We're here to help—contact us if you'd like more information or advice on your application.

< Back to Examples Overview