Up

Excel add-in

Download VaRtools additional samples
 

Value at Risk (VaR): VaRtools

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 confidence interval 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.  The size of the risks assessed by answering questions like:

will be smaller in absolute terms for smaller entities and private traders.  But the answers 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 tools support 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 (options add-in)  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 include:

Calculation of VaR for portfolios containing instruments linearly dependent on market variables (like stocks, cash-flow mapped bonds, foreign exchange exposures).
 

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.

Either the partial "delta-gamma-theta" Taylor expansion approximation can be used to measure option risk, or the simulation component can provide the correlated simulated prices required for the full portfolio revaluation method. 

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 calculation and the positions can be either in local (foreign) or home currencies.
 

Functions are provided to simplify the management of medium to large portfolios by enabling the portfolios to be split into sub portfolios (eg equities, currencies) which can then be represented by a single entity ("sub portfolio") rather than at the individual asset level when calculating VaR.

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. 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 calculation.
 

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.

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 valuation 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.

To ensure that externally provided data is internally consistent VaRtools provides functions to check matrix consistency in terms of being positive definite and positive semi-definite.
 

VaRtools is part of the full version of the Hoadley options add-in which can be purchased and downloaded from this site. The add-in contains sample worksheets demonstrating the use of each of the functions and components.

In addition another spreadsheet, VaRtools samples, can be downloaded from this site.  This spreadsheet contains five worksheets illustrating additional points, such as how to handle foreign currency exposures and how to conduct 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.
 

Before you download the add-in (which contains VaRtools) or the additional samples spreadsheet note that by downloading either of these products you signify your assent to these Terms of Use.  In particular note that the add-in is for your private use only. (Contact Peter Hoadley for commercial license enquiries.)

Note:

Download and purchase Finance Add-in  (which contains VaRtools and the essential VaR examples and samples)

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.