Returns the historical/theoretical value at risk (VaR).
Syntax
NxVaR(X ,C, Method, Distribution)
 X
 is the portfolio rate of returns data series (a onedimensional array of cells (e.g., rows or columns)).
 C
 is the statistical confidence level for calculating the VaR. If missing, a confidence value of 95% is assumed.
 Method
 is an integer switch to select the method for calculating the VaR: (0 = Historical (default), 1 = KDE, 2 = Theoretical).
Value Description 0 Historical (default). 1 Kernel Density Estimation (KDE). 2 Theoretical.  Distribution
 is an integer switch to select the underlying theoretical distribution: (0 = Gaussian (default), 1 = Log Normal).
Value Distribution 0 Gaussian Distribution (default). 1 LogNormal Distribution.
Status
The NxVaR function is available starting with NumXL version 1.68 CAMEL.
Remarks
 By definition, all values in the input data set (i.e., X) must be greater than 1.0.
 The input data series may include missing values (e.g., #N/A, #VALUE!, #NUM!, empty cell), but they will not be included in the calculations.
 Value at Risk (VaR) is a statistic that quantifies the extent of possible financial losses within a firm, portfolio, or position over a specific time frame.
 There are three main ways of computing VaR.
 The first is the historical method, which looks at one's prior returns history. In this method, VaR is determined by taking the returns belonging to the lowest quintile of the series (identified by the confidence level).
 The second is the variancecovariance method. Instead, this method assumes that gains and losses are normally distributed (or logNormal).
 A final approach to VaR is to conduct a Monte Carlo simulation.
 The VaR value for the boundary conditions is as follows:
 For the zero (0) confidence level, the VaR is infinite.
 For the 100% confidence level, the VaR is 1.0.
 Despite being widely used, VAR suffers from a few drawbacks:
 VaR does not indicate the size of the loss associated with the tail of the probability distribution out of the confidence level.
 VaR is not additive, so VaR figures of components of a portfolio do not add to the VaR of the overall portfolio because this measure does not take correlations into account.
 VaR calculated with different methods may differ significantly.
Examples
Example 1:


Formula  Description (Result) 

=NxVaR(\$B\$2:\$B\$14,0.95, 0)  VaR (historical) (0.07844) 
=NxVaR(\$B\$2:\$B\$14,0.95, 1)  VaR (Normal) (0.07844) 
=NxVaR(\$B\$2:\$B\$14,0.95, 2)  VaR (LogNormal) (0.07809) 
Files Examples
Related Links
References
 Hamilton, J .D.; Time Series Analysis, Princeton University Press (1994), ISBN 0691042896
 Tsay, Ruey S.; Analysis of Financial Time Series John Wiley & SONS. (2005), ISBN 0471690740
Comments
Please sign in to leave a comment.