Up
 

Data download &  analysis

Portfolio
optimization

Scenario comparisons
 

Active portfolio management statistics
 

Leverage & capital allocation

Estimating
volatilities, correlations, & returns

Versions &
licensing

Software environment
 

Download Portfolio Optimizer

Hoadley Portfolio Optimizer

The Hoadley Portfolio Optimizer applies the principles of Modern Portfolio Theory (MPT)  and the Capital Asset Pricing Model (CAPM) to analyzing and optimizing a portfolio of stocks and/or other asset classes.

The Excel-based application, which is not password protected, performs two key functions:

The data download and analysis function (which is optional) will automatically retrieve historic stock, fund, and index prices from Yahoo Finance (most exchanges supported) for a complete portfolio and will calculate key risk measures such as volatility, Beta, and R-Squared:  for individual securities, the specified index, and for the portfolio as a whole. 

Value at Risk (VaR) and Conditional Value at Risk (CVaR, also known as Expected Shortfall) are also calculated for the portfolio.

Note: The retrieval of data from Yahoo is optional and the portfolio optimization process does not in any way depend on Yahoo data. The optimizer can be used with data from any source.
 

The portfolio optimization component uses mean-variance optimization (MVO), originally developed by Harry Markowitz, to determine the weightings of each asset required to produce a range of returns for the portfolio at the lowest possible risk (the efficient frontier).  The "assets" to be optimized can be individual assets (eg individual stocks, bonds, funds) or asset classes (eg equity and other indices, funds of a particular investment style, an industry sector).

Minimum and maximum weights can be specified for each asset, including negative weights if short selling is permitted.

Assets can also optionally be assigned to user-defined asset groups.  The groups could represent industry groupings, investment style (value, growth, small cap...), asset class (domestic equities, foreign equities, fixed interest...) or anything.

Minimum and maximum weight constraints can be specified by asset group as well as by asset.  So, for example, you could specify that no more than 15% of a portfolio should be in technology stocks.

An optimal portfolio --  the portfolio which will provide the maximum return for the lowest unit of risk -- is then estimated by maximizing the Sharpe ratio.  A minimum variance portfolio is also produced.

The portfolio to be optimized can either be a portfolio analyzed by the portfolio analysis component using data downloaded from Yahoo (as described in the portfolio analysis section above), or by using user-supplied historic data (not from Yahoo). The portfolio can consist of individual assets or multiple asset classes rather than individual assets.

Following optimization the efficient frontier, and associated capital allocation line (or capital market line), are automatically charted to provide a graphical representation of the risk/reward trade-off options available.

If a previously analyzed "current" portfolio is used, then the Sharpe ratio is also calculated for this portfolio and its position relative to the efficient frontier is shown on the efficient frontier graph (the yellow diamond in the example shown above). This lets you easily compare your current portfolio with an efficient portfolio in terms of return per unit of risk.

All results can be viewed graphically (as shown above) or in tabular form showing the individual optimal asset weights for each point on the efficient frontier.

Individual assets can also optionally be shown on the the  chart so their risk and return can be viewed in in relation to the efficient frontier.

If assets have been assigned to user-defined groups, an analysis is produced showing the actual allocations by asset group (eg industry, investment style...) for each point on the efficient frontier and for the optimal portfolio. The current portfolio is also summarised by asset group to enable easy comparison with the optimal portfolio.

View: Portfolio Optimizer tutorial.
 

Optimization scenarios --  sets of assumptions on returns, volatilities, minimum and maximum weights, correlations and asset groupings -- can be saved and later retrieved for further analysis and fine tuning.

The optimization results -- optimal and minimum risk portfolios, and efficient frontiers -- from saved scenarios can also be retrieved and compared, two at a time, both in tabular and in chart form.  The above picture illustrates how the efficient frontiers from two scenarios can be plotted on one chart for comparative analysis.

Saving optimization scenario datasets and scenario comparisons is available in the Portfolio Optimizer Pro version of the Portfolio Optimizer.
 

Using the results from the optimization process the Portfolio Optimizer produces a comprehensive set of active portfolio management statistics.

The statistics include excess returns and risks (tracking error) decomposed into residual (alpha), active, and market (benchmark);  Beta and R-Squared; the Sharpe ratio; residual and active information ratios, and M-Squared (a measure of risk adjusted return). 

These statistics are produced for the optimal portfolio (tangency portfolio), the minimum variance portfolio, and for ten combinations of optimal portfolio and cash holdings to show the effects of leveraging/de-leveraging the optimal portfolio along the capital allocation (or capital market) line.  Active portfolio management statistics are also shown for a portfolio with a beta of one.

A correlation matrix and volatilities are produced for the residual risk (residual covariance).  This can be used, for instance, by portfolio managers who wish to focus on maximizing alpha while ensuring that their market risk tracks their nominated benchmark closely, or by eliminating beta entirely (a market neutral strategy). 

Active management statistics are available in the Portfolio Optimizer Pro version of the Portfolio Optimizer.
 

When investors can borrow and lend at the risk free rate then the capital allocation line (CAL) -- shown in red on the picture below -- will be a straight line tangent to the efficient frontier. Theoretically, all investors should hold the same "risky" (optimal) portfolio, differing only in the amount of leverage applied. By combining risk free borrowing and lending with the optimal portfolio an investor can select a point on the CAL commensurate with their tolerance for risk.

However, usually investors cannot borrow at the risk free rate. When borrowing is more expensive the CAL will be kinked, with the slope of the line to the right of the optimal portfolio (the yellow triangle in the picture below) being flatter than the slope of the line to the left of the optimal portfolio.

In this case the CAL will dip below the efficient frontier for a range of volatilities (as shown on the above picture).  This indicates that, to obtain returns in excess of the optimal portfolio, there are more efficient asset allocation options available than simply leveraging the optimal portfolio.

The Hoadley Portfolio Optimizer produces a leverage analysis showing the total portfolio returns and volatilities for a range of cash/optimal portfolio combinations, from 0% invested in the optimal portfolio (ie 100% in cash) to 200% invested in the optimal portfolio.  A premium over the risk free rate for borrowing can be specified, to clearly highlight the real risk/reward tradeoffs available to the investor or portfolio manager.

 

The Hoadley Finance Add-in for Excel includes all the tools needed to estimate the key inputs to the portfolio optimizer:  asset (or sector) volatilities, correlations, and expected returns.

Volatilities and correlations can be easily estimated from historical data using the volatility and correlation functions in the Finance Add-in for Excel, or by using the portfolio analysis sheet in the portfolio optimizer to automatically download data and calculate these measures, as described above. 

Downside deviation and correlation ( ie semicovariance) calculated using the Hoadley downside risk functions can be used instead of standard volatility and correlation, to perform Downside Risk Optimization (DRO) if required.  In this case the Sortino ratio, rather than the Sharpe ratio, would be maximised to produce the optimal portfolio.

Expected returns, however, are more difficult to estimate.  Historical returns (using historical price data) are a very poor guide to future returns -- in fact they are of no value at all in portfolio optimization --  so other techniques must be used.

One of the most sophisticated and widely used of these is the Black-Litterman Bayesian asset allocation model. The Black-Litterman model  was developed by Fischer Black (of Black-Scholes fame) and Robert Litterman at Goldman Sachs in the early 1990s.

The Black-Litterman model starts with market (or strategic portfolio) asset weightings and backs-out, using reverse optimization, the returns implied by the market. These equilibrium returns are combined with user views about the absolute or relative performance of assets/sectors to produce revised estimates of returns.  The revised estimated returns are then used in the Hoadley Portfolio Optimizer to produce the optimal portfolio and efficient frontier.

Using the Black-Litterman model leads to well diversified portfolios which avoid extreme long/short positions, and the wild swings in asset allocation weights (and hence portfolios which are impractical to implement) which you normally get from MVO when asset returns are changed, even slightly, in isolation.

The Hoadley Finance Add-in for Excel includes a full implementation of the Black-Litterman model.

View: Black-Litterman tutorial which demonstrates, by way of example, how freely available S&P Global Industry Classification Standard (GICS) data can be used to estimate expected returns for equity markets.

 

There are two versions of the Hoadley Portfolio Optimizer:  Portfolio Optimizer Personal and Portfolio Optimizer Pro.  Both versions are included in the download file which is available to users of the full version of the add-in who are within their one year free download period.

The two versions of the Portfolio Optimizer are identical to use.  However they are based on different technologies.

Portfolio Optimizer Personal uses Microsoft Excel Solver to find each portfolio on the efficient frontier and the optimal (tangency) portfolio.  It is suitable for optimizing up to forty assets.

Portfolio Optimizer Pro uses the HoadleyEfficientFrontier function which implements the Markowitz critical line algorithm.  Excel solver is not used and it is therefore much faster than the the Portfolio Optimizer Personal version. 

For example, Portfolio Optimizer Personal will will typically take between one and two minutes (depending on linear constraints) to produce the efficient frontier plus the optimal portfolio for 40 assets, whereas Portfolio Optimizer Pro will produce the same result in less than one second.  "What if" analysis --  rapidly testing the impact on portfolio composition of changing input assumptions -- is therefore much more practical using Portfolio Optimizer Pro.

Active portfolio management statistics,  scenario comparisons, and leverage analysis are only available in the Pro version.

Licensing:   Portfolio Optimizer Pro included in the full version of the Finance Add-in for Excel bought under a private use/personal license is limited to optimizing a maximum of ten assets.  To optimize more than ten assets using Portfolio Optimizer Pro a corporate/commercial license is required for businesses (Commercial licensing enquiries) or a "Pro upgrade" license for private investors ("Pro upgrade" licensing enquiries).

Portfolio Optimizer Personal has no licensing limitations on the number of assets, but there are practical limitations due to the slower speed of the Personal version compared with the Pro version. 

Licensing summary

Portfolio Optimizer Personal has been tested with English versions of Microsoft Excel 2002 - 2007 on English versions of Microsoft Windows 2000, XP and Vista with English language regional settings. The Optimizer is not supported on non-English versions of Excel, on non-English Windows or on Windows with non-English regional settings. The Optimizer may well run on these un-tested platforms but no support can be provided where any issues are judged by Hoadley Trading & Investment Tools to be related to these non-English platforms.

Portfolio Optimizer Pro will run on versions of Microsoft Excel 2002 and above and on Widows 2000 and above with any regional settings.

The Hoadley Finance Add-in for Excel must be installed before using the Portfolio Optimizer.
 

The Portfolio Optimizer is free.  However it requires the full version of the Hoadley Finance Add-in for Excel as the optimizer makes extensive use of the volatility and VaRtools functions contained in the add-in. This software will not work with the trial version of the add-in.

Before you download the add-in or the Portfolio Optimizer note that:

View: Portfolio Optimizer tutorial.

View: Black-Litterman tutorial.

Download and purchase Finance Add-in .

Download the Hoadley Portfolio Optimizer (both Portfolio Optimizer Personal and Portfolio Optimizer Pro included).  If you have not purchased the Finance Add-in in the last 12 months you will be redirected to the secure purchase page.