Returns the historical/theoretical value at risk (VaR).
NxVaR(X ,C, Method, Distribution)
- is the portfolio rate of returns data series (a one-dimensional array of cells (e.g., rows or columns)).
- is the statistical confidence level for calculating the VaR. If missing, a confidence value of 95% is assumed.
- 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.
- is an integer switch to select the underlying theoretical distribution: (0 = Gaussian (default), 1 = Log Normal).
Value Distribution 0 Gaussian Distribution (default). 1 Log-Normal Distribution.
The NxVaR function is available starting with NumXL version 1.68 CAMEL.
- 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 variance-covariance 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.
|=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 (Log-Normal) (-0.07809)|