

Hoadley Portfolio Optimizer

Overview 
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 Excelbased application, which is not password protected, performs two key functions:
Data download (or import) and analysis
Portfolio
optimization
Data Download (or Import) and Analysis 
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
RSquared: 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.
Portfolio Optimization 
The portfolio optimization component uses meanvariance 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 oneperiod Markowitz model. 

Mean geometric returns: This is a multiperiod model which aims to maximize capital growth over the longer term. (Portfolio Optimizer Proedition 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 userdefined 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 and, with the Proedition, 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 usersupplied 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 tradeoff 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 userdefined 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.
Scenario comparisons ("Pro" edition only) 
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.
Active portfolio management analytics and risk attribution ("Pro" edition only) 
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 RSquared; the Sharpe ratio; residual and active information ratios, and MSquared (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/deleveraging 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/deleveraged portfolio. Active portfolio management statistics are also shown for a portfolio with a beta of one.
Riskadjusted measures: Active management statistics are also shown on a risk adjusted basis using the M3 (Mcubed) 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 userspecified 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.
Leverage & capital allocation ("Pro" edition only) 
One of the fundamental insights from the Capital Asset Pricing Model (CAPM) is that all investors should hold the same "risky" (optimal) portfolio, differing only in the amount of leverage applied.
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 tradeoff  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.
Portfolio Restructuring and Transaction Costs 
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/sectorspecific 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 meanvariance 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.
MeanCVaR Portfolio Optimization 
A number of researchers and practitioners have argued that the minimization of a portfolio's variance for a given portfolio return (ie MeanVariance Optimization) may sometimes produce misleading results in terms of asset allocation given the unrealistic normal distribution and linear correlation assumptions upon which MeanVariance 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 ValueatRisk (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 MeanCVaR edition performs portfolio optimization using the MeanCVaR model, rather than the standard MeanVariance model. When a normal distribution of asset returns and linear correlation is assumed, MeanVariance and MeanCVaR 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
MeanCVaR 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 Addin for Excel), and provide great
flexibility for the specification of asset distributions and dependencies.
Estimating Volatilities, Correlations and Returns 
The Hoadley Finance Addin 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 Addin 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 Addin 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 LedoitWolf models, and principal component analysis (PCA).
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 Addin 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 BlackLitterman Bayesian asset allocation model. The BlackLitterman model was developed by Fischer Black (of BlackScholes fame) and Robert Litterman at Goldman Sachs in the early 1990s.
The BlackLitterman model starts with market asset class weightings and backsout, 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 BlackLitterman 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 BlackLitterman model achieves this diversification without the need to impose tight arbitrary constraints on asset weights during optimization.
The Hoadley Finance Addin for Excel includes a full implementation of the BlackLitterman model. The BlackLitterman functions in the addin can be used directly in user designed spreadsheets, or the Hoadley BlackLitterman Returns Estimator application can be used to simplify the process.
View:
BlackLitterman
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.
Taxadjusted portfolio optimization 
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 aftertax wealth.
The Hoadley Finance Addin for Excel includes two functions for converting pretax returns, volatilities and market values into their after tax equivalents, for both taxable investments and investments held in a taxdeferred 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 aftertax basis. The after tax asset weights from the portfolio optimizer would then be converted, again using the tax adjustment functions, back to pretax (market) weights for implementation.
A sample
spreadsheet is available for download with the full version of the Finance
Addin 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, taxdeferred
and taxexempt accounts taking into account legislative or other constraints on
the values that can be held in the taxadvantaged accounts ("location
planning").
Riskbased Asset Allocation ("Pro" edition only) 
Riskbased asset allocation strategies differ from traditional meanvariance 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 riskbased asset allocation models, in additional to traditional mean variance optimization:
Minimum variance 

Risk parity (equal risk contribution (ERC)) 

Most diversified portfolio (MDP). 
Portfolio asset weights for each of these three riskbased 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 diversification ratio for each portfolio.
The riskbased 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 riskbased asset allocation strategy
compared with the traditional MVObased asset allocation approach.
Portfolio Optimizer Editions and Licensing 
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 addin 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 riskbased asset allocation are only available in the Pro edition. Users of the Personal version can still produce these analyses using functions in the Finance Addin 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. 

MeanCVaR Portfolio Optimizer uses the MeanCVaR model for portfolio optimization rather than the MeanVariance 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 Addin 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 MeanCVar 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 "MeanCVaR" editions are aimed at professional asset managers and very active private investors.
Licensing
summary
The Finance Addin for Excel is required to run all
editions of the Portfolio Optimizer.
Pricing.
Software Environment 
Portfolio Optimizer Personal, Portfolio Optimizer with Transaction Costs, and the MeanCVar Optimizer have been tested with English versions of Microsoft Excel for Windows 2003 and above (32bit and 64bit) on English versions of Microsoft Windows XP or later with English language regional settings. The "Personal" and "Transaction Cost" editions are not supported on nonEnglish versions of Excel, on nonEnglish Windows or on Windows with nonEnglish regional settings. The Optimizers may well run on these untested platforms but no support can be provided where any issues are judged by Hoadley Trading & Investment Tools to be related to these nonEnglish platforms.
Portfolio Optimizer Pro will run on versions of Microsoft Excel for Windows 2003 and above (32bit and 64bit) on Windows XP or later, including Windows 7 and Windows 8, 32bit and 64 bit editions, with any regional settings.
The Hoadley Finance Addin for Excel must be installed
before using the Portfolio Optimizer.
Download the Hoadley Portfolio Optimizer 
Both versions of the Portfolio Optimizer are included with the full version of the Hoadley Finance Addin for Excel. Pricing and licensing information for the Finance Addin for Excel can be found here.
Before you download the addin 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 addin and the Portfolio Optimizer you signify your assent to these Terms of Use. In particular note that the addin and Portfolio Optimizer software are for your private use only. (Contact Peter Hoadley for commercial license enquiries.) 

The analyze component of the addin 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 addins, and use spreadsheets. Technical assistance will therefore not be provided in the use of Excel per se. 
View: Portfolio Optimizer tutorial.
View: BlackLitterman tutorial.
Download and purchase Hoadley Finance Addin for Excel . The Finance addin 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 Addin in the last 12 months you will be redirected to the secure purchase page.