Historical VaR and expected shortfall verification
Fixed fictional reference; not market observations

1–1,000 returns, each between −1,000,000% and +1,000,000%, with at most 6 decimal places. Up to 64 characters per line and 65,000 overall. Confidence: strictly between 0% and 100%, at most 6 decimals. Optional value: greater than 0 and at most 1,000,000,000,000,000, at most 6 decimals. Exact fractions determine ranks and tail weights; only display values are rounded. These are tool limits, not an economic validation of a return below −100%.

1. Record the return source, observation dates, equal horizon, simple-return definition, fees, missing observations and exposure basis. A return below −100% requires a meaningful denominator/exposure interpretation; the numerical tool cannot validate it. Do not mix horizons or silently fill gaps.

2. Enter one signed percentage per line: 3 means 3%. Choose confidence as a percentage strictly between 0 and 100. Keep the original order and source record. A positive fixed value is optional; label its unit. Without it there are no monetary outputs.

3. Fictional reference: returns 3; 2; 1; 0; −1; −2; −3; −4; −6; −10%, confidence 85%. Losses ascending are −3; −2; −1; 0; 1; 2; 3; 4; 6; 10%. N=10, c=0.85, rank=ceil(8.5)=9, VaR=6%.

4. Tail mass m=N*(1−c)=1.5. Descending losses 10% and 6% receive raw weights 1 and 0.5; normalized weights 2/3 and 1/3. Contributions are 20/3 and 2 percentage points. ES=26/3%≈8.66666666666667%. The mean of all losses≥VaR is (10+6)/2=8%, not ES. At confidence 90%, m=1, VaR=6%, ES=10%, with no extra boundary row.

5. Spreadsheet: returns B2:B11; losses C2=-B2 copied down. Enter K2=85 as an ordinary number, not 85% in percentage format. K3=ROWS(B2:B11); K4=K3*(1-K2/100); VaR in K5=SMALL(C2:C11,ROUNDUP(K3*K2/100,0)). D2=LARGE($C$2:$C$11,ROWS(D$2:D2)); E2=MAX(0,MIN(1,$K$4-ROWS(D$2:D2)+1)); F2=E2/$K$4; G2=F2*D2. Copy D2:G2 through row 11. ES in K6=SUM(G2:G11). The naive conditional mean is AVERAGEIF(C2:C11,">="&K5,C2:C11). E sums to 1.5, F to 1, G to 26/3. LARGE checks sorted values but does not preserve tie identities: for a row audit, sort a copy of loss and original source ID together by loss descending, then source ID ascending. English function names and commas may require local names and semicolons. Do not round early; spreadsheet arithmetic is not exact rational arithmetic.

6. Optional fixed value 10,000 USD: VaR=10,000*6/100=600 USD; ES=10,000*(26/3)/100=2,600/3 USD≈866.666666666667. Negative VaR/ES remain negative. A tail mass below one makes ES equal the worst observation; it does not create new evidence.

7. Compare original inputs, confidence, rank, exact tail mass, raw weights (sum m), normalized weights (sum 1) and contributions (sum ES). Import exact CSV columns as text. A matching calculation is not a forecast, a maximum loss, a trading recommendation or an FRTB calculation. Document missing data before interpreting any statistic.

Your verification record
Source / dates / one-period horizon / return definition: ____________________
Exposure denominator / fees / gaps / unverified data: ____________________
Confidence / quantile convention / tail mass / tie order: ____________________
Optional fixed value / currency or unit / valuation date: ____________________
Arithmetic and tail allocation checked by / on: ____________________

This component keeps entries in page memory. It does not send them to a server or write persistent browser storage. Sitewide analytics are separate.