

Value at Risk (VaR)
Software

Overview 
The Value at Risk (VaR)
approach to risk management aims to consolidate in a consistent way, at the
organization or entity level, the risks inherent in a portfolio of various
classes of financial instruments. The results are expressed as a single
number  the VaR  in terms of the of maximum expected loss, the
significance level of the loss (eg 1%) and the number of days in the risk period
(eg five days).
Despite VaR being widely accepted by practitioners, academics and regulators as a key component of bestpractice risk management, VaR products available on the market tend to be suitable only for financial institutions and other large organizations due to their complexity and high cost.
Understanding and managing risk is, however, just as important for private traders, investors, and small to mediumsize organizations as it is for very large organizations. Organizations and investors typically need answers to questions such as:
What is the maximum loss that we can
be 95% confident won't be exceeded over the next five days on our combined
stock, bonds and options positions? 

What is our firm's exposure to
foreign currency risk if we remain unhedged for the next ten days? 

If things get really bad over the next five days, what is the size of the expected loss? 
The answers to these questions will be smaller in absolute terms for smaller entities and private traders. But they are proportionately as critical as they are for large organizations for managing risk.
VaRtools aims to place the analysis and management of risk using the VaR framework within the reach of private individuals and small to medium organizations as well as the traditional large company users of VaR. The software supports the industrystandard approach, RiskMetrics 1996, developed by J.P. Morgan.
VaRtools, which is
included in the full version of the Hoadley Finance
Addin for Excel provides a powerful set of tools for Value at Risk
calculation, and for assembling and verifying the information base (correlation
matrices, cash flow maps etc) which are used as input to the process.
Key Features 
VaRtools comprises a set of Excelbased tools for the calculation of the two most widely used VaR measures:
Value at Risk (VaR): The
maximum loss that will not be exceeded with a given probability (significance
level) during a given number of days. For example, "there is
only a 5% chance that our company's losses will exceed $20M over the next
five days". This is the "classic" VaR measure. VaR does not provide any
information about how bad the losses might be if the VaR level is
exceeded. 

Conditional Value at Risk (CVaR): The average size of the loss that can be expected when it exceeds the VaR level. It is the loss that can be expected in the worst n% of cases over a given number of days. CVaR, also known as Expected Shortfall and Expected Tail Loss (ETL), provides an answer to the question "when things get bad (ie the VaR level is exceeded) then what is our expected loss?". 
Three methodologies are available to compute VaR: Analytic (Parametric), Filtered Historical Simulation (FHS), and Monte Carlo Simulation.
Key features in
VaRtools include:
Analytic VaR, CVaR, & Component VaR (Parametric model) 
The analytic VaR function calculates VaR and CVaR for portfolios containing instruments linearly dependent on market variables (like stocks, cashflow mapped bonds, foreign exchange exposures). The analytic VaR model is also referred to as the parametric VaR model or the Linear model.
VaRTools also includes a function to decompose total portfolio VaR and CVaR for linear portfolios into the VaR and CVaR contributed by each component. "Component VaR/CVaR" shows the contribution of each individual asset to total portfolio risk taking into account the asset's volatility and it's correlation with the total portfolio. Individual component VaRs sum to the total portfolio VaR.
Marginal VaR and CVaR (the impact on total VaR/CVaR
of increasing or decreasing individual asset positions) and VaR beta (a
measure of the relative sensitivity to changes in portfolio VaR) are
also calculated for each asset in the portfolio.
Monte Carlo and copula simulation of VaR and CVaR 
For portfolios containing both linear and nonlinear assets (eg options) . The prices for any number of instruments can be simultaneously simulated in a correlated fashion using the Cholesky decomposition of the covariance between individual assets.
Alternatively copulas can be used to generate the prices. Copulas provide much greater flexibility than "traditional" Monte Carlo Simulation in that they allow the dependency between assets to be based on structures other than simple linear correlation. They also allow the distribution of individual assets to be based on nonnormal distributions. Lower tail dependence often found in financial returns can be taken into account using copulas  particularly important for VaR with its focus on extreme losses.
The Finance Addin for Excel includes a comprehensive set of copula functions which are tightly integrated with the VaR simulation component.
VaR on options positions can be calculated by full portfolio revaluation ("exact revaluation") or, as is usually the case, by risk factor mapping ("partial revaluation"). In the latter case the Taylor deltagammatheta approximation is used. The Finance Addin for Excel includes option valuation functions for the calculation of the hedge ratios (delta, gamma, theta) required for risk factor mapping.
For both full and partial revaluation, volatility risk can also be included in the VaR calculation as an additional risk factor.
VaR for bonds and other similar instruments can be handled either by the portfolio weighted duration approximation approach, or by cash flow mapping (see below).
Any number of
positions can be included in the VaR and CVaR calculations and the positions can
be either in local (foreign) or home currencies.
Filtered Historical Simulation (FHS) of VaR and CVaR 
As an alternative to Monte Carlo simulation, the Filtered Historical Simulation (FHS) model can be selected for portfolios containing both linear and nonlinear assets. FHS, a nonparametric methodology, makes no assumptions about the distribution of asset returns. Future daily price movements over the VaR horizon are simulated using historical returns directly, but which have been volatilityadjusted, or scaled, by their daily GARCH volatility estimates to more closely reflect current market conditions.
The GARCH volatility model
is used for returns scaling by the FHS component, and the Finance Addin
for Excel includes a function to estimate the GARCH "parameters" for
each asset in the portfolio using the maximum likelihood method.
VaR Simulator application 
The three types of simulation  Monte Carlo, copula and FHS  can be implemented in simple VBA modules (multiple examples provided) or by using the Hoadley VaR Simulator application. The VaR simulator automates the production of VaR and CVaR using a simple form filling and pointandclick interface and no VBA programming is required
An overview of the VaR Simulator can be found
here.
VaR & CVaR aggregation 
To simplify the management of medium to large portfolios, portfolios can be split into sub portfolios (eg equities, currencies, fixed interest) which can then be represented by a single entity ("sub portfolio") rather than at the individual asset level when calculating VaR and CVaR.
Functions are provided to aggregate linear standalone VaR and CVaR from sub portfolios, and to split the aggregated VaR or CVar into components to highlight each subportfolio's contribution to total risk.
Principal component analysis (PCA), included in the Finance Addin for Excel, can also be used to improve the stability of results by limiting the number of factors used in representing sub portfolios to those designated as significant "market" factors.
A sample spreadsheet which
illustrates both simple portfolio aggregation and portfolio aggregation using PCA
is available for download (see below).
Cash flow mapping: bonds, futures 
Any number of future cash flows from bonds, loans or other instruments can be mapped to multiple userspecified time vertices ("standard maturities"). Quadratic interpolation is used to ensure that both the present values of the cash flows and their market risks are preserved. The resultant cash flow maps can then be treated as a series of linear instruments for VaR and CVaR calculations.
Cashflow mapping is also commonly used
to map futures or forwards to constant maturity price series.
Correlation and covariance matrix construction 
VaRtools provides a set of functions for creating correlation and covariance matrices from historic price data. Unlike the simple Excel corr and covar functions the VaRtools functions create an entire matrix with one function call and without the need to calculate asset returns from prices.
Both correlation and covariance matrices can be produced using either the equally weighted model, or the EWMA model (as per the RiskMetrics datasets). Functions are included to convert correlation matrices to covariance matrices, and vice versa.
Price histories which differ in length: For situations where
assets have price histories of different lengths (eg a new fund is to be included
in the analysis) then correlations and volatilities can be estimated using the Stambaugh method. Instead of the common unsatisfactory approach of
truncating price histories to match the asset with the shortest history  and
therefore discarding valuable information  the full history of all
assets is utilized, and the data for assets with shorter histories is
"backfilled" by a sophisticated regression/maximumlikelihood technique.
Currency rebasing 
Functions are provided to
rebase volatilities and correlation matrices from one currency to another.
All volatilities, or an entire correlation matrix, can be rebased with one
function call. Correlations between currency and noncurrency risk factors
are rebased as well as correlations between currencies.
Sources of data 
The key data, apart from positionspecific data, required for VaR calculations are volatility and correlation matrices, or the equivalent covariance matrices. These can either be constructed from historic price data (eg from Yahoo or any other source) using the addin's volatility and matrix tools, or alternatively can be acquired in "ready to use" form from external sources.
For example J.P. Morgan's RiskMetrics datasets, which contain volatility and correlation data for a large number of asset classes, world indices and currencies, can be used.
Volatilities and
correlations from external sources like RiskMetrics (which is based in US$) can
be rebased into any other currency.
Samples Spreadsheet 
VaRtools is part of the full version of the Hoadley Finance Addin for Excel. The addin contains sample worksheets demonstrating the use of each of the functions and components.
An additional spreadsheet, VaRtools samples, can also be downloaded from this site. This spreadsheet contains nine worksheets illustrating additional points, such as handling foreign currency exposures and hedging, managing large numbers of assets using sub portfolios and aggregation, Monte Carlo Simulation vs Filtered Historical Simulation (FHS) vs Copula simulation, cashflow mapping, and and simple stress testing.
It also contains several
examples designed to demonstrate clearly that the various VaRtools
functions and components are working as expected. For example, the calculation of
VaR on a portfolio of options using the deltagammatheta approximation is
compared with calculating VaR on the same portfolio using full revaluation.
The examples are designed to give users confidence in the results produced by
VaRtools.
Download the VaRtools Additional Samples 
By downloading the Finance Addin for Excel you signify your assent to these Terms of Use. In particular note that the Finance Addin for Excel is for your private use only. (Contact Hoadley Trading & Investment Tools for corporate/commercial license enquiries.)
Note:
The samples spreadsheet will not
function unless the full version of the Hoadley Finance Addin for Excel is installed. 

The VaRtools samples spreadsheet is designed to supplement the examples in the addin's samples spreadsheet, not to replace them. 
Download and purchase Finance Addin (which contains VaRtools and the essential VaR examples and samples) for your own private, nonbusiness use. Corporate/commercial enquiries.
Download the additional VaRtools samples. If you have not purchased the Excel options addin in the last 12 months you will be redirected to the secure purchase page.