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.  Organizations and investors typically need answers to questions such as: 

	What is the maximum loss that we can be 95% certain 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 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  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 (confidence interval) 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?".

Key features in VaRtools include:

The covariance VaR function calculates VaR and CVaR for portfolios containing instruments linearly dependent on market variables (like stocks, cash-flow mapped bonds, foreign exchange exposures).

VaRTools also includes a function to decompose total portfolio VaR for linear portfolios into the VaR contributed by each component. "Component VaR" shows the contribution of each individual asset to total portfolio VaR 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 (the impact on total VaR 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.

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 and CVar calculations 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 and CVaR calculations.
 

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.
 

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