Portfolio Optimization

See also: ModelRisk functions and windows, Optimisation in risk analysis introduction, Insurance and finance risk analysis modeling introduction

The Portfolio Optimization window uses the Capital Asset Pricing Model (CAPM) to find the composition of a portfolio of assets that has optimal return rate for minimal variance (i.e. sensitivity for market risk).

In the view of this model, two types of risk are at play for assets:

  • The non-systematic risk attached to an individual asset. This can be reduced (to the point where it is neglectable)  by diversifying the portfolio, so this risk is also known as diversifiable risk.

  • The systematic risk, caused by the uncertainty of the market. This can be thought of as the risk that is still there when adding the asset to a portfolio that is already well diversified. This type of risk is called the non-diversifiable or market risk.

Sensitivity for the second type of risk (which is the most important, as the first can be diversified away), called the variance of the portfolio, is represented by beta coefficient in finance. An optimal portfolio is one that has the lowest variance - lowest beta coefficient - for a given return. In a variance-return plot, these optimal portfolio combinations make up the efficient frontier.

As total budget to invest is often a constraint when composing a portfolio, the quantities of each asset that comprise it are expressed in weights (proportions of the total budget). The budget constraint is accounted for in the fact that the weights sum to one.

One other component can be incorporated. Rather than investing the entire budget in assets, one might keep part of the budget in cash, earning an (albeit low) interest at the risk-free return rate. The variance-return relationship of this is linear, and represented as the Security Market Line (SML).

Both components are optimally accounted for in the Tangent Portfolio: where the SML and efficient frontier meet.

So, in summary: the Portfolio Optimization window finds the optimal set of asset weights for a given portfolio, taking into account market risk, correlation between the assets, the "risk-free" interest rate of the assets, and of course the returns and deviations of each individual asset. For calculating this optimum, the Tangent Portfolio, the CAPM model is used.

To see the output functions of this window, click here.

Window elements

In the output range field, you can specify where the calculated asset weights are inserted in the spreadsheet (upon pressing the OK button). This should be a 1xN array, where N is the number of assets.

In the Number of Assets field, you can specify the number of assets to be included in the portfolio. (5 by default) This should be a positive integer.

In the Interest Rate field, you can specify the risk-free interest rate. This should be a real number greater than zero.

In the "asset matrix" shown, you can specify the Labels, Returns and Deviations attached to each individual asset.

The correlation matrix of the portfolio is shown. This matrix's elements can be obtained from the spreadsheet (should be an NxN array), or specified withinin the Portfolio Optimization window.

On the right, the individual assets, the efficient frontier and security market line are shown in a variance-risk plot. By default the assets are colored in green, the efficient frontier in blue and the security market line in red.

For explanations about other fields, buttons, graphs and summary statistics tables in this window, see Common elements of ModelRisk windows.

Useful tips and tricks

See also: Graphics, workflow and error handling in ModelRisk

Using View Function to return to a window

The output of ModelRisk windows always corresponds to VoseFunctions (the functions ModelRisk adds to Excel) being entered into one or more spreadsheet cells.

You can always re-open the window for a ModelRisk function that is in a spreadsheet cell by using View Function. Select the spreadsheet cell and then select View Function from the ModelRisk menu/toolbar/ribbon.



Monte Carlo simulation in Excel. Learn more


Adding risk and uncertainty to your project schedule. Learn more



For Microsoft Excel

Download your free copy of ModelRisk Basic today. Professional quality risk modeling software and no catches

Download ModelRisk Basic now


For Primavera & Microsoft Project

Download your free copy of Tamara Basic today. Professional quality project risk software and no catches.

Download Tamara Basic now