From one-number risk limits to the full shape of loss
History · Historical Simulation · Parametric VaR · Monte Carlo · Backtesting
Prof. V. Ravichandran28+ years in Corporate Finance & Banking · 10+ years in Academia
Interactive Study GuideMBA · CFA · FRM · Risk Analytics
Imagine you have invested ₹1,00,000. Its value changes every day. You want to know: how much could I lose on a bad day, and how often might the loss be even larger? Value-at-Risk (VaR) estimates a loss threshold. Expected Shortfall (ES) describes the average loss in the worst outcomes. Start with the basics below, then explore the calculations.
Your learning path: Understand chance → read a VaR statement → explore the three methods → learn what VaR misses → check the model against actual outcomes.
Start hereRisk Basics in Everyday Language
No statistics background is needed for this introduction. We will use one investment and a one-day time period throughout.
What is probability?
Probability means the chance that a specified event will happen. It runs from 0 to 1, or from 0% to 100%. A 0% probability means impossible within the model; 100% means certain within the model.
50% · One in two
A fair coin has a 50% chance of heads on each toss.
5% · Five in 100
An event with a 5% chance occurs about five times per 100 comparable trials on average.
1% · One in 100
Less likely does not mean impossible—or harmless.
These are long-run averages, not schedules. A fair coin need not produce exactly five heads in ten tosses. Similarly, severe market losses can occur close together.
Market probabilities are estimates. Unlike a fair coin, tomorrow’s market does not come with known odds. We estimate probabilities using historical observations or a mathematical model. For example, 5 losses beyond a threshold in 100 past trading days give a historical frequency of 5%; that does not guarantee the same chance tomorrow.
What are a portfolio, a return and a loss?
A portfolio is the collection of investments you hold. A return is the change in value expressed as a percentage. Profit and loss (P&L) is the change expressed in money.
One investment, two ways to describe the same change
The negative sign shows a fall in value. When we report the size of the loss, we say ₹2,000, a positive amount. This example ignores fees, income and money added or withdrawn.
What is a confidence level in VaR?
The confidence level is the share of outcomes the model places at or below the reported loss threshold. At 95% confidence, the threshold covers 95% of modelled outcomes; the remaining 5% form the worse-loss tail. For a continuous distribution, the modelled probability of losing more than the threshold is 5%.
Read this sentence carefully
“The one-day 95% VaR is ₹2,000.”
According to the model, there is a 95% chance that the one-day loss will be ₹2,000 or less, including outcomes with no loss or a profit. There is a 5% chance of a loss larger than ₹2,000.
It does not mean a 95% chance of making a profit, that you will lose ₹2,000 tomorrow, or that ₹2,000 is the largest possible loss.
Picture the confidence level · 100 possible outcomes
Blue: at or below the VaR loss threshold. Red: beyond it. Each square represents 1% probability; these are model outcomes, not a forecast of the next 100 days. Raising confidence generally raises the VaR threshold for the same portfolio, horizon and model.
VaR confidence
Worse-loss tail
Expected frequency under a correctly calibrated continuous model
90%
10%
About 10 in 100 comparable periods
95%
5%
About 5 in 100 comparable periods
99%
1%
About 1 in 100 comparable periods
Confidence is coverage, not model accuracy. Selecting 99% does not make the model 99% reliable. Actual outcomes can differ when the data or assumptions are unsuitable. With discrete historical samples and tied outcomes, exact tail percentages can differ from the nominal level.
What is the holding period—or risk horizon?
The holding period is the length of time over which we measure the change in value. One-day VaR concerns one trading day; ten-day VaR concerns ten trading days. It does not mean you must sell at the end of that period.
Always compare estimates using the same horizon and confidence level. A one-day 95% VaR and a ten-day 99% VaR answer different questions. Longer horizons require additional assumptions about market moves, trading and the portfolio.
What are a distribution, a percentile and a tail?
A distribution describes possible outcomes and how likely they are. Imagine arranging possible investment returns from the biggest loss on the left to the biggest gain on the right.
Large losses Left tailSmall changes MiddleLarge gains Right tail
A percentile is a position in that ordered distribution. The 5th-percentile return is the cutoff with about 5% of returns at or below it. For 95% VaR, we look at this left-tail return cutoff, then reverse its sign to report the positive loss magnitude.
A tail is an extreme end of the distribution. Throughout this guide, returns and P&L keep their natural sign: large losses are negative and appear in the left tail. Only the reported loss amount reverses the sign to give a positive magnitude.
What is volatility?
Volatility describes how much returns fluctuate. An investment that frequently moves sharply has higher volatility than one whose daily changes are usually small. Standard deviation is a common numerical measure of that spread.
Daily volatility of 1% does not mean a maximum daily loss of 1%, or that the investment moves by exactly 1% every day. Volatility measures spread; it does not tell the whole story about rare severe losses.
How is Expected Shortfall different from VaR?
Expected Shortfall is also commonly called Conditional VaR (CVaR). See the sorted-return example to locate a VaR cutoff and average the worst tail by hand.
VaR asks
At our chosen confidence level, where is the loss threshold?
ES asks
What is the average loss in the worst fraction of outcomes?
Stress testing asks
What would we lose in a particular severe market scenario?
A threshold does not describe the losses beyond it
Suppose a model gives a 95% VaR of ₹2,000. Losses in its worst 5% of outcomes might be much larger. If the average loss across that entire worst 5% is ₹4,000, the 95% Expected Shortfall is ₹4,000.
₹2,000 is the cutoff; ₹4,000 is the tail average. Neither is a guarantee or a maximum loss. These numbers illustrate the distinction; ES cannot be calculated from the VaR number alone.
Try reading a complete risk statement
“A ₹1,00,000 portfolio has a one-day 99% VaR of ₹3,000.” The model estimates a 1% probability of losing more than ₹3,000 over one trading day. It does not tell us how large those worse losses could be.
Does 99% confidence mean a 99% chance of earning a profit?
No. It refers to the loss threshold. The covered outcomes include smaller losses, no change and profits.
If a 95% VaR limit is exceeded twice in one week, is that impossible?
No. Probabilities are not schedules, and losses can cluster. Repeated or clustered exceptions are a reason to investigate the model.
Can two portfolios have the same VaR but different Expected Shortfalls?
Yes. Their loss cutoffs can be the same while the losses in the worst tail differ substantially.
Ready for the next step? Read Section 1 to connect these ideas to the formula. Then use the lab: hold portfolio value and volatility fixed, increase confidence from 95% to 99%, and observe how the loss threshold changes.
Sign convention used in this guide
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
In plain language: VaR marks a bad-outcome cutoff, using a chosen probability and time period. We estimate possible returns, locate the lower-tail cutoff, and convert it into a money loss.
In the formula, \(R\) denotes the portfolio return over the 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.
1 · PortfolioWhat positions and risk factors?
2 · HorizonOne day, ten days, or longer?
3 · Confidence95%, 97.5%, or 99%?
4 · ModelHistorical, parametric, or simulated?
Before using the lab: Portfolio value is the amount invested; daily volatility measures the spread of daily returns; daily mean is the average return assumed by the model. Start with the default values. This lab assumes normally distributed returns—a bell-shaped model—so it illustrates that assumption rather than predicting your personal investment losses.
Interactive lab · See VaR and ES in the left tail of returns
Parametric VaR (₹ loss)—
Expected Shortfall (₹ loss)—
Signed VaR return—
Negative returns are on the left. The red line marks the return cutoff corresponding to VaR; the shaded left tail contains worse returns. ES converts their average into a positive loss magnitude.
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.
1980s
Trading-room aggregation Large banks develop portfolio-wide measures to consolidate market exposures across risk factors.
1994
RiskMetrics is released publicly J.P. Morgan publishes a transparent methodology and market data framework, accelerating industry adoption.
1996
RiskMetrics Technical Document, fourth edition The framework becomes a widely used reference for variance–covariance market-risk measurement.
1996+
Basel backtesting framework Supervisors connect model exceptions to a green, yellow and red traffic-light regime.
2016–19
Expected Shortfall enters the revised trading-book framework 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
The everyday idea: Replay past market days on the investments you own today. If yesterday’s market moves happened again, what would your portfolio gain or lose?
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
1 · CalculateConvert historical prices into periodic returns
2 · ApplyReplay each return or risk-factor shock on today’s portfolio
3 · RevalueGenerate one simulated P&L for every historical day
4 · SortOrder outcomes and read the left-tail cutoff
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:
$$\mathrm{P\&L}_t=V_0R_t.$$
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:
At confidence \(c=1-\alpha\), the left-tail probability is \(\alpha\). Under the common nearest-rank convention, the ascending-order cutoff is:
$$k=\left\lceil\alpha N\right\rceil.$$
Document the quantile convention
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:
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
Small-sample warning
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:
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
The everyday idea: Describe possible returns with a mathematical shape, estimate how wide that shape is, and calculate the bad-outcome cutoff. The common normal model is a bell curve, with many small moves and fewer large moves.
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:
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
Be consistent about the Z-value
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:
$$0.05\%-1.645(1.00\%)=-1.595\%.$$
Using the negative left-tail value gives exactly the same result:
$$0.05\%+(-1.645)(1.00\%)=-1.595\%.$$
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:
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.
Example
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.
Where delta-normal VaR breaks
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.
5.6 · Convert VaR Between Confidence Levels
Higher confidence reaches farther into the loss tail. For the same portfolio and horizon under a normal-return model with zero mean, convert VaR using the ratio of the two normal critical values.
3New VaR = ₹15,000 × 2.326348 ÷ 1.644854 ≈ ₹21,214.79
Confidence changes from 95% to 99%; portfolio, volatility, mean and holding period stay the same. The modelled worse-loss probability falls from 5% to 1%.
New confidence
New Z-score
VaR converted from ₹15,000 at 95%
90%
1.281552
₹11,686.92
99%
2.326348
₹21,214.79
99.9%
3.090232
₹28,180.92
=15000*NORM.S.INV(99%)/NORM.S.INV(95%)
What if the expected return is not zero?
For the same horizon, let V be portfolio value and μ be expected return over that horizon. First add back the mean-return adjustment, apply the Z-score ratio, then subtract the adjustment again:
Example: V = ₹1,00,000, daily mean return 0.05%, daily volatility 1%. The 95% VaR is ₹1,594.85. Add ₹50, multiply by the Z-score ratio and subtract ₹50: 99% VaR becomes ₹2,276.35.
Use the shortcut only under its assumptions. The distribution remains normal, the portfolio and horizon stay fixed, and volatility stays the same. Historical and Monte Carlo VaR should be recalculated from the relevant scenario quantile. A mean adjustment is needed when mean return is nonzero.
5.7 · Calculate the Diversification Benefit
Assets that do not move perfectly together can reduce portfolio volatility. Compare the sum of standalone position VaRs with the combined portfolio VaR, using the same model, horizon, confidence and mean convention.
Reuse the two-asset example: portfolio ₹1,00,000, A weight 60%, B weight 40%, daily volatilities 1% and 1.5%, correlation 0.25, zero means, one-day 95% confidence.
1A position VaR = ₹60,000 × 1.644854 × 1% = ₹986.91
2B position VaR = ₹40,000 × 1.644854 × 1.5% = ₹986.91
For this long-only normal model, correlation +1 would make the combined VaR equal the sum of standalone position VaRs. Lower correlation reduces it here. In stressed markets, co-movement can increase and the benefit can shrink.
The last two lines are formula templates: replace the names with the cells holding your results, or define those names in Excel. VaR can fail subadditivity for other distributions, so a positive diversification benefit is not universal. Do not ignore means in standalone estimates while including them in the combined estimate.
6Monte Carlo VaR
The everyday idea: Generate many possible future market scenarios, value today’s investments in each scenario, and arrange the resulting gains and losses. A simulation is a modelled possibility, not a prediction.
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:
$$S_{t+\Delta t}=S_t\exp\!\left[
\left(\mu-\frac{\sigma^2}{2}\right)\Delta t
+\sigma\sqrt{\Delta t}\,Z\right],\qquad Z\sim N(0,1).$$
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
Risk forecasting is not risk-neutral pricing
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.
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:
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
More scenarios do not repair a wrong model
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.
Monte Carlo convergence · More scenarios, less sampling noise
The path uses a fixed teaching simulation. Even a correct model produces a noisy tail estimate when the scenario count is small.
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.
Interactive method comparison
Do not choose the method by the largest or smallest answer
Choose it by portfolio structure, data quality, horizon, tail behaviour and intended use. A more complicated method is not automatically a better model.
7.1 · Which VaR Method Should You Use—and When?
Three approaches. Different assumptions. Different use cases. Choose based on the portfolio’s complexity, available data, modelling assumptions and speed required.
01
Historical VaR
Let past market movements inform today’s risk.
Replays historical risk-factor changes on the current portfolio and measures the resulting losses.
✓ Best used when
You have sufficient, representative historical market data.
You want to preserve observed skewness, fat tails and cross-market movements.
You prefer an approach with fewer explicit distributional assumptions.
Practical example: Estimate an equity portfolio’s daily risk by applying past joint market shocks to today’s holdings.
Watch out: History may miss future crises. A calm data window can understate risk, and high-confidence estimates depend on very few extreme observations.
02
Parametric VaR
A fast estimate built from a chosen distribution.
Uses estimated parameters—typically mean, volatility and correlations—to calculate a loss threshold.
✓ Best used when
You need quick, frequent risk estimates.
Portfolio exposures are mainly linear.
The chosen distribution and covariance estimates reasonably describe the portfolio.
Practical example: Monitor a diversified cash-equity or linear FX portfolio throughout the trading day.
Watch out: Normal-distribution assumptions can understate heavy-tail risk. Delta-normal approximations can miss material option nonlinearities.
03
Monte Carlo VaR
Simulate possible markets and revalue the portfolio.
Generates many joint scenarios—or complete price paths—from a specified risk-factor model.
✓ Best used when
You hold options, structured products or other nonlinear instruments.
Payoffs depend on the path of market prices.
You need flexible modelling of multiple risk factors.
You have the computing capacity and expertise to validate the models.
Practical example: Measure risk for a derivatives portfolio containing barrier options or other path-dependent products.
Watch out: Results depend on the scenario and valuation models. More simulations reduce sampling noise; they cannot repair incorrect assumptions.
Quick Selection Guide
Your primary need
Method to consider
Essential condition
Fast, frequent estimates
Parametric
Mainly linear exposures and reasonable distribution assumptions
Replay observed market behaviour
Historical
Sufficient, representative and aligned historical data
Revalue complex or path-dependent payoffs
Monte Carlo
Suitable scenario models and reliable full revaluation
Two important clarifications
Historical VaR can handle options when every historical scenario fully revalues the positions.
Monte Carlo does not automatically capture fat tails. The chosen model must explicitly represent them.
The Takeaway
There is no universally “best” VaR method.
Choose according to portfolio structure, data quality, risk horizon, tail behaviour and required speed.
Backtest the estimates, compare against a benchmark method, and report Expected Shortfall and stress losses alongside VaR.
Choose the method that fits the risk—not the one that gives the lowest number.
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.
A simple coherence counterexample
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
Regulatory context
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.
9.1 · Conditional VaR: Sort, Find the Cutoff, Average the Tail
Conditional Value-at-Risk (CVaR) answers: “What is the average loss in the worst part of the distribution?” It is commonly used as another name for Expected Shortfall (ES), defined as the average loss in the worst 1 − confidence fraction of outcomes.
VaR is a cutoff. CVaR is a tail average. We do not average several VaR estimates; we average the losses in the selected worst tail.
A complete example you can calculate by hand
Imagine a ₹1,00,000 investment and 20 equally likely one-day return scenarios. Use 90% confidence so the worst 10% contains exactly two scenarios. This small sample is for learning, not a reliable real-world risk forecast.
Step 1 · Sort the returns from worst to best
Negative returns are losses. Each scenario has probability 1 ÷ 20 = 5%. The two red tiles form the worst 10%; the outlined tile is the VaR cutoff.
1-10%Worst tail
2-6%VaR cutoff
3-4%
4-3%
5-2%
6-1%
7-0.5%
80%
90.2%
100.5%
110.8%
121%
131.2%
141.5%
152%
162.5%
173%
184%
195%
206%
Step 2 · Locate the VaR cutoff
1Tail probability = 100% − 90% = 10%
2Tail size = 20 × 10% = 2 scenarios
3Nearest-rank cutoff = second-worst return = −6%
Signed-return VaR = −6%. Expressed as a positive loss magnitude, monetary VaR = ₹1,00,000 × 6% = ₹6,000.
Step 3 · Average the returns in the worst tail
The two tail returns are −10% and −6%. Include the cutoff observation here so that the selected tail contains exactly 10% of the scenario probability.
Tail-average return → positive CVaR loss
Average tail return = (−10% + −6%) ÷ 2 = −8%.
CVaR / ES as a positive loss percentage = 8%.
Monetary CVaR = ₹1,00,000 × 8% = ₹8,000.
Tail scenario
Return
Positive money loss
Worst
−10%
₹10,000
Second-worst · VaR cutoff
−6%
₹6,000
Average of the two
−8%
₹8,000 · CVaR / ES
Step 4 · See the difference
VaR cutoff · ₹6,000
CVaR tail average · ₹8,000
Worst sample loss · ₹10,000
Read it aloud: “For this teaching sample, the one-day 90% VaR loss threshold is ₹6,000. The average loss in the worst 10% is ₹8,000, so 90% CVaR is ₹8,000.” The worst observed scenario loses ₹10,000; neither VaR nor CVaR is a maximum possible loss.
Why “strictly beyond VaR” needs care in a small sample
In this discrete example, only the −10% return is strictly worse than −6%. Averaging that observation alone gives ₹10,000 and covers only 5% probability—not the intended 10% tail. The correct worst-10% average includes the −6% cutoff observation.
For continuous distributions, there is no probability mass exactly at the cutoff, so CVaR can be described as the average loss beyond VaR. For discrete scenarios, tied returns or fractional tail sizes, use the appropriate probability weight at the boundary to average exactly the worst 1 − confidence fraction. Simply including every observation equal to the cutoff may include too much probability.
What if confidence changes?
Confidence
Worst tail
Nearest-rank VaR loss
CVaR / ES loss
80%
Worst 4 returns: −10%, −6%, −4%, −3%
3% · ₹3,000
5.75% · ₹5,750
90%
Worst 2 returns: −10%, −6%
6% · ₹6,000
8% · ₹8,000
95%
Worst 1 return: −10%
10% · ₹10,000
10% · ₹10,000
The last row shows a small-sample limitation: at 95%, the tail contains only one observation, so its average equals its cutoff. A larger, representative dataset is needed to estimate rare tail risk credibly.
Quick check: Why is CVaR ₹8,000 rather than ₹6,000?
₹6,000 is the threshold observation. CVaR averages both losses in the worst 10%: (₹10,000 + ₹6,000) ÷ 2 = ₹8,000.
Does “conditional” mean a different market-scenario forecast here?
No. Here it refers to the worst-loss tail. A forecast conditional on today’s volatility or another market state is a different use of the word “conditional”.
The question stays the same: what is the average loss in the worst tail? Historical ES uses past market shocks, Parametric ES uses a fitted distribution, and Monte Carlo ES uses simulated market scenarios. All require a confidence level and a risk horizon. ES is also commonly called CVaR.
For the examples below, use a ₹1,00,000 portfolio, one trading day and 95% confidence. The tail is the worst 5%. Historical and Monte Carlo figures are separate teaching examples, not estimates computed from one shared dataset.
01
Historical Expected Shortfall
Replay past shocks, then average the worst losses.
In plain language: Ask what today’s portfolio would have lost under each historical market move. Sort those scenario P&Ls from worst to best, select the worst 5% and average them.
1Apply 100 historical joint shocks to today’s holdings
2Sort P&Ls; select the worst 5 scenarios
3Average their losses → Historical ES
Worked example · 100 equally weighted scenarios
Tail rank
Scenario return
Positive loss
1 · worst
−6%
₹6,000
2
−4%
₹4,000
3
−3%
₹3,000
4
−2.5%
₹2,500
5 · VaR cutoff
−2%
₹2,000
The other 95 scenarios have returns greater than −2%. With the nearest-rank convention, 95% VaR is ₹2,000. The worst-five average is:
This simple formula assumes equally weighted scenarios and an integer tail size. For fractional tail sizes, weighted observations or ties, allocate boundary probability appropriately to average exactly the worst (1-c) fraction. See Section 9.1.
When to use it
Useful when you have representative, aligned market history and want to preserve observed joint movements without choosing a distribution family. Nonlinear positions require full revaluation in each historical scenario.
Main limitation: Missing historical crises cannot appear in the empirical tail. ES is especially sensitive to a few extreme observations; a short sample can make the estimate unstable.
02
Parametric ES · Hull’s Normal-Return Formula
Use the distribution’s mathematical tail average.
In plain language: Assume a bell-shaped normal distribution and estimate its mean and standard deviation. VaR uses a percentile multiplier; ES uses a larger multiplier that averages the whole worst tail.
Use returns and P&L: losses are on the left
Let R be the portfolio return, with mean μ and volatility σ. At confidence c, the left-tail probability is α = 1 − c and the signed normal cutoff is q = Φ−1(α). At 95% confidence, α = 0.05 and q = −1.644854.
For the usual negative tail outcomes, multiply these signed returns by portfolio value to obtain negative P&L. Reverse the sign only when reporting a positive loss magnitude: monetary ES = V × [σφ(q)/α − μ]. The bell curve and tail illustrations always show signed returns or P&L, with bad outcomes on the left.
φ(q) is the normal density height; Φ(q) is the cumulative probability to the left. At q = −1.644854, Φ(q) = 0.05 and φ(q) ≈ 0.10313564.
What exactly is the ES multiplier?
The multiplier tells us how many standard deviations below the mean the average left-tail return lies, expressed as a positive loss magnitude. For a normal distribution at 95% confidence, it is 2.0627. It is a dimensionless number—not a percentage and not an amount of money.
Number
Where it comes from
What it means
0.95
Chosen confidence: 95%
Probability outside the worst left tail
0.05
1 − 0.95
Probability of the worst-loss tail
−1.644853627
NORM.S.INV(0.05)
Signed cutoff of the worst left 5%, measured in standard deviations
0.103135640
NORM.S.DIST(-1.644853627,FALSE)
Normal density height at that cutoff; this is not a 10.31% probability
2.062712808
0.103135640 ÷ 0.05
Positive magnitude of the average standardised return in the left 5%; signed average = −2.062712808
AFind the tail 1 − 95% = 5% = 0.05
BFind the density At q = −1.644854, φ(q) ≈ 0.10313564
CFind the multiplier 0.10313564 ÷ 0.05 = 2.0627128
Why divide by 0.05?
An average within a selected group divides that group’s weighted total by the group’s weight. Here the selected group is the worst 5%, whose probability weight is 0.05.
For standard normal returns, the probability-weighted total of returns in the left tail equals −φ(q) ≈ −0.10313564. Divide by the left-tail probability to obtain the signed average. Reverse its sign to report the positive loss multiplier:
Signed average standardised return in the left 5% = −0.10313564 ÷ 0.05 = −2.0627128. Positive loss multiplier = −(−2.0627128) = 2.0627128.
This density shortcut is a property of the normal distribution. It is not a general rule for every distribution.
Only now convert the multiplier into rupees
Money standard deviation = ₹1,00,000 × 1% = ₹1,000. Mean P&L = ₹0. Average tail P&L = ₹0 − ₹1,000 × 2.0627128 = −₹2,062.71. Reported ES loss = −(−₹2,062.71) = ₹2,062.71.
Say it in words: “The average return in the left 5% is about 2.06 standard deviations below the mean. Its average P&L is −₹2,063; we report ES as a positive ₹2,063 loss.”
VaR versus ES: −1.6449 is the signed normal left-tail cutoff; −2.0627 is its signed standardised tail average. Their positive loss multipliers are 1.6449 and 2.0627. They answer different questions.
Optional: see the mathematical reason for the density shortcut
Let Z be a standard normal return and q = Φ−1(0.05) = −1.644854. The signed left-tail average is:
Because φ′(u) = −uφ(u), the integral equals −φ(q). Divide by Φ(q) = 0.05 to obtain −2.0627. Reverse the sign only to report a positive loss multiplier.
Excel: keep the two calculations separate
Multiplier only: =NORM.S.DIST(NORM.S.INV(1-95%),FALSE)/(1-95%)
Result: 2.062712808
ES for this zero-mean example: =1000*2.062712808
Result: ₹2,062.71
For a live spreadsheet, reference the cell containing the full-precision multiplier instead of typing its rounded value. This matches B11 (multiplier) and B12 (ES) in the step-by-step worksheet below.
3First find the multiplier: 0.10313564 ÷ 0.05 = 2.0627128. Tail P&L = −₹1,000 × 2.0627128 = −₹2,062.71. Reported ES loss = ₹2,062.71 ≈ ₹2,063
95% VaR ≈ ₹1,645. 95% Parametric ES ≈ ₹2,063. The ES multiplier is approximately 2.0627, compared with the VaR multiplier 1.6449.
Fully worked calculation · Hull normal ES, one step at a time
Follow the same ₹1,00,000 example from inputs to result. Intermediate numbers below are rounded for readability; the final Excel result uses full precision.
Losses are negative P&L on the left. Signed VaR cutoff ≈ −₹1,645; signed tail-average P&L ≈ −₹2,063. Reported positive loss magnitudes are VaR ₹1,645 and ES ₹2,063.
In Excel, =NORM.S.DIST(NORM.S.INV(1-95%),FALSE) gives approximately 0.103136. FALSE returns density height; TRUE would return left-tail cumulative probability 0.05. Keep full precision in Excel.
06
Turn density into the ES multiplier
Signed left-tail average = −φ(q) ÷ 0.05 = −0.103135640 ÷ 0.05 = −2.062712808. Reported positive loss multiplier = 2.062712808
The mean return in the left 5% is about 2.0627 standard deviations below the overall mean. This is larger than the VaR multiplier 1.6449.
07
Calculate the average tail loss
Average tail P&L = ₹0 − ₹1,000 × 2.062713 = −₹2,062.71. Reported ES loss = −(−₹2,062.71) = ₹2,062.71 ≈ ₹2,063
Read it as: under this model, the average one-day loss in the worst 5% of outcomes is about ₹2,063. It is not a maximum loss.
08
Calculate VaR alongside ES
Signed cutoff P&L = ₹0 + ₹1,000 × (−1.644854) = −₹1,644.85. Reported VaR loss = ₹1,644.85 ES − VaR = ₹2,062.71 − ₹1,644.85 = ₹417.86
VaR is the threshold. ES includes the more severe losses beyond it, so the tail average is higher in this normal-model example.
Reproduce all eight steps in a fresh Excel worksheet
Cell
What it means
Enter
Result
B2
Portfolio value
100000
₹1,00,000
B3
Daily volatility
1%
0.01
B4
Daily mean return
0%
0
B5
Confidence
95%
0.95
B6
Tail probability
=1-B5
0.05
B7
Mean P&L
=B2*B4
₹0
B8
Money standard deviation
=B2*B3
₹1,000
B9
Normal critical value
=NORM.S.INV(B6)
−1.644854
B10
Normal density
=NORM.S.DIST(B9,FALSE)
0.103136
B11
ES multiplier
=B10/B6
2.062713
B12
Expected Shortfall
=-B7+B8*B11
₹2,062.71
B13
Value-at-Risk
=-(B7+B8*B9)
₹1,644.85
This is a separate one-day worksheet layout. Enter percentages as percentages, and format B12:B13 as currency. Changing B5 recalculates the tail, multipliers, VaR and ES.
Keep the signed tail separate from the reported loss
Signed tail-average return = μ − σφ(q)/(1−c). Its P&L is V times that return. Reported monetary ES reverses the sign:
If the daily mean return is +0.05%, mean P&L is +₹50. The average tail P&L is ₹50 − ₹2,062.71 = −₹2,012.71; reported ES is ₹2,012.71.
How confidence changes the normal-model estimate
Confidence
Positive VaR multiplier |q|
Positive ES multiplier φ(q)/(1−c)
VaR
ES
95%
1.6449
2.0627
₹1,645
₹2,063
97.5%
1.9600
2.3378
₹1,960
₹2,338
99%
2.3263
2.6652
₹2,326
₹2,665
All rows use zero mean return and daily money standard deviation ₹1,000. Mean and volatility must match the selected horizon; daily inputs do not automatically produce a ten-day estimate.
Result: approximately ₹2,062.71. FALSE selects the density, not the cumulative probability. For other values, replace portfolio value, volatility, confidence and mean return consistently.
When to use it
Useful for fast, transparent estimates when a normal approximation and mainly linear exposures are reasonable. Multi-asset portfolios use the portfolio standard deviation calculated with covariances.
Main limitation: Normal ES can understate heavy-tail risk and miss material nonlinear payoffs. Parametric ES can also use other distributions, but each requires its own appropriate tail-average calculation.
Simulate possible markets, then average the worst losses.
In plain language: Generate many joint market scenarios, fully revalue the current portfolio in each one, and average the worst fraction of the resulting P&Ls.
1Generate 10,000 equally likely one-day scenarios
2Revalue and sort P&Ls from worst to best
3At 95%, average the worst 500 outcomes
Worked example · illustrative simulation outputs
Suppose the 500th-worst P&L is −₹3,000 and the total P&L of the worst 500 scenarios is −₹21,00,000. Assume distinct outcomes:
Monte Carlo VaR = ₹3,000. Monte Carlo ES = −(−₹21,00,000 ÷ 500) = ₹4,200.
Here V(i) is the scenario portfolio value ordered from lowest to highest, and M is the simulation count. The formula assumes an integer tail size and equally weighted scenarios; otherwise use the appropriate boundary weights.
The tail-average step is the same as Historical ES. The difference is where the scenarios come from: observed past shocks versus a specified simulation model.
When to use it
Useful for portfolios with complex options, structured products, several risk factors or path-dependent payoffs, provided the scenario and valuation models capture the relevant risks.
Main limitation: ES depends on the extreme simulated losses and can require many scenarios for stability. More simulations reduce sampling noise but do not fix unsuitable tail assumptions, dependence or valuation models. Monte Carlo is not automatically fat-tailed.
Which ES method should you choose?
Method
Where outcomes come from
How ES is calculated
Good starting use
Historical
Past joint market shocks
Average the worst historical scenario losses
Representative history; full revaluation available
Parametric · normal
Fitted normal distribution
Use mean, volatility and the normal ES multiplier
Fast estimates for mainly linear exposures
Monte Carlo
Chosen stochastic market model
Average the worst simulated losses
Complex or path-dependent portfolios
One consistent rule: Use the same portfolio, horizon, confidence level, currency and sign convention when comparing ES estimates. Differences should be explained by data and modelling assumptions. Neither the lowest nor the highest estimate alone identifies the best method.
10Backtesting VaR
The everyday idea: Compare yesterday’s forecast with what actually happened. If the loss exceeded the forecast threshold, record an exception. Check both how often exceptions occur and whether they bunch together.
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.
Green0–4 exceptions
Yellow5–9 exceptions
Red10 or more
Backtesting lab · 99% VaR over 250 trading days
Green
Exceptions4
Observed rate1.60%
Kupiec p-value—
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
A professional risk report never stops at one number
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
Excel regional settings
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.
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.
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:
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)))
Freeze and audit simulations
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.
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.
11.6 · Single-Asset, Two-Asset and Multi-Asset VaR in Excel
Start with one asset, then add co-movement. These examples calculate normal Parametric VaR for mainly linear exposures using the variance–covariance approach. They do not calculate Historical VaR or Monte Carlo VaR.
1Estimate mean returns and covariances
2Combine them using portfolio weights
3Convert portfolio volatility to VaR
The examples use ₹1,00,000 portfolio value, 95% confidence and one trading day. Use a new worksheet for this layout; its cells differ from the earlier Excel examples. All assets must have numeric returns for the same dates, horizon and currency basis.
Common worksheet layout
Cell or range
Purpose
Entry
A2:A501
500 aligned trading dates
Same date for all asset returns in each row
B2:B501
Asset A daily returns
1% is entered as 1% or 0.01
C2:C501
Asset B daily returns
Needed for two or more assets
D2:D501
Asset C daily returns
Needed for the three-asset example
E2:E4
Estimated means for A, B and C
Use AVERAGE on each return column
F2:F4
Portfolio weights for A, B and C
Vertical column; active weights sum to 100%
H2
Portfolio value
100000
H3
Confidence
95%
H4
Horizon in trading days
1
H5
Portfolio daily mean return
Formula changes with number of assets
H6
Portfolio daily volatility
Formula changes with number of assets
H7
Monetary VaR
Same final formula for all three cases
J2:L4
Sample covariance matrix
Rows and columns both ordered A, B, C
01
Single-Asset VaR
One asset needs its own mean and volatility.
Invest the full ₹1,00,000 in Asset A. Set F2 to 100% and F3:F4 to 0%. Estimate the mean and volatility from B2:B501:
Worked check: if the estimated daily mean is 0% and volatility is 1%, one-day 95% VaR = ₹1,00,000 × 1.644854 × 1% ≈ ₹1,645.
A covariance matrix is unnecessary for a single asset. Its variance is simply volatility squared: 0.01² = 0.0001.
02
Two-Asset VaR
Include each asset’s variance and their covariance.
Set F2 to 60%, F3 to 40% and F4 to 0%. For this worked check, assume Asset A volatility 1%, Asset B volatility 1.5%, correlation 0.25 and both mean returns zero.
1A variance = 0.01² = 0.0001
2B variance = 0.015² = 0.000225
3Cov(A,B) = 0.25 × 0.01 × 0.015 = 0.0000375
Build the 2 × 2 sample covariance matrix
Cell
Meaning
Formula from aligned returns
Worked-check value
J2
Var(A)
=COVARIANCE.S($B$2:$B$501,$B$2:$B$501)
0.0001
K2
Cov(A,B)
=COVARIANCE.S($B$2:$B$501,$C$2:$C$501)
0.0000375
J3
Cov(B,A)
=K2
0.0000375
K3
Var(B)
=COVARIANCE.S($C$2:$C$501,$C$2:$C$501)
0.000225
Actual historical formulas will give the values implied by your data. The worked-check numbers above are specified assumptions, not promised results from arbitrary returns.
Use the same H7 formula as the single-asset case. Do not add the individual assets’ VaRs: that misses the portfolio’s co-movement.
03
Multiple-Asset VaR · Covariance Matrix
The same calculation scales to three or more assets.
A covariance matrix lists every asset’s variance on its diagonal and every pair’s covariance off the diagonal. Match the matrix’s row and column order to the weight vector.
Weights are A = 50%, B = 30%, C = 20%. All means are zero. Daily volatilities are 1%, 1.5% and 2%; correlations are A–B = 0.25, A–C = 0.10, B–C = 0.40.
Worked-check portfolio variance = 0.0000909. Daily volatility ≈ 0.9534%. 95% one-day VaR ≈ ₹1,568 on ₹1,00,000.
For N assets, use N weights, N means and an N × N covariance matrix. Expand the ranges in SUMPRODUCT and MMULT; do not change the underlying calculation.
H5 is daily mean return and H6 is daily portfolio volatility. H4 = 1 gives one-day VaR. Scaling to more days assumes independent returns, stable daily mean and covariance, and suitable linear exposures. Without those conditions, model the requested horizon directly.
For the three-asset case, put the formula below in N2 and copy down to N501:
=$F$2*B2+$F$3*C2+$F$4*D2
With fixed weights and the same complete aligned observations, =STDEV.S(N2:N501) should agree with H6, and =AVERAGE(N2:N501) should agree with H5. This is a useful independent check of the matrix layout.
Avoid common spreadsheet mistakes
Covariance is not correlation. If starting from correlation, calculate Cov(A,B) = correlation × volatility A × volatility B. Do not multiply by volatility again when using an already formed covariance matrix.
Variance is not volatility. Matrix multiplication produces variance; take its square root once.
Use decimal returns. Enter 1% as 0.01, not 1. Covariances here use decimal-return-squared units, so leave matrix cells as ordinary numbers.
Align the data. Use the same complete dates for all assets; do not let separate missing-data patterns silently change pairwise samples.
Keep sample conventions consistent. COVARIANCE.S and STDEV.S use sample estimates. The covariance matrix must be symmetric and valid; a materially negative calculated variance indicates an input or matrix problem.
Match dimensions. Keep weights vertical and matrix order consistent. Microsoft 365 accepts the MMULT formula with Enter; older Excel versions may require Ctrl+Shift+Enter.
VaR turns market exposures into a common loss threshold. It supports decisions when read alongside ES, stress losses, concentration and liquidity. The examples below illustrate internal uses; they are not regulatory capital formulas.
Application
Simple example
How to use the result
Daily risk monitoring
Same one-day 95% model: VaR rises from ₹2,000 to ₹2,750
Investigate new positions, higher volatility, correlation changes or data issues
Risk limits and budgets
An internal VaR limit is ₹2,000; the estimate is ₹2,750
A ₹750 breach triggers the organisation’s review or escalation process; VaR is not an automatic guaranteed stop-loss
Hedging and portfolio design
A proposed hedge lowers VaR but increases stress losses
Evaluate both measures, hedge cost, liquidity and basis risk before changing the portfolio
Economic capital allocation
A firm uses tail-risk analysis to allocate capital across desks
Assess portfolio contributions and stress scenarios; do not simply sum standalone VaRs or equate one-day VaR to capital
Risk-adjusted performance
Annual risk-adjusted profit ₹12,000; allocated economic capital ₹1,00,000
Illustrative annual RAROC = ₹12,000 ÷ ₹1,00,000 = 12%; capital and profit definitions must be consistent
Model validation and reporting
Record realised P&L breaches of a one-day VaR forecast
Check frequency and clustering; explain assumptions and limitations to decision-makers
1Measure: VaR and ES
2Compare: limits and stress losses
3Review: exposures, causes and actions
RAROC is not profit divided by daily VaR. Risk-adjusted return on capital uses an appropriately defined profit measure and allocated economic capital over a consistent evaluation period. A firm’s capital methodology may use VaR, ES and other risk measures.
In the Basel trading-book internal-model framework, ES is used in market-risk capital calculations while VaR remains relevant to backtesting. Requirements include additional conditions and adjustments; a simple VaR calculation alone is not a capital-compliance calculation. Basel market-risk framework.
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.
12.1 · Practice Problems with Checked Solutions
These original teaching adaptations cover the topics in the lecture notes. Monetary examples below use rupees. All normal-model solutions use full-precision Excel Z-scores rather than rounded 1.65 or 2.33, so answers can differ slightly from rounded textbook calculations. Open a question, attempt it, then read its worked answer.
1 · Read a risk statement
Try first: An annual 99% VaR is ₹45,000. What does it mean?
Worked answer
Under a correctly calibrated continuous model, the probability of an annual loss greater than ₹45,000 is 1%. It is not a 1% probability of losing exactly ₹45,000, a maximum loss, or a promise about the next year.
2 · Historical VaR
Try first: You have 100 equally weighted distinct daily scenario returns. The fifth-worst is −2.1%. Portfolio value is ₹10,00,000. Find nearest-rank 95% VaR.
Worked answer
Tail size = 100 × 5% = 5. Signed cutoff = −2.1%. Positive monetary VaR = ₹10,00,000 × 2.1% = ₹21,000. Because this sample is discrete, exact strict-exceedance probability need not equal 5%; document the quantile convention.
3 · Single-asset normal VaR
Try first: Value ₹1,00,000, daily mean 0%, daily volatility 1.5%, one-day 95% confidence.
Worked answer
VaR = ₹1,00,000 × 1.644853627 × 0.015 = ₹2,467.28.
=100000*NORM.S.INV(95%)*1.5%
4 · Parametric VaR with and without a mean
Try first: Value ₹5,00,000, daily volatility 1.8%, one-day 95% confidence. Calculate VaR for mean 0%, then mean +0.08%.
The variance must be recomputed from the stated matrix; 0.000584 is not the result of these inputs.
7 · Combine horizon and confidence · keep the mean
Try first: Value ₹2,00,000; weights 50%, 30%, 20%; daily volatilities 1.8%, 2.5%, 3.2%; daily means 0.04%, 0.06%, 0.08%; correlations 0.4, 0.3, 0.6 for A–B, A–C, B–C. Find one-day and ten-day VaR at 95% and 99%.
Worked answer
Daily portfolio mean = 0.054% = 0.00054. Covariances = 0.00018, 0.0001728 and 0.00048. Portfolio variance = 0.00032437; volatility = 1.8010%.
Use VaR = V × [z × daily volatility × √T − daily mean × T].
Horizon
95% VaR
99% VaR
One day
₹5,816.85
₹8,271.63
Ten trading days
₹17,656.03
₹25,418.73
Mean scales by T; volatility scales by √T. Multiplying total one-day VaR by √10, or multiplying it by a Z-score ratio, would incorrectly scale its nonzero mean component.
The stated volatilities are annual, so this is an annual model result. The correct portfolio volatility is approximately 11.1542%, not 10.85%.
9 · Monte Carlo ordering
Try first: At 95% confidence with 10,000 equally likely scenarios, how do you find VaR and ES?
Worked answer
Revalue the portfolio and sort P&Ls from worst to best. Under the article’s nearest-rank convention, VaR comes from rank 500; ES averages the worst 500 with the appropriate boundary convention. If instead sorting positive losses from smallest to largest, the loss quantile is the upper 95th percentile—not the lower 5th percentile.
Try first: Value ₹10,00,000, daily volatility 2.5%, mean zero, one-day 95% confidence. Calculate both measures.
Worked answer
VaR = ₹10,00,000 × 2.5% × 1.644853627 = ₹41,121.34. ES multiplier = φ(z95%) ÷ 0.05 ≈ 2.062712808. ES = ₹10,00,000 × 2.5% × 2.062712808 = ₹51,567.82.
These two values use the same distribution and inputs. The normal ES should not be interpreted as the average beyond an unrelated ₹25,000 cutoff.
Before accepting an answer: check the horizon, confidence, decimal-return units, sign convention, portfolio mean and covariance arithmetic. Distinguish genuine corrections from small differences caused by rounding Z-scores.