Up
 

Data download &  analysis

Portfolio
optimization

Scenario comparisons
 

Active portfolio management statistics & risk attribution
 

Leverage & capital allocation
 

Transaction costs
 

Mean-CVaR optimization
 

Estimating
volatilities, correlations, & returns
 

Tax adjusted optimization

Risk-based asset allocation

Editions &
licensing

Software environment
 

Download Portfolio Optimizer

Hoadley Portfolio Optimizer
Portfolio Optimization Software

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:

Data download (or import) and analysis

Portfolio optimization

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

Historical trends for individual asset volatilities, betas, prices, and trends for correlations between pairs of assets can be viewed graphically to provide a picture of their stability over time.

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 historical price data from any source, such as Bloomberg providing that data can be placed in columns (one column per symbol) in any spreadsheet.  One button click will then import data from that external 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).

Efficient frontiers can be based on either of two models:

	Mean arithmetic returns:  This is the standard one-period Markowitz model.
	Mean geometric returns: A Compound Annual Growth Rate (CAGR) model which aims to maximize capital growth over the longer term. (Portfolio Optimizer Pro-edition only).

Both models have their strengths and weaknesses.  An explanatory spreadsheet which provides additional information and compares and contrasts efficient frontiers produced by both models is available for download.

Minimum and maximum weights can be specified for each asset, including negative weights if short positions are 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.

The exposure of each asset to one or more risk factors, such as the CAPM market beta, or the Fama-French three factor model Rm-Rf, SML and HML, can also be specified (factor exposure constraints are only available in the Portfolio Optimizer Pro-edition).

Minimum and maximum weight constraints can be specified by asset group, or by factor, as well as by asset.  So, for example, you could specify that no more than 15% of a portfolio should be in technology stocks, or the beta of each optimized portfolio must be between 0.9 and 1.1 ("factor exposure targeting").

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 and, with the Pro-edition, the portfolio which maximizes the geometric mean portfolio return are 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 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.

Similarly, if factor exposures, and optionally factor constraints, are specified by asset, an analysis is produced showing the total exposure of each portfolio on the efficient frontier to each factor. This provides an effective way of implementing factor "tilts" (eg towards the Fama-French value factor).

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 and risk statistics.

Active management statistics: The portfolio analytics include excess returns and risks decomposed into residual (alpha), active (active returns and tracking error), 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 each of 20 portfolios on the efficient frontier, 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.  Value at Risk (VaR) and Conditional Value at Risk (CVaR) are calculated for each leveraged/de-leveraged portfolio.  Active portfolio management statistics are also shown for a portfolio with a beta of one.

Risk-adjusted measures: Active management statistics are also shown on a risk adjusted basis using the M3 (M-cubed) methodology.  The M3 methodology uses a combination of the active portfolio, the passive benchmark and the risk free asset to produce a combined portfolio with a volatility equal to the benchmark volatility and with a tracking error equal to a user-specified tracking error target. The M3 analysis enables "normalized" comparisons of expected portfolio performance, and provides guidance for portfolio construction with tracking error targets.

Risk attribution:  A risk attribution analysis is produced for the optimal portfolio showing the contribution of each asset and, optionally, each asset grouping (eg asset class, industry, country) to overall portfolio volatility.  The analysis compares contribution to risk, marginal contribution to risk and percent contribution to risk with the allocated weights of assets and asset groups to facilitate the active management of portfolio risk.

Active management statistics and the risk attribution analysis are available in the Portfolio Optimizer Pro version of the Portfolio Optimizer.
 

One of the fundamental insights from Modern Portfolio Theory is that all investors should hold the same "risky" (optimal) portfolio, differing only in the amount of leverage applied (The Tobin separation theorem).

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.  By combining risk free borrowing and lending with the optimal portfolio an investor can select a point on the CAL -- the risk/reward trade-off --  commensurate with his/her tolerance for risk.

However, usually investors cannot borrow at the risk free rate. When borrowing is more expensive than the risk free rate 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 achieve returns in excess of the optimal portfolio return, there are more efficient asset allocation options available than simply leveraging the optimal portfolio.

The Hoadley Portfolio Optimizer produces a leverage analysis showing 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.

 

When revising or rebalancing an existing portfolio, transaction costs associated with moving from the existing asset allocation to the new one are usually ignored by standard portfolio optimizers.  The impact of transaction costs, however, can be significant in some situations.  Failure to consider transaction costs, particularly those which vary in proportion to the value of each asset traded in restructuring an existing portfolio, can lead to suboptimal asset and sector allocations.

Transaction costs cannot be simply calculated after an efficient frontier has been produced but must be incorporated into the optimization process itself.  Like asset volatilities, correlations and estimated returns, transaction costs directly impact the optimal asset weights for each portfolio on the efficient frontier.  Clearly as transaction costs increase it becomes progressively less optimal to move away from the current portfolio.

The Hoadley Portfolio Optimizer treats proportional transaction costs, such as brokerage and buy/sell spreads, as an integral part of the optimization process.  A default transaction cost percentage which applies to all assets/sectors, unless overridden by asset/sector-specific transaction costs percentages, is applied to the value of each asset that must be traded to move from the current portfolio to a portfolio on the efficient frontier.  The mean-variance optimization framework incorporates these proportional transaction costs into the production of the efficient frontier.

To highlight the impact of transaction costs, two efficient frontiers, including their optimal (tangency) portfolios, can be displayed simultaneously on the one efficient frontier chart. For instance, an efficient frontier ignoring transaction costs and an efficient frontier subject to transaction costs could be viewed together.  Results can also be compared in table form.

 

A number of researchers and practitioners have argued that the minimization of a portfolio's variance for a given portfolio return (ie Mean-Variance Optimization) may sometimes produce misleading results in terms of asset allocation given the unrealistic normal distribution and linear correlation assumptions upon which Mean-Variance Optimization (MVO) is based. They suggest that, in order to minimize portfolio risk effectively and to obtain a more optimal asset allocation, a measure of tail risk such as Conditional Value at Risk (CVaR) should be used in some situations.

Conditional Value-at-Risk (CVaR) -- also known as Expected Tail Loss (ETL) or Expected Shortfall (ES) -- measures the expected loss in the left tail of the returns distribution conditional on a particular Value at Risk (VaR) threshold being exceeded.

The Hoadley Portfolio Optimizer Mean-CVaR edition performs portfolio optimization using the Mean-CVaR model, rather than the standard Mean-Variance model.  When a normal distribution of asset returns and linear correlation is assumed, Mean-Variance and Mean-CVaR optimizations will produce the same asset allocations --  the efficient frontiers will be identical.  When these assumptions are relaxed, however, the two optimization models can lead to significantly different asset allocations.

Copulas are used by the Hoadley Mean-CVaR optimizer to define the distributions of each asset/asset class and their dependency structure. Copulas have a strong theoretical foundation, can be easily calibrated to market data (using the copula functions in the Finance Add-in for Excel), and provide great flexibility for the specification of asset distributions and dependencies.

 

The Hoadley Finance Add-in for Excel includes tools to help 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.

The Finance Add-in for Excel includes a number of tools to improve estimation accuracy of volatilities and correlations. These include exponentially weighted moving average (EWMA) volatility and correlation models (which are the default models in the Portfolio Optimizer), Bayesian shrinkage estimators using the Jorion or Ledoit-Wolf models, statistical factor models using principal component analysis (PCA), and fundamental factor models using either a single index model such as the CAPM or a multi-factor model such as the Fama French multiple factor models.

In situations where price histories differ in length, as may be the case when some of the assets have only recently been listed or when some funds are relatively new, then the Stambaugh methodology can be used to estimate volatilities and correlations without truncating data to match the asset with the shortest price history. See the  HoadleyCorrelStambaugh function for more information on this sophisticated estimator.

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 are more difficult to estimate than volatilities and correlations. Historical returns can provide quite robust estimates of future volatility and correlation but are usually very poor predictors of future returns. Models such as Bayesian shrinkage estimators can help improve estimates based solely on historical sample mean returns, and the Finance Add-in for Excel includes a Bayesian estimator function using the Jorion model.  But usually more advanced 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 asset class weightings and backs-out, using reverse optimization, the returns implied by the market.  These equilibrium expected returns -- the market neutral reference point -- are combined with user views about the absolute or relative performance of selected asset classes/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 Black-Litterman model achieves this diversification without the need to impose tight arbitrary constraints on asset weights during optimization.

The Hoadley Finance Add-in for Excel includes a full implementation of the Black-Litterman model. The Black-Litterman functions in the add-in can be used directly in user designed spreadsheets, or the Hoadley Black-Litterman Returns Estimator application can be used to simplify the process.

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.
 

The impact of taxes on efficient asset allocation for private investors is significant -- taxes really do matter. 

Incorporation of tax impacts into an efficient allocation framework, however, can be challenging without a clear methodology to follow and taxation is therefore often ignored.  The result is the construction of portfolios which fail to achieve their prime objective: the most efficient maximization of after-tax wealth.

The Hoadley Finance Add-in for Excel includes two functions for converting pre-tax returns, volatilities and market values into their after tax equivalents, for both taxable investments and investments held in a tax-deferred account, such as a US 401(k) retirement account. The functions take into account the multiple tax rates that can apply during the life of each asset (taxes on annual income and short term capital gains, taxes on dividends, taxes on long term capital gains realized at the end of the investment horizon etc) and make adjustments for embedded tax liabilities from unrealized capital gains at the start of the investment period.

The adjusted returns, volatilities, and values from the functions can then be used with the Hoadley Portfolio Optimizer to allocate assets on an after-tax basis.  The after tax asset weights from the portfolio optimizer would then be converted, again using the tax adjustment functions, back to pre-tax (market) weights for implementation.

A sample spreadsheet is available for download with the full version of the Finance Add-in for Excel which provides working examples for three common situations: allocation of assets for a new taxable portfolio (ie without existing unrealized capital gains);  allocation of assets in an existing taxable portfolio (ie with embedded tax liabilities attached to some assets at the start of the investment period); and allocation of assets or asset classes efficiently across taxable, tax-deferred and tax-exempt accounts taking into account legislative or other constraints on the values that can be held in the tax-advantaged accounts ("location planning").

 

Risk-based asset allocation strategies differ from traditional mean-variance asset allocation strategies in that they do not require estimates of future returns. Instead, asset allocations are based purely on the risk characteristics of the assets.

The Hoadley Portfolio Optimizer implements three risk-based asset allocation models, in additional to traditional mean variance optimization:

	Minimum variance
	Risk parity (equal risk contribution (ERC))
	Diversification maximization (measuring the impact of correlation on portfolio volatility)

Portfolio asset weights for each of these three risk-based allocation strategies, and the portfolio asset weights for the equivalent optimal MVO portfolio, are summarized in a single table together with the volatility, expected return, Sharpe ratio and measures of diversification for each portfolio. 

The risk-based asset allocation also includes a risk attribution analysis showing the contribution of each asset to overall portfolio risk.

These comparative schedules clearly highlight the key performance characteristics of each risk-based asset allocation strategy compared with the traditional MVO-based asset allocation approach.

 

There are two main editions of the Hoadley Portfolio Optimizer:  Portfolio Optimizer Personal, and Portfolio Optimizer Pro.  Both editions 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 Personal and Pro editions 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 fifty or sixty assets.

Portfolio Optimizer Pro uses the HoadleyEfficientFrontier function which implements the Markowitz critical line algorithm.  Excel solver is not used and optimization is very fast.  For example, producing an efficient frontier for 70 assets typically takes less than one second.  "What if" analysis --  rapidly testing the impact on portfolio composition of changing input assumptions -- is therefore more practical using Portfolio Optimizer Pro.

Active portfolio management statistics, risk attribution analysis,  scenario comparisons, leverage analysis, geometric mvo,  and risk-based asset allocation are only available in the Pro edition. Users of the Personal version can still produce these analyses using functions in the Finance Add-in for Excel.

Specialized editions: Two supplementary editions of the Portfolio Optimizer are also included in the download file:

	Portfolio Optimizer with Transaction Costs is the edition specifically designed to handle transaction costs when restructuring an existing portfolio.  It is based on the same technology as the Personal Edition. Details.
	Mean-CVaR Portfolio Optimizer uses the Mean-CVaR model for portfolio optimization rather than the Mean-Variance model.  This edition, which is based on the same technology as the Personal Edition, is suitable for the optimization of around 14 asset classes. Deails.

Licensing:   Portfolio Optimizer Pro included in the full version of the Finance Add-in for Excel bought under a private use/personal license uses the HoadleyEfficientFrontier function and 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 approved private investors ("Pro upgrade" licensing enquiries).

Portfolio Optimizer Personal, Portfolio Optimizer with Transaction Costs, and the Mean-CVar Portfolio Optimizer have no licensing limitations on the number of assets, but there are practical limitations due to the slower speed of these editions compared with the Pro edition.

The "personal" edition is used mainly by private investors.  The "pro", "transaction cost" and "Mean-CVaR" editions are aimed at professional asset managers and very active private investors.

Licensing summary

The Finance Add-in for Excel is required to run all editions of the Portfolio Optimizer. Pricing.

Portfolio Optimizer Personal, Portfolio Optimizer with Transaction Costs, and the Mean-CVar Optimizer have been tested with English versions of Microsoft Excel (32-bit and 64-bit) on English versions of Microsoft Windows 10 or later with English language regional settings. The "Personal" and "Transaction Cost" editions are not supported on non-English versions of Excel, on non-English Windows or on Windows with non-English regional settings. The Optimizers 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 requires Microsoft Excel (32-bit or 64-bit) running under Microsoft Windows and will run with English and non-English versions of Excel. 

For detailed systems requirements, including supported versions of Windows and Excel see systems requirements.

The Hoadley Finance Add-in for Excel is required for all editions of the Portfolio Optimizer..

 

All editions of the Portfolio Optimizer are included with the full version of the Hoadley Finance Add-in for Excel.  Pricing and licensing information for the Finance Add-in for Excel can be found here.

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

	To use this (or any)  Portfolio Optimizer effectively, you need an understanding of the Modern Portfolio Theory  (MPT) and the Capital Asset Pricing Model (CAPM) principles (but not the mathematics) upon which the Hoadley Portfolio Optimizer is based.
	By downloading the add-in and the Portfolio Optimizer you signify your assent to these Terms of Use.  In particular note that the add-in and Portfolio Optimizer software are for your private use only. (Contact Peter Hoadley for commercial license enquiries.)
	The analyze component of the add-in extracts data from Yahoo finance (and can be bypassed if users have their own data sources). Note that no guarantee can be given that stock or other price data will be provided by Yahoo or, if it is currently available, that it will continue to be available from the Yahoo site in the future. No guarantee can be given that Yahoo data will remain free of charge, or that Yahoo will not change the format of its data without notice in the future.
	The Portfolio Optimizer is made available to experienced Excel users who have a good understanding of how to install add-ins, and use spreadsheets.  Technical assistance will therefore not be provided in the use of Excel per se.

View: Portfolio optimizer tutorial

View: Black-Litterman tutorial.

Download and purchase Hoadley Finance Add-in for Excel . The Finance add-in for Excel is required to run the Portfolio Optimizer.

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