Mbinu tatu za kuhesabu Value at Risk (VaR)

Value at risk (VaR)

Value at risk, au VaR, huhesabiwa kwa kuweka muda maalum, kuweka kiwango cha imani, na kisha kusoma asilimia kutoka kwenye mfululizo wa mapato. VaR ya siku moja ya asilimia tisini na tisa ya asilimia mbili ingemaanisha kuwa hasara inabaki chini ya asilimia mbili katika siku tisini na tisa kati ya kila siku mia moja na kuizidi katika siku ya mia moja. Mbinu tatu za kawaida hujibu swali hilo kutoka kwa data sawa, na zinatofautiana kwa kiasi kikubwa kuliko ambavyo watu wengi wanatarajia.

Thamani ya hatari (VaR) hupima nini hasa

VaR ni quantile ya usambazaji wa hasara. Panga kila faida ya kila siku katika sampuli kutoka mbaya zaidi hadi bora zaidi, tembea asilimia moja ya njia kutoka upande wa hasara, na faida unayofikia, ikionyeshwa kama hasara chanya, ndiyo 1-day 99% historical VaR. Hakuna chochote katika ujenzi huo kinachoahidi hali mbaya zaidi. Inaashiria ukingo wa eneo ambalo makadirio hayo huacha kuelezea.

Hiyo ndiyo sifa ambayo wasomaji huikosea mara nyingi zaidi. 99% VaR inasema kuwa asilimia moja ya siku mbaya zaidi ziko nje ya kizingiti hicho, na haisemi chochote kuhusu umbali wake. Maximum drawdown hujibu swali tofauti, hasara kutoka kilele hadi chini ambayo kitabu cha biashara kilipitia kihalisi, na viashiria hivi viwili vinaweza kupanga jozi moja ya kwingineko katika mpangilio tofauti.

Kila takwimu hapa chini inategemea mfululizo mmoja: bei za kufunga za kila siku za SPY kuanzia mwanzo wa mwaka 2010 hadi mwisho wa 2025, zilizobadilishwa kuwa mabadiliko ya asilimia ya kufunga-kwa-kufunga. Dirisha la muda limefungwa badala ya kusonga, na kila paneli hujenga upya mfululizo huo kutoka tarehe hizo hizo, hivyo namba hazibadiliki kati ya majaribio.

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

Mfululizo huo unachukua 16 miaka ya kalenda, 252 vipindi vya biashara katika 2010 pekee. Sifa mbili zake ni muhimu kwa kila kitu kinachofuata. Wastani wa kipindi cha biashara hauna umuhimu wowote katika upeo huu: 0.054% katika 2010, dhidi ya mkengeuko wa kawaida wa kila siku, sigma, wa 1.13%. Na sigma si thabiti. 2020 ilikuwa 2.11% kwa siku, 2017 ilikuwa 0.43%. Sigma moja haiwezi kuelezea zote mbili.

Jinsi Value at Risk inavyokokotolewa, njia tatu

Historical VaR: soma asilimia kutoka kwa yaliyotokea

Panga mapato halisi na uchukue asilimia husika. Hakuna mgawanyo unaodhaniwa, na hiyo ndiyo mvuto wake. Mbinu hii hudhani kuwa sampuli iliyopo tayari inajumuisha aina ya siku ambayo makadirio hayo yanalenga kuifunika. Ukisukuma kiwango cha imani (confidence level) mbali vya kutosha, ni vipindi vichache tu vibaya zaidi katika kipindi chote ndivyo vitatoa jibu.

Parametric VaR: wastani ukiondoa z mara sigma

Fupisha mfululizo wa data kwa kutumia wastani (mean) na sigma yake, kisha dhani kuwa mapato yanafuata mgawanyo wa kawaida (normal distribution). VaR ni z mara sigma ukiondoa wastani, ambapo z ni quantile ya kawaida: 1.645 kwa asilimia 95, 2.326 kwa asilimia 99, 3.090 kwa asilimia 99.9. Hesabu hii ni ya haraka na dhana hiyo inafeli katika mwelekeo maalum. Mapato ya kila siku ya hisa hujikusanya kwa karibu zaidi katikati kuliko mkondo wa kawaida na hufika mbali zaidi kwenye ncha (extremes). Sigma hapa ndiyo kigawanyo kinachobeba pia the Sharpe ratio, na huleta upofu uleule kwa data zenye ncha nene (fat-tailed data).

Monte Carlo VaR: igiza, huku mbegu ikiwa imefungwa

Chora sampuli kubwa ya kutengenezwa kutoka kwa mchakato unaodhaniwa na usome asilimia kutoka kwa michoro hiyo. Paneli iliyo hapa chini inatumia michoro 40,000 ya kawaida iliyojengwa kwa Box-Muller transform kutoka kwa mfuatano wa uniform uliotokana na hash, uliopimwa kulingana na wastani na sigma ya mfululizo huo. Mbegu (seed) iko kwenye SQL, kwa hivyo michoro ni sawa kila wakati inapoendeshwa tena. Uigaji (simulation) huleta unyumbufu, utegemezi wa njia, na uwiano wa mali nyingi, bila kuleta uhalisia: ukiipa mgawanyo wa kawaida, itakupa jibu la parametric likiwa na kelele za sampuli juu yake. Kwa upande mwingine, kuchagua upya mapato yaliyozingatiwa, mbinu iliyo nyuma ya bootstrapped confidence intervals, huweka ncha halisi (real tail) katika mchezo.

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)

Katika kiwango cha 95.0%, njia hizi tatu hutoa matokeo yanayokaribiana: 1.66% historical, 1.74% parametric, 1.71% simulated. Katika 99.9%, njia hizo hutofautiana: 5.85% historical dhidi ya 3.31% parametric, pengo la 2.54 asilimia kwenye data zinazofanana. Safu ya simulated hukaa kando ya safu ya parametric katika kila kiwango, na hilo ndilo funzo badala ya kuwa kasoro. Uigaji huzalisha upya mgawanyo wowote uliopokelewa.

Soma paneli kuelekea chini kwenye safu na kila hatua katika imani huongeza kizingiti. Soma kuelekea upande kwenye mstari na chaguo la mbinu halionekani sana katikati ya mgawanyo wakati linatawala kwenye ncha. Kikomo cha VaR kilichotajwa bila mbinu yake na kipindi chake si namba ambayo mtu mwingine yeyote anaweza kuizalisha upya.

Mambo ambayo VaR haikwambii: expected shortfall

Expected shortfall, ambayo pia huitwa conditional VaR, hupiga wastani wa hasara katika siku ambazo hasara imevuka kiwango cha VaR. VaR huashiria mahali ambapo mkia wa usambazaji (tail) huanzia. Expected shortfall hupima kile kilichomo ndani ya mkia huo.

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)

Katika 99.0%, VaR kwenye mfululizo huu ni 3.09% na expected shortfall ni 4.43%, au mara 1.44 ya kizingiti hicho. Chini ya usambazaji wa kawaida (normal distribution), uwiano huo ungekuwa karibu na moja nukta moja tano katika kiwango hicho. Hata katika 99.9%, ambapo kizingiti kimefika 5.85%, wastani wa siku ya uvunjifu wa kizingiti hicho hufikia 8.14%. Kikomo kilichoandikwa kwa kutumia VaR pekee huchukulia kila uvunjifu kama tukio sawa, na safu ya uwiano hupima jinsi dhana hiyo isivyo ya kweli.

Je, 1-day 99% VaR ina maana sawa kwa taasisi na mfanyabiashara?

Hapana, na tofauti hiyo inatokana zaidi na muda wa umiliki. Mfanyabiashara anayefunga nafasi zake kabla ya soko kufungwa hubeba hatari kwa saa chache tu, hivyo kizingiti cha kikao kimoja kinaendana na muda wake wa umiliki, ingawa mienendo ya ndani ya siku inaweza kuvuka kwa mbali namba ya close-to-close. Taasisi inayofadhili madeni ya muda mrefu hushikilia nafasi kwa miaka, mfiduo wake hudumu kwa robo mwaka, na namba yake ya 1-day 99% hufanya kazi kama nyenzo ya mtaji na ufuatiliaji badala ya maelezo ya hatari inayobeba. Kanuni za mtaji wa benki ziliundwa kwa msingi wa 1-day 99% VaR kwa miaka mingi, na mfumo wa hatari ya soko wa Basel baadaye ulihamishia kipimo hicho kwenye 97.5% expected shortfall.

Daraja la kawaida kati ya muda tofauti ni kupima kwa kutumia square root of time: zidisha namba ya kikao kimoja kwa square root ya muda wa umiliki katika vikao. Hatua hiyo inadhani kuwa mapato hayategemeani na yana sigma isiyobadilika, na safu ya sigma ya mwaka hadi mwaka hapo juu tayari inaonyesha kuwa nusu ya pili ya dhana hiyo inafeli.

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)

Safu ya kwanza ni uhakiki wa utambulisho: katika 1 session namba iliyopimwa inalingana na ile iliyopimwa, uwiano wa 1. Katika 20 sessions hizo mbili zinatofautiana, na si katika mwelekeo ambao njia ya mkato huonywa mara nyingi: kupima namba ya kikao kimoja kunatoa 13.8% wakati mapato yaliyopimwa katika muda huo yanatoa 10.7%, uwiano wa 0.78. Katika dirisha hili, namba iliyopimwa inakaa juu ya mkia wa vipindi vingi uliopimwa. Mifumo miwili inavutana hapa. Usambazaji wa kikao kimoja wenye mkia mnene (fat-tailed) hupungua kadiri mapato yanavyojumlishwa, na 99% quantile iliyojumlishwa hupanuka polepole zaidi kuliko square root ya muda wa umiliki. Ujumuishaji wa volatility (volatility clustering) hufanya kazi kinyume, ikikusanya vikao vikali ndani ya dirisha moja. Katika mfululizo huu, mfumo wa kwanza ni mkubwa kuliko wa pili, na hakuna mwelekeo unaohakikishiwa katika dirisha lingine au mali nyingine, na ndiyo hoja yenyewe: multiplier ni dhana, si kipimo. Ujumuishaji huo pia ni sifa ambayo volatility targeting hutegemea inaporekebisha ukubwa wa nafasi.

Kikomo kingine, kilichoelezwa wazi. Kila kitu hapo juu ni VaR ya mali moja kwenye mfuko mpana wa fahirisi. Kitabu cha majina kumi yanayohusiana hubeba concentration risk ambayo portfolio VaR hushughulikia kupitia makadirio ya correlation, na correlations husogea zaidi wakati ambapo makadirio hayo ni muhimu.

Maswali Yanayoulizwa Mara Kwa Mara

1-day 99% VaR ni nini?

Hii ni kiwango cha hasara ambacho siku moja kati ya siku mia moja za biashara mbaya zaidi hukizidi, ikipimwa katika kipindi kimoja cha soko. Kwenye mfululizo hapo juu, makadirio ya kihistoria ni 3.09%. Takwimu hii inataja kizingiti na haisemi chochote kuhusu ukubwa wa hasara zinazozidi kiwango hicho.

Ni njia ipi ya kukokotoa VaR iliyo sahihi zaidi?

Hakuna hata moja kati ya hizo tatu iliyo sahihi kwa nadharia, kwa sababu kila moja hujibu swali kwa kutumia dhana tofauti. Historical VaR ni mwaminifu kwa sampuli uliyopewa na haitoi maoni kuhusu chochote ambacho sampuli hiyo ilikosa. Parametric VaR ni ya gharama nafuu na hupunguza uzito wa hatari za mwisho (equity tails). Monte Carlo ni nzuri kulingana na usambazaji wa data unaoingizwa ndani yake.

Kuna tofauti gani kati ya VaR na expected shortfall?

VaR ni kizingiti katika kiwango cha imani kilichochaguliwa. Expected shortfall hupiga wastani wa hasara katika siku ambazo kizingiti hicho kinavukwa. Kwenye mfululizo huu, expected shortfall katika kiwango cha asilimia tisini na tisa ni mara 1.44 ya VaR, ikilinganishwa na takribani mara moja nukta moja tano chini ya usambazaji wa kawaida (normal distribution).

Je, unaweza kubadilisha 1-day VaR kuwa 10-day VaR?

Kuzidisha kwa mzizi wa mraba wa kumi ni njia ya mkato ya kawaida, na inadhani kuwa mapato yanajitegemea na yana volatility isiyobadilika. Paneli ya muda (horizon panel) inapima pengo hilo: katika vipindi ishirini, takwimu iliyokokotolewa kwa njia hiyo ilikuwa 13.8% dhidi ya 10.7% iliyopimwa kihalisi.

Kila paneli hapo juu ina SQL iliyoizalisha. Ili kufanya mahesabu hayo matatu kwenye ticker au kipindi kingine, omba hivyo kwa lugha rahisi kwenye terminal ya Strasmore.