Expected Shortfall Formula: Reproduce Historical VaR and ES

An expected shortfall number is useful only when you can reconstruct what entered its tail. This guide takes a supplied series of simple returns, converts it to losses, sorts the observations and shows the exact weight of every included row. The result is a reproducible calculation record, not a forecast. You can use the fixed example first and then replace it with comparable observations of your own.

Each return must describe one period of the same length and the same return definition. Do not mix daily and weekly rows, portfolio returns and individual-trade percentages, or percentages calculated on different capital bases without first establishing comparability. Record the source, date range, period length, valuation timing, fees and treatment of external cash flows. The component accepts numbers, so it cannot verify those properties for you.

The input is already in percent: 3 means 3%, −2 means −2%, and 0 means an unchanged value. It is not a decimal-return field in which 0.03 means 3%. If your source stores decimal returns, multiply them by 100 before pasting and document that conversion. Missing observations remain missing information; an invented zero would change both the sample size and the tail.

All observations in this checker have equal empirical probability 1/N. There are no recency weights, fitted distribution, volatility adjustment or extrapolated events. The date range is context you must retain separately; the number of lines alone cannot demonstrate that the history is representative.

Set Lᵢ = −rᵢ, where rᵢ is the entered percentage return. A return of −6% becomes a loss of +6%; a return of +3% becomes a loss of −3%. A positive loss denotes a decline and a negative loss denotes a gain. The sign is retained throughout the table, chart and downloads.

Let N be the number of valid observations and c the confidence level as a fraction. Entering 85 in the confidence field means c = 0.85. Sort losses in ascending order and select k = ceil(N × c), counting from 1. This guide defines VaR_c = L_(k). The rule selects an observed value without interpolating between adjacent losses. Here ceil means rounding upward to an integer, leaving an integer unchanged.

The official R quantile documentation distinguishes nine algorithms: type 1 is the inverse empirical distribution function; its default is type 7. Our stated rank rule matters more than a shared function name. A spreadsheet percentile function with interpolation can therefore return a different VaR without either calculation having used the same convention.

VaR identifies a location in this supplied loss distribution; it does not describe the severity of every observation beyond that location. Nor is it a maximum possible loss. At a discontinuity, the empirical probability below or at the selected loss need not equal c exactly. Keep the confidence, rank and selected observation alongside the reported value so that the result can be checked.

There is no annualization step. If your inputs are daily returns, the result describes that one-day observation horizon; monthly inputs describe their monthly horizon. Converting between horizons requires additional assumptions and data outside this checker.

The worst tail occupies empirical mass m = N × (1 − c), measured in observation equivalents. Sort losses from largest to smallest, retaining the original row number. Give weight 1 to each of the floor(m) worst observations and, if needed, give the next observation the remaining fraction. All subsequent weights are zero. At an integer m there is no extra boundary observation. The function floor rounds downward to an integer.

For descending rank j, the raw weight is wⱼ = max(0, min(1, m − j + 1)). Expected shortfall is ES_c = Σ(wⱼLⱼ)/m. A raw weight of 1/2 means half of one observation’s empirical mass is used; it does not mean that the historical loss itself was halved. The normalized weight is wⱼ/m, and its contribution to ES is (wⱼ/m)Lⱼ.

Three checks make the calculation auditable: raw weights sum to m, normalized weights sum to 1, and row contributions sum to ES. Weights have no currency or percent unit. Losses and contributions use the entered percentage scale; an individual contribution is an additive part of ES, not another observed return. Contributions are percentage points (pp) of the reported ES: the reference contributions of 20/3 pp and 2 pp combine into an ES of 26/3%.

Acerbi and Tasche explain why discrete distributions require careful boundary treatment. Here the empirical calculation includes only the mass needed to fill the tail. A conditional average over every row with L ≥ VaR can use too much mass, including tied boundary observations, and need not equal this ES. Its tail-fraction notation α corresponds to 1 − c here.

For equal losses, original row order determines which row receives any fractional weight. That bookkeeping choice does not change ES because the tied values are equal. Reordering the same observations can change which tied row is highlighted, but cannot change VaR or ES. Preserve row identifiers if you need to explain the allocation.

Use these fictional one-period returns: 3, 2, 1, 0, −1, −2, −3, −4, −6, −10 percent. They are a teaching dataset, not market history. Set confidence to 85. Then N = 10, c = 0.85, the ascending quantile rank is ceil(8.5) = 9, and the ninth loss is 6%. The tail mass is 10 × 0.15 = 1.5 observations.

Starting at the worst loss, take all of the 10% observation and half of the 6% observation. The weighted loss sum is 1 × 10 + (1/2) × 6 = 13 in the percentage scale. Divide by tail mass 1.5: ES = 13/1.5 = 26/3% ≈ 8.666666666666667%. The normalized weights are 2/3 and 1/3; their contributions are 20/3 and 2, summing to 26/3.

The naive condition L ≥ 6 includes both full observations, so its average is (10 + 6)/2 = 8%. It has used two observations when the specified tail has mass 1.5. The discrepancy is a boundary-definition difference, not a rounding error. This diagnostic is displayed separately and is not substituted for ES.

At 90% confidence, ceil(10 × 0.9) = 9 still selects the 6% VaR, while the tail mass is exactly 1 and ES is 10%. At 95%, only half of the worst observation is needed; dividing its weighted loss by that half returns 10% again. This is a small historical tail, not evidence that its value has become precisely estimated.

An optional fixed base value V = 10000 USD converts a percentage loss using amount = V × lossPercent/100. The reference VaR is 600 USD and ES is 2600/3 USD ≈ 866.6666666666667 USD. These amounts summarize the same percentage observations on that chosen base; they are not account balances, margin requirements, guaranteed limits or additional losses to add to the percentages.

Paste one signed number per line. A point or comma may be used as the decimal separator within a value; neither is a separator between observations. Do not paste a header, percent sign, exponent notation, thousands separator or a comma-separated list. Outer spaces are trimmed and one final newline is allowed; an internal empty row or an additional blank line is rejected rather than filled or silently removed. Use the keyboard minus sign - for a negative input.

The bounded input accepts 1–1000 returns between −1000000 and +1000000 percent, with at most six decimal places. Each raw line may contain at most 64 characters and the full series at most 65000. Confidence must be strictly between 0 and 100 percent, with at most six decimal places and 32 raw characters. Those limits control input size; they do not certify an economically valid return definition.

Returns below −100% are not clipped. Such a row needs an appropriate exposure or capital basis; verify what your source actually measured. The optional base value accepts a positive amount up to 1000000000000000, with six decimal places and 48 raw characters. Leave it blank for percentage results only. The unit label is 1–24 characters, starts with a letter or digit, and permits letters, digits, combining marks, spaces, a dot, underscore, slash or hyphen thereafter.

Choose a preset or enter your data, set confidence and select “Calculate and inspect”. Check N, the quantile rank and the tail mass before reading VaR or ES. Inspect the ranked-loss chart and the complete row table; use the row selector to inspect an observation without replacing the calculated dataset. Changing a model input hides the old result and its download until you calculate again.

Exact fractions determine the boundary and calculation. Rounded decimal displays are identified as approximations and do not feed back into the model. The dynamic CSV belongs to the last successful calculation. Inputs remain in component memory and are not sent by the form to a server or written to persistent storage; this statement does not describe the site’s separate analytics.

Without JavaScript, the fixed reference, its complete table and input CSV, exact reference CSV and verification protocol remain available. The interactive controls are disabled. The input CSV is a static teaching file, not a promise of an implemented CSV importer.

Use the ten-row example before substituting another range. Put the entered percentage numbers in B2:B11 and source row numbers in A2:A11. Keep the cells as numbers: entering 3 and then applying a spreadsheet percentage format would display 300%, which is a different scale. In C2 enter =-B2 and copy down to create losses. Check that all ten intended cells are numeric and that none are missing.

Put 85 in K2. Use =ROWS(B2:B11) in K3 for N and =K3*(1-K2/100) in K4 for m. In K5, =SMALL(C2:C11,ROUNDUP(K3*K2/100,0)) implements this guide’s VaR convention. Do not replace it with an interpolating percentile function while expecting the same definition. For the sample, K3 = 10, K4 = 1.5 and K5 = 6.

In D2 enter =LARGE($C$2:$C$11,ROWS(D$2:D2)) and copy through D11. This produces losses in descending order. In E2 enter =MAX(0,MIN(1,$K$4-ROWS(D$2:D2)+1)) and copy down for raw tail weights. In F2 use =E2/$K$4 for normalized weights; in G2 use =F2*D2 for ES contributions. Copy both formulas down. For a row-level audit, sort a copy of losses and original row identifiers together by loss descending, then source row ascending. The LARGE column checks ordered values alone and does not preserve the identity of tied source rows.

The checks are =SUM(E2:E11) giving 1.5, =SUM(F2:F11) giving 1, and =SUM(G2:G11) giving 26/3 numerically. Put the last sum in K6. To display the contrasting conditional mean, =AVERAGEIF(C2:C11,">="&K5,C2:C11) gives 8. For a fixed value of 10000, =10000*K5/100 and =10000*K6/100 give the monetary summaries.

The formulas use English function names and comma argument separators. Your spreadsheet may require translated names, semicolons or decimal commas. Keep reference-cell meanings unchanged when adapting the syntax. Import CSV fraction and exact-value columns as text so software does not interpret a fraction as a date or round a long value. Spreadsheet decimal arithmetic is a separate approximation; at a sensitive boundary compare the exact rank and mass in the download before diagnosing a discrepancy.

When m < 1, only a fraction of the single worst observation fills the tail, so ES equals that observed loss. More decimal places do not add historical information. With N = 1, VaR and ES both equal the only loss for every allowed confidence. The calculation is defined, but the sample provides one observation rather than evidence about a broad range of severe outcomes.

If all observations are gains, the losses are negative and VaR or ES can be negative too. For returns 1, 2 and 3 percent, the worst observed loss is −1%. At 90% confidence, VaR = ES = −1%. Clipping that result to zero would change the requested empirical calculation. It does not establish that future losses are impossible.

A historical tail cannot include events absent from the supplied sample. Regime changes, stale valuations, missing observations, cost changes and inconsistent exposures can all make an apparently precise result poorly matched to the question. A longer series changes the sample; it does not automatically solve comparability or guarantee that the next loss lies within its range.

This tool does not estimate uncertainty bands, prove independence, validate a probability model or apply regulatory capital rules. It also does not convert daily returns into a multiday risk number. Document those missing capabilities instead of attaching an unsupported interpretation to the output.

A label such as CVaR is not enough to reconcile two reports. Ask for the actual definition, quantile rule, treatment of atoms or ties, weights and sample. Different implementations can use the same label while making different choices. Here the exact worst-tail-mass construction and the separate naive conditional mean make that distinction inspectable.

Save the original input sequence, confidence, return horizon, source dates, return definition and any optional base value. Export the calculated CSV together with the verification protocol. Record unresolved items explicitly: for example, whether a supplied return includes financing costs or whether one missing date was deliberately excluded. An unknown assumption should not be converted into a reassuring result.

• First compare N and every entered percentage, including signs and decimal scale.
• Then compare quantile rank, confidence and the full tail mass before comparing rounded outputs.
• Check whether a competing result includes all tied boundary observations or interpolates a quantile.
• Finally compare costs, horizon, weights and monetary base; only matching definitions justify expecting matching numbers.

This guide answers a different question from Sharpe and Sortino, which relate mean return above their respective benchmarks—Rf for Sharpe and MAR for Sortino—to specified variability or downside deviation. Historical ES is not maximum drawdown, because sorting observations removes their chronological path. Nor is it a risk-of-ruin estimate, which needs a separate model for a sequence of outcomes and a ruin condition.

The useful stopping point is a traceable agreement or a named unresolved difference. If the rows, convention and exact weights match, repeated recalculation of unchanged inputs adds no evidence. If they do not match, isolate that specific difference before expanding the exercise. No result here recommends a trade, hedge, leverage level or position size.

Is expected shortfall the average loss above VaR?

Not automatically in a finite sample. This checker averages exactly the worst mass N × (1 − c), allowing a fractional boundary row. Averaging all rows with loss ≥ VaR can include more mass, as the 85% example shows: 8% versus an ES of 26/3%.

Why does 90% confidence give VaR 6% but ES 10%?

With ten observations, the selected ascending rank is 9, where the loss is 6%. The tail mass is exactly one observation, so ES uses only the worst 10% loss. VaR and ES describe different features of the same sample.

Can VaR or ES be negative?

Yes. Gains become negative losses, and a tail containing gains can retain a negative value. The calculator does not floor it at zero. This historical sign does not guarantee that the next period cannot lose money.

What happens with one observation or a tail mass below one?

The worst observed loss determines ES. For N = 1 it also determines VaR. A valid arithmetic result does not mean there are enough observations to assess rare outcomes; the small tail mass is shown explicitly.

Do I need to enter a fixed base value?

No base value is needed for percentage VaR and ES. Leave the optional value blank and no monetary results are produced. The form still requires a unit label, but it labels amounts only when a value is supplied. A positive value scales losses on that fixed base; it does not convert currencies or establish an account balance.

Does this reproduce a regulatory expected-shortfall calculation?

No. It is an equally weighted historical one-period calculation with a disclosed empirical quantile and fractional tail convention. It contains no regulatory liquidity-horizon adjustments, capital rules, scenario calibration or forecast model.