NxCVaR - Calculates the Conditional Value at Risk

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 one-dimensional 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	Log-Normal Distribution.

Remarks

1. By definition, all values in the input data set (i.e., X) must be greater than -1.0.
2. 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.
3. 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.
4. 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}{1-c}\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.
5. There are three main ways of computing CVaR:
   1. 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).
   2. The second is the variance-covariance 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}(1-c))}{1-c}$$ 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.
      Log-Normal Distribution $$\textrm{CVaR} = 1-\exp \left(\mu +{\frac {\sigma ^{2}}{2}}\right){\frac {\Phi \left(\Phi ^{-1}(1-c )-\sigma \right)}{1-c }}$$ Where:
      • $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.
   3. A final approach to VaR is to conduct a Monte Carlo simulation.
6. Conditional value at risk (CVaR) is also called expected shortfall, average value at risk (VaR), expected tail loss (ETL), and super-quantile.
7. The CVaR value of the boundary conditions is as follows:
   1. For the zero(0) confidence level, the CVaR is equal to the mean.
   2. For the 100% confidence level, the CVaR and the VaR equal -1.0.

Examples

Example 1:

Related Links

• Wikipedia - Expected shortfall
• Investopedia - Conditional Value at Risk (CVaR)

References

• Hamilton, J .D.; Time Series Analysis, Princeton University Press (1994), ISBN 0-691-04289-6.
• Tsay, Ruey S.; Analysis of Financial Time Series John Wiley & SONS. (2005), ISBN 0-471-690740.