A risk manager asks a deceptively simple question: how far can this portfolio’s return fall on a bad day? Value-at-Risk identifies a cutoff in the left tail of the return distribution. Expected Shortfall asks the harder—and often more useful—question: if returns cross that cutoff, what is the average shortfall?
Returns and P&L appear in their natural signed form: bad outcomes are negative and sit in the left tail. Accordingly, VaR expressed as a return is the negative left-tail cutoff. Only a reported loss percentage or monetary VaR reverses the sign to show a positive loss magnitude. Thus, a 5th-percentile return of −2% is the signed 95% VaR return; on a ₹100 crore portfolio, its reported monetary VaR is ₹2 crore. VaR is not a maximum possible loss.
1What VaR Answers
Let \(R\) denote the portfolio return over a chosen horizon. At confidence level \(c\), signed return VaR is the lower \(1-c\) quantile. Monetary VaR is obtained by reversing that sign and multiplying by portfolio value:
A complete VaR statement always contains four ingredients: amount, confidence level, holding period and currency. “₹2 crore” alone is not a risk measure; “₹2 crore at 99% confidence over one trading day” is.
2A Short History of VaR
Quantile-based loss limits have older roots, but VaR became a common language when large trading organisations needed one comparable number across desks, instruments and locations.
Large banks develop portfolio-wide measures to consolidate market exposures across risk factors.
J.P. Morgan publishes a transparent methodology and market data framework, accelerating industry adoption.
The framework becomes a widely used reference for variance–covariance market-risk measurement.
Supervisors connect model exceptions to a green, yellow and red traffic-light regime.
The Basel Committee shifts internal-model capital from VaR toward stressed ES with liquidity horizons.
Big idea: VaR succeeded because it compressed many positions into a comparable risk threshold. Its strength—one number—is also its weakness.
3Three Ways to Estimate VaR
Historical simulation
Replay observed risk-factor moves on today’s portfolio. Few distributional assumptions; fully dependent on the chosen history.
Parametric
Assume a distribution and estimate its parameters. Fast and transparent; fragile when tails or nonlinear positions violate the assumptions.
Monte Carlo
Simulate many joint scenarios from a chosen model and revalue the portfolio. Flexible, but computationally and model intensive.
| Question | Historical | Parametric | Monte Carlo |
|---|---|---|---|
| Distribution assumed? | No explicit family | Yes | Yes, through scenario model |
| Handles options? | Yes, with full revaluation | Only with approximations | Yes, with full revaluation |
| Speed | Medium | Fast | Slowest |
| Main vulnerability | History may not repeat | Wrong distribution/covariance | Wrong model disguised by precision |
4Historical Simulation VaR
Unlike Parametric VaR, Historical VaR assumes no particular distribution family for returns. It uses the empirical distribution created by actual historical observations—commonly 250 or 500 trading days—and assumes that this history is informative about tomorrow’s risk.
4.1 · Step-by-step calculation
Step 1 · Calculate historical returns
For \(N+1\) prices \(P_0,P_1,\ldots,P_N\), calculate either simple or continuously compounded returns:
Use one return definition consistently across the sample and the portfolio valuation process.
Step 2 · Create simulated gains and losses
For a linear single-asset position with current value \(V_0\), the scenario P&L is:
For options and other nonlinear instruments, apply each historical risk-factor shock to today’s market inputs and fully revalue the position. Simply multiplying value by return is not sufficient for a materially nonlinear payoff.
Step 3 · Order the outcomes
Sort the scenario returns or P&Ls in ascending order, so that the most negative outcome is first:
Step 4 · Locate the percentile rank
At confidence \(c=1-\alpha\), the left-tail probability is \(\alpha\). Under the common nearest-rank convention, the ascending-order cutoff is:
Quantile methods differ across textbooks, spreadsheets and risk systems. Some use a discrete order statistic; others linearly interpolate between adjacent observations when the target position is not an integer. The method should be chosen in advance and applied consistently in calculation and backtesting.
Step 5 · Read signed-return and monetary VaR
The historical signed-return VaR is the negative left-tail observation. Reported monetary VaR reverses the sign:
4.2 · Concrete index cutoffs
With \(N=500\) observations, the nearest-rank cutoff identifies:
| Confidence \((1-\alpha)\) | Tail \((\alpha)\) | Rank \((\alpha\times500)\) | Observation |
|---|---|---|---|
| 90% | 10% | 50 | 50th-worst return or P&L |
| 95% | 5% | 25 | 25th-worst return or P&L |
| 99% | 1% | 5 | 5th-worst return or P&L |
With 250 observations at 99% confidence, \(\alpha N=2.5\). Depending on the documented quantile convention, the cutoff may use an adjacent order statistic or interpolation between neighbouring observations. Either way, the result depends on only a few extreme days and is therefore statistically noisy.
4.3 · Multi-asset historical simulation
For \(M\) linear assets with portfolio-weight vector \(\mathbf w\) and the aligned vector of asset returns \(\mathbf R_t\) on historical day \(t\):
Sort the simulated portfolio outcomes, not each asset separately. This preserves the cross-asset co-movement that occurred on each historical date. For nonlinear positions, shock all relevant market factors together and fully revalue the entire portfolio before sorting.
4.4 · Historical Expected Shortfall
If \(\widehat q_{\alpha}\) is the empirical left-tail return cutoff, Historical ES averages all returns at or below it and then reports a positive monetary magnitude:
4.5 · Parametric versus Historical VaR
| Attribute | Parametric | Historical simulation |
|---|---|---|
| Distribution | Assumes a chosen family, often normal | Uses the empirical return distribution |
| Skewness and fat tails | Captured only if the chosen model includes them | Preserves those present in the selected history |
| Nonlinear instruments | Requires an approximation or richer parametric model | Can handle them through full revaluation |
| Data requirement | Estimated parameters such as mean and covariance | Complete, aligned historical risk-factor series |
| Main limitation | Model and distribution misspecification | The selected past may not represent future stress |
Strength: Historical VaR preserves observed skewness, fat tails and cross-market co-movement. Weakness: it assigns zero empirical probability to shocks absent from the window and equal relevance to every included day unless the method is deliberately weighted.
5Parametric VaR
Parametric—or variance–covariance—VaR assumes a probability distribution for returns. Under the normal approximation, the calculation reduces to the portfolio mean, volatility and a standard-normal critical value.
5.1 · Parametric VaR for a single asset
For portfolio value \(V_0\), expected return \(\mu_{\Delta t}\), volatility \(\sigma_{\Delta t}\), confidence level \(c=1-\alpha\), and positive critical value \(z_c\):
This is the left-tail return cutoff and is normally negative. To express it as a positive currency loss, reverse the sign and multiply by the current portfolio value:
For a short horizon, practitioners often assume \(\mu_{\Delta t}\approx0\). The monetary formula then simplifies to:
| Symbol | Meaning |
|---|---|
| \(V_0\) | Current value of the asset or portfolio in currency terms |
| \(c=1-\alpha\) | Chosen confidence level; \(\alpha\) is the left-tail probability |
| \(z_c\) | Positive standard-normal critical value for confidence level \(c\) |
| \(\sigma_{\Delta t}\) | Standard deviation of returns over horizon \(\Delta t\) |
| \(\mu_{\Delta t}\) | Expected return over horizon \(\Delta t\) |
5.2 · Time-horizon scaling
If daily returns are independent with stable mean and volatility, scale them to a \(T\)-trading-day horizon as follows:
The rule can misstate risk when volatility changes through time, returns are autocorrelated, markets are illiquid, or the portfolio contains nonlinear positions. For those cases, simulate or revalue directly over the intended horizon.
5.3 · Common one-tailed Z-scores
| Confidence \((c=1-\alpha)\) | Tail probability \((\alpha)\) | Positive Z-score \((z_c)\) |
|---|---|---|
| 90% | 10% | 1.282 |
| 95% | 5% | 1.645 |
| 97.5% | 2.5% | 1.960 |
| 99% | 1% | 2.326 |
In \(\mu-z_c\sigma\), \(z_c\) is a positive magnitude. If software supplies the negative left-tail value directly—such as \(\Phi^{-1}(0.05)=-1.645\)—use \(\mu+z_{\mathrm{left}}\sigma\) instead. Both forms give the same negative return cutoff.
Example · 95% one-day VaR: suppose daily mean return is \(\mu=0.05\%\) and daily volatility is \(\sigma=1.00\%\). Using the positive critical value:
Using the negative left-tail value gives exactly the same result:
Therefore, the signed VaR return is −1.595%. Reversing the sign and applying it to a ₹100 crore portfolio gives a reported positive monetary VaR of ₹1.595 crore, approximately ₹1.60 crore.
5.4 · Parametric VaR for a multi-asset portfolio
For a linear portfolio with weight vector \(\mathbf w\) and return covariance matrix \(\Sigma\), portfolio mean and volatility are:
For two assets \(A\) and \(B\), the volatility formula expands to:
where \(\rho_{AB}\) is the correlation between the two asset returns. The signed portfolio return cutoff and the positive monetary VaR are then:
If \(\mu_p\approx0\), this simplifies to \(\operatorname{VaR}^{(\mathrm{money})}_{p,c}\approx V_0z_c\sigma_p\). Diversification enters through the covariances in \(\Sigma\); lower correlations generally reduce portfolio volatility and VaR.
5.5 · Parametric Expected Shortfall
For normally distributed returns, parametric Expected Shortfall as a positive monetary loss is:
where \(\phi\) is the standard-normal density. Unlike VaR, ES averages the outcomes beyond the left-tail cutoff.
A ₹100 crore linear portfolio with 1.25% daily volatility and zero mean has a 95% return cutoff of \(-1.645\times1.25\%=-2.06\%\). Converting that negative cutoff into a positive loss magnitude gives VaR of approximately ₹2.06 crore. At 99%, using \(z=2.326\), VaR rises to about ₹2.91 crore.
Options, barriers and structured products have curved payoffs. A linear approximation can miss gamma and volatility effects precisely when markets move sharply. Delta–gamma methods help; full revaluation is safer when material nonlinearities remain.
6Monte Carlo VaR
Monte Carlo VaR runs thousands of randomized scenarios from a chosen stochastic model. Unlike Historical VaR, it is not restricted to observed past scenarios; unlike closed-form Parametric VaR, it can fully revalue complex nonlinear positions such as options and structured derivatives.
6.1 · Simulating an asset price with GBM
A standard introductory model is Geometric Brownian Motion. Over horizon \(\Delta t\), one simulated terminal price is:
| Symbol | Meaning |
|---|---|
| \(S_t\) | Current asset price |
| \(S_{t+\Delta t}\) | Simulated price at the end of the risk horizon |
| \(\mu\) | Expected annualized drift under the simulation measure |
| \(\sigma\) | Annualized asset-return volatility |
| \(\Delta t\) | Horizon in years; for one trading day, commonly \(1/252\) |
| \(Z\) | Independent standard-normal random draw |
For VaR scenario generation, drift, volatility and dependence should represent the intended real-world risk horizon. Risk-neutral parameters may still be used inside the valuation model when repricing derivatives in each simulated market scenario.
6.2 · Correlated multi-asset simulations
For \(n\) assets, draw a vector \(\mathbf Z\) of independent standard normals. If \(C\) is the return correlation matrix, obtain its Cholesky factor \(L\) and create correlated standard-normal shocks:
Use \(\varepsilon_i\) in the price equation for asset \(i\). Equivalently, if \(\Sigma\) is the return covariance matrix and \(B B^\top=\Sigma\), a vector of correlated return innovations is \(B\mathbf Z\). Keeping correlation and covariance notation separate avoids applying volatility twice.
6.3 · Step-by-step calculation
For each simulation \(m=1,2,\ldots,M\), calculate the terminal portfolio value and its signed profit or loss:
Sort all simulated P&Ls from the most negative to the most positive:
6.4 · Extract VaR and Expected Shortfall
At confidence \(c=1-\alpha\), use the documented quantile convention. Under the nearest-rank method, \(k=\lceil\alpha M\rceil\). The signed return cutoff and positive monetary VaR are:
Monte Carlo Expected Shortfall averages the P&Ls at or below the VaR cutoff and reverses the sign:
Normal draws produce Gaussian shocks. Student-\(t\), filtered historical simulation, jump diffusion, stochastic volatility or copula-based models can represent heavier tails and richer dependence. The chosen model determines which risks the simulation can—and cannot—see.
6.5 · Method comparison
| Feature | Parametric VaR | Historical VaR | Monte Carlo VaR |
|---|---|---|---|
| Distribution | Closed-form chosen distribution, often normal | Empirical historical distribution | Chosen stochastic processes and innovations |
| Skewness and fat tails | Only if explicitly modelled | Only those present in the selected history | Can be modelled with fat-tailed or asymmetric dynamics |
| Option payoffs | Limited under delta-normal approximation | Good with full revaluation | Excellent with full revaluation |
| Speed | Fastest | Fast to moderate | Most computationally intensive |
| Primary model risk | Distribution and covariance assumptions | Historical-window representativeness | Process, parameter and dependence choices |
A larger simulation count reduces sampling noise, but it does not correct missing risk factors, poor parameters, unsuitable dynamics or an incorrect valuation model. Monte Carlo precision is not the same as model accuracy.
7Compare the Three Methods
The same portfolio can produce different VaR estimates because each method asks the data to speak in a different way. The chart below uses one reproducible teaching sample containing calm days and several shocks.
Choose it by portfolio structure, data quality, horizon, tail behaviour and intended use. A more complicated method is not automatically a better model.
8Where VaR Falls Short
Blind beyond the cutoff
VaR locates a left-tail return cutoff, but does not say how negative returns become beyond it.
Can fail subadditivity
For some distributions, combined VaR can exceed the sum of stand-alone VaRs, conflicting with diversification intuition.
Model and window risk
Confidence, horizon, data window, volatility model and dependence assumptions can dominate the answer.
Two independent positions each produce P&L of −₹10 crore with 4% probability and zero otherwise. Each position’s 95% VaR is zero. The combined P&L is negative by at least ₹10 crore with probability \(1-0.96^2=7.84\%\), so its 95% VaR is ₹10 crore. Thus \(\operatorname{VaR}(A+B)>\operatorname{VaR}(A)+\operatorname{VaR}(B)\).
VaR is also procyclical: quiet windows produce low volatility and low limits; after a shock, measured risk rises when liquidity may already be scarce. Stress testing, scenario analysis, concentration limits and liquidity metrics must sit beside it.
9Expected Shortfall: Looking Beyond VaR
Expected Shortfall at confidence \(c\) is the positive magnitude of the average return in the worst \(1-c\) fraction of outcomes. For a continuous return distribution:
The conditioning region is the left tail. ES is tail-sensitive and, under standard conditions, coherent—including subadditivity.
| Measure | Question answered | What it misses |
|---|---|---|
| VaR | Where does the bad tail begin? | Severity beyond the threshold |
| Expected Shortfall | What is the average loss once we are in the tail? | Still depends on model/data; harder to estimate and backtest |
| Stress loss | What happens in a named severe scenario? | No inherent probability unless one is assigned |
The Basel Framework’s internal-model approach uses a 97.5% one-tailed Expected Shortfall, computed daily and adjusted for risk-factor liquidity horizons. VaR remains important for model backtesting: the framework compares one-day VaR with actual and hypothetical P&L.
10Backtesting VaR
A VaR exception occurs when realised daily P&L falls below the negative VaR cutoff: \(\text{P&L}_t<-\operatorname{VaR}_t\). At 99% confidence, a correctly calibrated model should produce exceptions on roughly 1% of days—but randomness means the observed count will not equal 1% exactly.
10.1 · Kupiec proportion-of-failures test
The Kupiec test asks whether the exception rate is consistent with the model’s promised coverage. With \(x\) exceptions in \(n\) days and expected exception probability \(p\):
A small p-value rejects correct unconditional coverage. It does not test whether exceptions cluster. Independence and conditional-coverage tests are needed because five scattered exceptions and five consecutive exceptions tell different risk stories.
10.2 · Basel traffic-light interpretation
For 250 observations at 99% VaR, the Basel framework treats 0–4 exceptions as green, 5–9 as yellow and 10 or more as red. The zones are supervisory diagnostics, not proof that a model is economically adequate.
11Implementation Blueprint
| Layer | Controls that matter |
|---|---|
| Data | Clean prices, corporate actions, stale quotes, missing data, consistent calendars and currencies |
| Positions | Complete inventory, correct sensitivities, option terms and legal netting |
| Model | Volatility, dependence, fat tails, nonlinear revaluation, horizon and liquidity assumptions |
| Validation | Exceptions, clustering, P&L attribution, sensitivity analysis and benchmark models |
| Governance | Limits, overrides, escalation, change control and independent review |
Report VaR and ES alongside exposures, sensitivities, stress losses, concentration, liquidity, backtesting exceptions and major model limitations. The number is an entry point for judgment—not a substitute for it.
11.1 · Excel setup
The formulas below use one consistent worksheet layout. Enter 500 daily returns in B2:B501 and use these input cells:
| Cell | Input or calculation | Excel entry |
|---|---|---|
| E2 | Current portfolio value | 1000000000 |
| E3 | Confidence level | 95% |
| E4 | Mean daily return | =AVERAGE(B2:B501) |
| E5 | Daily volatility | =STDEV.S(B2:B501) |
| E6 | Left-tail probability | =1-E3 |
| E7 | Holding period in trading days | 1 |
These examples use commas between function arguments. If your Excel installation uses semicolons, replace each argument-separating comma with a semicolon. Format return outputs as percentages and monetary outputs in the portfolio currency.
11.2 · Parametric VaR and ES in Excel
| Calculation | Excel formula |
|---|---|
| Positive Z-value | =NORM.S.INV($E$3) |
| Signed VaR return | =$E$4-NORM.S.INV($E$3)*$E$5 |
| Positive monetary VaR | =-$E$2*($E$4-NORM.S.INV($E$3)*$E$5) |
| Normal monetary ES | =$E$2*($E$5*NORM.S.DIST(NORM.S.INV($E$3),FALSE)/(1-$E$3)-$E$4) |
| T-day volatility | =$E$5*SQRT($E$7) |
| T-day mean return | =$E$4*$E$7 |
| T-day monetary VaR | =-$E$2*($E$4*$E$7-NORM.S.INV($E$3)*$E$5*SQRT($E$7)) |
For a multi-asset worksheet, place portfolio weights in B1:D1 and aligned asset returns in B2:D501. In E2, calculate each day’s portfolio return and copy downward:
=SUMPRODUCT($B$1:$D$1,B2:D2)
Apply the Parametric formulas to the resulting portfolio-return series in E2:E501. This is equivalent to incorporating the assets’ covariance through the observed portfolio volatility.
11.3 · Historical VaR and ES in Excel
| Calculation | Excel formula |
|---|---|
| Scenario P&L in C2; copy down | =$E$2*B2 |
| Interpolated signed-return cutoff | =PERCENTILE.INC($B$2:$B$501,1-$E$3) |
| Nearest-rank signed cutoff | =SMALL($B$2:$B$501,ROUNDUP((1-$E$3)*COUNT($B$2:$B$501),0)) |
| Positive monetary VaR from returns | =-$E$2*PERCENTILE.INC($B$2:$B$501,1-$E$3) |
| Positive monetary ES from returns | =-$E$2*AVERAGEIF($B$2:$B$501,"<="&PERCENTILE.INC($B$2:$B$501,1-$E$3),$B$2:$B$501) |
| Positive VaR directly from P&L | =-PERCENTILE.INC($C$2:$C$501,1-$E$3) |
| Positive ES directly from P&L | =-AVERAGEIF($C$2:$C$501,"<="&PERCENTILE.INC($C$2:$C$501,1-$E$3),$C$2:$C$501) |
PERCENTILE.INC interpolates, while SMALL with a rounded rank selects an observed return. Choose one convention in the risk policy and use the same method for VaR reporting and backtesting.
11.4 · Monte Carlo VaR and ES in Excel
For a single-asset GBM simulation, place current price in K2, annual drift in K3, annual volatility in K4, and horizon in years in K5. Generate 10,000 scenarios from rows 2 to 10001:
| Column | Calculation | Excel formula for row 2 |
|---|---|---|
| G | Standard-normal draw | =NORM.S.INV(RAND()) |
| H | Simulated terminal price | =$K$2*EXP(($K$3-0.5*$K$4^2)*$K$5+$K$4*SQRT($K$5)*G2) |
| I | Simulated return | =H2/$K$2-1 |
| J | Simulated portfolio P&L | =$E$2*I2 |
After copying the simulation formulas down, calculate:
| Calculation | Excel formula |
|---|---|
| Monte Carlo signed-return cutoff | =PERCENTILE.INC($I$2:$I$10001,1-$E$3) |
| Positive Monte Carlo VaR | =-PERCENTILE.INC($J$2:$J$10001,1-$E$3) |
| Positive Monte Carlo ES | =-AVERAGEIF($J$2:$J$10001,"<="&PERCENTILE.INC($J$2:$J$10001,1-$E$3),$J$2:$J$10001) |
For three correlated assets in Excel 365, store a precomputed 3×3 lower-triangular Cholesky factor in N2:P4. This dynamic-array formula produces one vector of correlated standard-normal shocks:
=MMULT($N$2:$P$4,NORM.S.INV(RANDARRAY(3,1)))
RAND() recalculates whenever the workbook changes. Use manual calculation while reviewing, run enough scenarios for tail stability, and paste the final scenario outputs as values when an auditable static result is required.
11.5 · VaR backtesting in Excel
Assume actual daily P&L is in L2:L251, the corresponding positive VaR forecast is in M2:M251, and exceptions are recorded in column N.
| Calculation | Excel formula |
|---|---|
| Exception flag in N2; copy down | =--(L2<-M2) |
| Exception count in N253 | =SUM(N2:N251) |
| Observation count in N254 | =COUNT(L2:L251) |
| Observed exception rate | =AVERAGE(N2:N251) |
| Kupiec LR statistic in N255 | =LET(n,$N$254,x,$N$253,p,1-$E$3,IF(x=0,-2*n*LN(1-p),IF(x=n,-2*n*LN(p),-2*((n-x)*LN((1-p)/(1-x/n))+x*LN(p/(x/n)))))) |
| Kupiec p-value | =CHISQ.DIST.RT(N255,1) |
| Basel zone for 250 days at 99% | =IF(N253<=4,"Green",IF(N253<=9,"Yellow","Red")) |
An exception occurs only when actual P&L is more negative than the negative VaR threshold. A Kupiec p-value below the selected test significance level indicates that the observed exception frequency is inconsistent with the model’s promised coverage; it does not test exception clustering.
12Knowledge Check
1 · Where are bad outcomes on a return distribution?
In the left tail, where returns and P&L are most negative.
2 · Why is signed return VaR negative while monetary VaR is positive?
The signed return VaR is the lower-tail return cutoff. For loss reporting, its sign is reversed before multiplying by the portfolio value.
3 · Why can historical VaR fall after a crisis observation leaves the window?
Because the empirical left tail changes discontinuously when that large negative return is no longer in the estimation window.
4 · Why is ES usually larger than VaR?
VaR converts the left-tail cutoff into a positive amount; ES converts the average of all returns below that cutoff into a positive amount.
5 · What does the Kupiec test miss?
It tests total exception frequency, not whether exceptions are independent through time or clustered in stress.
§Sources & Further Reading
1. J.P. Morgan/Reuters, RiskMetrics Technical Document, Fourth Edition (1996).
2. Basel Committee on Banking Supervision, MAR33: Internal models approach—capital requirements calculation.
3. Basel Committee on Banking Supervision, MAR32: Backtesting and P&L attribution requirements.
4. Basel Committee on Banking Supervision, Supervisory Framework for Backtesting (1996).
5. Artzner, Delbaen, Eber & Heath, “Coherent Measures of Risk,” Mathematical Finance (1999).