Returns the historical/theoretical conditional value at risk (CVaR).
Syntax
NxCVaR(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 NxCVaR 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.
 CVaR is a risk assessment measure that quantifies the amount of tail risk an investment portfolio has. CVaR quantifies the expected losses that occur beyond the VaR breakpoint.
 CVaR is derived by taking a weighted average of the losses in the tail of the distribution of possible returns beyond the value at risk (VaR) cutoff point.
$$\textrm{CVaR}=\textrm{CVaR}=E\left[x  x< \textrm{VaR} \right ]$$ $$\textrm{CVaR}=\frac{1}{1c}\times\int_{1}^{\textrm{VaR}}{x.p(x).dx}$$ Where: $c$ is the statistical confidence level for calculating the VaR (e.g., 95%).
 $p(.)$ is the probability density function.
 $\textrm{VaR}$ is the value at risk (VaR) level for the confidence level c.
 There are three main ways of computing CVaR:
 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).
Normal (Gaussian) distribution: $$\textrm{CVaR} = \mu + \sigma \frac {\varphi (\Phi ^{1}(1c))}{1c}$$ Where: $c$ is the statistical confidence level for calculating the VaR (e.g., 95%).
 $\varphi(.)$ is the standard normal probability density function (PDF).
 $\Phi(.)$ s the standard normal cumulative density function (CDF).
 $\Phi^{1}(.)$ is the standard inverse normal CDF or quantile.
 $\mu$ is the mean of the gaussian distribution.
 $\sigma$ is the standard deviation of the gaussian distribution.
 $c$ is the statistical confidence level for calculating the VaR (e.g., 95%).
 $\Phi(.)$ is the standard normal CDF.
 $\Phi^{1}(.)$ is the standard inverse normal CDF or quantile.
 $\mu$ is the mean of the gaussian distribution.
 $\sigma$ is the standard deviation of the gaussian distribution.
 A final approach to VaR is to conduct a Monte Carlo simulation.
 Conditional value at risk (CVaR) is also called expected shortfall, average value at risk (VaR), expected tail loss (ETL), and superquantile.
 The CVaR value of the boundary conditions is as follows:
 For the zero(0) confidence level, the CVaR is equal to the mean.
 For the 100% confidence level, the CVaR and the VaR equal 1.0.
Examples
Example 1:


Formula  Description (Result) 

=NxCVaR(\$B\$2:\$B\$14,0.95, 0)  CVaR (historical) (0.07844) 
=NxCVaR(\$B\$2:\$B\$14,0.95, 1)  CVaR (Normal) (0.07844) 
=NxCVaR(\$B\$2:\$B\$14,0.95, 2)  CVaR (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.