How Dem Calculate Value at Risk: 3 Methods

Value at risk, or VaR, dey calculate by setting a time horizon, setting a confidence level, then reading one percentile from return series. If 1-day 99% VaR na 2%, e mean say loss go stay below 2% for 99 out of every 100 trading sessions, but e go pass 2% for the hundredth session. Three standard methods dey answer that question from the same inputs, but dem fit disagree more than most people expect.

Wetin value at risk really dey measure

VaR na quantile for loss distribution. Arrange every daily return for one sample from worst reach best. Then move one percent from the bad end. The return wey you reach there, write am as positive loss, na the one-day 99% historical VaR. Nothing for this method promise worst-case result. E just mark the edge of the area wey the estimate still dey describe.

Na this property readers dey usually understand wrong. 99% VaR mean say the worst one percent of days dey beyond the threshold. But e no talk how far beyond. Maximum drawdown answer another question: the peak-to-trough loss wey portfolio actually experience. The two measures fit rank the same two portfolios in opposite order.

Every figure for here come from one series: SPY daily closes from the beginning of 2010 reach the end of 2025, converted to close-to-close percentage changes. The window no dey roll; e stay fixed. Each panel rebuild the series from those same dates, so the numbers no dey change between runs.

WITH
px AS
(
    SELECT date, max(toFloat64(close)) AS close_px
    FROM global_markets.stocks_daily_aggs
    WHERE ticker = 'SPY'
      AND date >= '2009-12-01'
      AND date <  '2026-01-01'
    GROUP BY date
),
rets AS
(
    SELECT date, 100 * (close_px / prev_close - 1) AS ret_pct
    FROM
    (
        SELECT date, close_px,
               lagInFrame(close_px, 1) OVER (ORDER BY date ROWS BETWEEN 1 PRECEDING AND CURRENT ROW) AS prev_close
        FROM px
    )
    WHERE date >= '2010-01-01' AND prev_close > 0
)
SELECT
    toString(toYear(date))        AS year,
    count()                       AS sessions,
    round(avg(ret_pct), 3)        AS mean_return_pct,
    round(stddevSamp(ret_pct), 2) AS daily_sigma_pct
FROM rets
GROUP BY year
ORDER BY year

The series cover 16 calendar years, with 252 sessions for 2010 alone. Two things about am matter for everything wey follow. The average session almost no matter for this horizon: 0.054% for 2010, compared with daily standard deviation, sigma, of 1.13%. And sigma no be constant. 2020 run at 2.11% per day, while 2017 run at 0.43%. One sigma no fit describe both.

How dem dey calculate value at risk, three ways

Historical VaR: read percentile from wetin happen

Arrange the actual returns from lowest to highest, then take the percentile. E no assume any distribution, and na this make the method attractive. But e assume say the sample already get the kind day wey the estimate suppose cover. If you push confidence level far enough, na only the few worst sessions inside the whole window go determine the answer.

Parametric VaR: mean minus z times sigma

Summarise the series with mean and sigma, then assume say returns follow normal distribution. VaR na z times sigma minus the mean, where z na the standard normal quantile: 1.645 at 95%, 2.326 at 99%, 3.090 at 99.9%. The calculation quick, but the assumption dey fail for one clear direction. Daily equity returns dey cluster tighter than normal curve for the middle, and dem reach much farther for the extremes. Sigma here na the denominator wey also dey inside the Sharpe ratio, and e carry the same blind spot when data get fat tails.

Monte Carlo VaR: simulate am, keep the seed fixed

Generate large synthetic sample from an assumed process, then read the percentile from the simulated draws. The panel below use 40,000 standard normal draws wey Box-Muller transform build from hash-seeded uniform sequence. Then dem scale am to the series mean and sigma. The seed dey inside the SQL, so the draws go remain identical every time dem rerun am. Simulation give flexibility, path dependence and multi-asset correlation, but e no automatically give realism. If you give am normal distribution, e go return the parametric answer plus sampling noise. If you resample the observed returns instead, like the technique behind bootstrapped confidence intervals, the real tail still dey inside the analysis.

WITH
px AS
(
    SELECT date, max(toFloat64(close)) AS close_px
    FROM global_markets.stocks_daily_aggs
    WHERE ticker = 'SPY'
      AND date >= '2009-12-01'
      AND date <  '2026-01-01'
    GROUP BY date
),
rets AS
(
    SELECT date, 100 * (close_px / prev_close - 1) AS ret_pct
    FROM
    (
        SELECT date, close_px,
               lagInFrame(close_px, 1) OVER (ORDER BY date ROWS BETWEEN 1 PRECEDING AND CURRENT ROW) AS prev_close
        FROM px
    )
    WHERE date >= '2010-01-01' AND prev_close > 0
),
emp AS
(
    SELECT
        avg(ret_pct)                  AS mu,
        stddevSamp(ret_pct)           AS sd,
        quantileExact(0.100)(ret_pct) AS h90,
        quantileExact(0.050)(ret_pct) AS h95,
        quantileExact(0.025)(ret_pct) AS h975,
        quantileExact(0.010)(ret_pct) AS h99,
        quantileExact(0.005)(ret_pct) AS h995,
        quantileExact(0.001)(ret_pct) AS h999
    FROM rets
),
draws AS
(
    SELECT
        quantileExact(0.100)(z) AS z90,
        quantileExact(0.050)(z) AS z95,
        quantileExact(0.025)(z) AS z975,
        quantileExact(0.010)(z) AS z99,
        quantileExact(0.005)(z) AS z995,
        quantileExact(0.001)(z) AS z999
    FROM
    (
        SELECT sqrt(-2 * log(u1)) * cos(2 * pi() * u2) AS z
        FROM
        (
            SELECT
                (cityHash64('var-seed-u1', i) % 999999937 + 1) / 999999938.0 AS u1,
                (cityHash64('var-seed-u2', i) % 999999937 + 1) / 999999938.0 AS u2
            FROM (SELECT arrayJoin(range(40000)) AS i)
        )
    )
)
SELECT
    tupleElement(lvl, 1)                                                  AS confidence,
    round(-1 * tupleElement(lvl, 2), 2)                                   AS historical_var_pct,
    round(tupleElement(lvl, 3) * sd - mu, 2)                              AS parametric_var_pct,
    round(-1 * (mu + sd * tupleElement(lvl, 4)), 2)                       AS monte_carlo_var_pct,
    round(-1 * tupleElement(lvl, 2) - (tupleElement(lvl, 3) * sd - mu), 2) AS method_spread
FROM
(
    SELECT
        mu,
        sd,
        arrayJoin([
            ('90.0%', h90,  1.281552, z90,  1),
            ('95.0%', h95,  1.644854, z95,  2),
            ('97.5%', h975, 1.959964, z975, 3),
            ('99.0%', h99,  2.326348, z99,  4),
            ('99.5%', h995, 2.575829, z995, 5),
            ('99.9%', h999, 3.090232, z999, 6)
        ]) AS lvl
    FROM emp
    CROSS JOIN draws
)
ORDER BY tupleElement(lvl, 5)

For 95.0% level, the three methods dey within one point of each other: 1.66% historical, 1.74% parametric, 1.71% simulated. For 99.9%, dem separate: 5.85% historical against 3.31% parametric, with gap of 2.54 percentage points on the same data. The simulated column dey beside the parametric column for every level. Na this be the lesson, no be defect. Simulation go reproduce any distribution wey you give am.

Read the panel down one column, and each increase for confidence go widen the threshold. Read am across one row, and method choice barely show for the middle of the distribution, but e dominate the tail. Any VaR limit wey person quote without stating the method and the window no be number anybody else fit reproduce.

Wetin VaR no dey tell you: expected shortfall

Expected shortfall, wey dem dey also call conditional VaR, na the average loss for days wey breach VaR. VaR dey show where the tail start. Expected shortfall dey measure wetin dey inside am.

WITH
px AS
(
    SELECT date, max(toFloat64(close)) AS close_px
    FROM global_markets.stocks_daily_aggs
    WHERE ticker = 'SPY'
      AND date >= '2009-12-01'
      AND date <  '2026-01-01'
    GROUP BY date
),
rets AS
(
    SELECT date, 100 * (close_px / prev_close - 1) AS ret_pct
    FROM
    (
        SELECT date, close_px,
               lagInFrame(close_px, 1) OVER (ORDER BY date ROWS BETWEEN 1 PRECEDING AND CURRENT ROW) AS prev_close
        FROM px
    )
    WHERE date >= '2010-01-01' AND prev_close > 0
),
qs AS
(
    SELECT
        quantileExact(0.100)(ret_pct) AS q90,
        quantileExact(0.050)(ret_pct) AS q95,
        quantileExact(0.025)(ret_pct) AS q975,
        quantileExact(0.010)(ret_pct) AS q99,
        quantileExact(0.005)(ret_pct) AS q995,
        quantileExact(0.001)(ret_pct) AS q999
    FROM rets
),
tails AS
(
    SELECT
        any(q90)                        AS var90,
        any(q95)                        AS var95,
        any(q975)                       AS var975,
        any(q99)                        AS var99,
        any(q995)                       AS var995,
        any(q999)                       AS var999,
        avgIf(ret_pct, ret_pct <= q90)  AS es90,
        avgIf(ret_pct, ret_pct <= q95)  AS es95,
        avgIf(ret_pct, ret_pct <= q975) AS es975,
        avgIf(ret_pct, ret_pct <= q99)  AS es99,
        avgIf(ret_pct, ret_pct <= q995) AS es995,
        avgIf(ret_pct, ret_pct <= q999) AS es999
    FROM rets
    CROSS JOIN qs
    HAVING countIf(ret_pct <= q999) > 0
)
SELECT
    tupleElement(lvl, 1)                                  AS confidence,
    round(-1 * tupleElement(lvl, 2), 2)                   AS historical_var_pct,
    round(-1 * tupleElement(lvl, 3), 2)                   AS expected_shortfall_pct,
    round(tupleElement(lvl, 3) / tupleElement(lvl, 2), 2) AS es_to_var_ratio
FROM
(
    SELECT
        arrayJoin([
            ('90.0%', var90,  es90,  1),
            ('95.0%', var95,  es95,  2),
            ('97.5%', var975, es975, 3),
            ('99.0%', var99,  es99,  4),
            ('99.5%', var995, es995, 5),
            ('99.9%', var999, es999, 6)
        ]) AS lvl
    FROM tails
)
ORDER BY tupleElement(lvl, 4)

For 99.0%, VaR for this series na 3.09% and expected shortfall na 4.43%, or 1.44 times the threshold. Under normal distribution, that ratio for dey near 1.15 for the same level. Even for 99.9%, where the threshold don already reach 5.85%, the average breach day come to 8.14%. Limit wey dem write only on VaR dey treat every breach like the same event, and the ratio column dey show how wrong that assumption be.

1-day 99% VaR mean the same thing for institution and trader?

No. The main difference na horizon. Trader wey dey flat overnight dey hold risk for some hours, so one-session threshold dey match the holding period, even though intraday movement fit go well beyond the close-to-close number. Institution wey dey fund long-dated liabilities dey hold positions for years. Its exposure dey run across quarters. So its 1-day 99% figure na more for capital and monitoring, no be full description of the risk wey e carry. Bank capital rules use 1-day 99% VaR for many years. Later, Basel market risk framework move the measure to 97.5% expected shortfall.

The normal way to connect different horizons na to scale with the square root of time. Multiply the one-session figure by the square root of the number of sessions for the horizon. This step assume say returns dey independent and sigma dey constant. The year-by-year sigma column above already show where this assumption dey fail.

WITH
px AS
(
    SELECT date, max(toFloat64(close)) AS close_px
    FROM global_markets.stocks_daily_aggs
    WHERE ticker = 'SPY'
      AND date >= '2009-11-01'
      AND date <  '2026-01-01'
    GROUP BY date
),
multi AS
(
    SELECT
        date,
        100 * (close_px / p1  - 1) AS r1,
        100 * (close_px / p5  - 1) AS r5,
        100 * (close_px / p10 - 1) AS r10,
        100 * (close_px / p20 - 1) AS r20
    FROM
    (
        SELECT
            date,
            close_px,
            lagInFrame(close_px, 1)  OVER (ORDER BY date ROWS BETWEEN 20 PRECEDING AND CURRENT ROW) AS p1,
            lagInFrame(close_px, 5)  OVER (ORDER BY date ROWS BETWEEN 20 PRECEDING AND CURRENT ROW) AS p5,
            lagInFrame(close_px, 10) OVER (ORDER BY date ROWS BETWEEN 20 PRECEDING AND CURRENT ROW) AS p10,
            lagInFrame(close_px, 20) OVER (ORDER BY date ROWS BETWEEN 20 PRECEDING AND CURRENT ROW) AS p20
        FROM px
    )
    WHERE date >= '2010-01-01' AND p20 > 0
),
q AS
(
    SELECT
        quantileExact(0.01)(r1)  AS q1,
        quantileExact(0.01)(r5)  AS q5,
        quantileExact(0.01)(r10) AS q10,
        quantileExact(0.01)(r20) AS q20
    FROM multi
)
SELECT
    tupleElement(h, 1)                                             AS horizon,
    round(-1 * tupleElement(h, 3), 2)                              AS actual_var_pct,
    round(-1 * q1 * sqrt(tupleElement(h, 2)), 2)                   AS sqrt_scaled_var_pct,
    round(tupleElement(h, 3) / (q1 * sqrt(tupleElement(h, 2))), 2) AS actual_to_scaled_ratio
FROM
(
    SELECT
        q1,
        arrayJoin([
            ('1 session',   1.0,  q1,  1),
            ('5 sessions',  5.0,  q5,  2),
            ('10 sessions', 10.0, q10, 3),
            ('20 sessions', 20.0, q20, 4)
        ]) AS h
    FROM q
)
ORDER BY tupleElement(h, 4)

The first row na identity check. For 1 session, the scaled figure equal the measured figure, with ratio of 1. By 20 sessions, the two don separate. And e no separate in the direction wey the shortcut warning usually point to. Scaling the one-session number gives 13.8%, while returns measured across that horizon print 10.7%, with ratio of 0.78. For this window, the scaled figure dey above the measured multi-session tail.

Two patterns dey work against each other here. A fat-tailed single-session distribution dey become thinner as returns join together. So the aggregated 99% quantile then widens more slowly than the square root of the horizon. Volatility clustering dey push the other way by putting violent sessions inside one window. For this series, the first pattern stronger. But nobody fit guarantee either direction for another window or another asset. Na the main point be this: the multiplier na assumption, no be measurement. The same clustering na the property wey volatility targeting dey use when e resize positions.

One more limit, make we state am plainly. Everything above na single-asset VaR on a broad index fund. A book of ten correlated names get concentration risk. Portfolio VaR handles this through a correlation estimate, but correlations dey move most at the exact time wey the estimate matter pass.

FAQ

Wetin be 1-day 99% VaR?

Na the loss level wey the worst 1 out of every 100 trading days go pass, measured across one trading session. For the series above, the historical estimate na 3.09%. The figure only show the threshold; e no talk about how big losses fit be after that point.

Which VaR calculation method dey most accurate?

None of the three dey accurate by itself, because each one answer the question under different assumptions. Historical VaR follow the sample wey dem give am, but e no fit talk about anything wey the sample miss. Parametric VaR cheap, but e usually understate equity tails. Monte Carlo only good as the distribution wey enter am.

Wetin be the difference between VaR and expected shortfall?

VaR na the threshold for a chosen confidence level. Expected shortfall na the average loss for days wey breach that threshold. For this series, expected shortfall at the 99% level dey 1.44 times VaR, compared with about 1.15 times under a normal distribution.

You fit convert 1-day VaR to 10-day VaR?

Multiplying am by the square root of 10 na the standard shortcut. E assume say returns dey independent and volatility remain constant. The horizon panel measure the difference: across 20 sessions, the scaled figure come to 13.8%, while the measured figure na 10.7%.

Every panel above get the SQL wey produce am. To run the same three calculations for another ticker or window, ask for am in plain English on the Strasmore terminal.