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 portfolio analysis phase (which is optional) will automatically retrieve historic stock,
fund, and
index prices from Yahoo (most exchanges supported) for a complete stock 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) is 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 the context of
the efficient frontier.
If assets have been assigned to user-defined groups, an
analysis is also 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.
|
Active portfolio
management statistics ("pro" version only) |
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). The residual covariance
information could be used to optimize the portfolio by residual risk and
return rather than by total risk and return.
Active management statistics are only available in
Portfolio Optimizer Pro version of the Portfolio
Optimizer.
|
Estimating volatilities, correlations and returns |
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 easily be 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.
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.
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.
The
active portfolio management statistics described above 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.
|
Download the Hoadley 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:
 |
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 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
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.
|
