Up

Excel add-in

Download VaRtools additional samples
 

Value at Risk (VaR) Software
"VaRtools" Excel Add-in

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 best-practice 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 medium-size 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 industry-standard approach, RiskMetrics 1996, developed by J.P. Morgan.

VaRtools, which is included in the full version of the Hoadley Finance Add-in 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.
 

VaRtools comprises a set of Excel-based 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:

The analytic VaR function calculates VaR and CVaR for portfolios containing instruments linearly dependent on market variables (like stocks, cash-flow 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.
 

For portfolios containing both linear and non-linear 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 non-normal 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 Add-in 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 delta-gamma-theta approximation is used.  The Finance Add-in 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.
 

As an alternative to Monte Carlo simulation, the Filtered Historical Simulation (FHS) model can be selected for portfolios containing both linear and non-linear assets. FHS, a non-parametric 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 volatility-adjusted, 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 Add-in for Excel includes a function to estimate the GARCH "parameters" for each asset in the portfolio using the maximum likelihood method.
 

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 point-and-click interface and no VBA programming is required 

An overview of the VaR Simulator can be found here.
 

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 stand-alone VaR and CVaR  from sub portfolios, and to split the aggregated VaR or CVar into components to highlight each sub-portfolio's contribution to total risk.

Principal component analysis (PCA), included in the Finance Add-in 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).
 

Any number of future cash flows from bonds, loans or other instruments can be mapped to multiple user-specified 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.
 

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/maximum-likelihood technique.
 

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 non-currency risk factors are rebased as well as correlations between currencies.
 

The key data, apart from position-specific 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 add-in'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.
 

VaRtools is part of the full version of the Hoadley Finance Add-in for Excel.  The add-in 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 delta-gamma-theta 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.
 

By downloading the Finance Add-in for Excel you signify your assent to these Terms of Use.  In particular note that the Finance Add-in 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 Add-in for Excel is installed.
	The VaRtools samples spreadsheet is designed to supplement the examples in the add-in's samples spreadsheet, not to replace them.

Download and purchase Finance Add-in  (which contains VaRtools and the essential VaR examples and samples) for your own private, non-business use.  Corporate/commercial enquiries.

Download the additional VaRtools samples. If you have not purchased the Excel options add-in in the last 12 months you will be redirected to the secure purchase page.