5 metoder til at beregne historisk VaR i Excel

Hvad er det værste, der kan ske, hvis markedet pludselig vender? Alle investorer – fra private daytradere til tunge porteføljeforvaltere – stiller netop dét spørgsmål. Svaret gemmer sig ofte i tre små bogstaver: VaR (Value at Risk).

Men selv om VaR er standardværktøj i finansbranchen, kan beregningen virke både kryptisk og dyr i licenser, hvis du ikke har adgang til specialiseret software. Her kommer Excel til undsætning. Med de rigtige formler kan du forvandle det velkendte regneark til et kraftfuldt risikolab – og du behøver hverken VBA-kode eller dyre add-ins.

I denne artikel dykker vi ned i fem praktiske metoder til at beregne historisk VaR direkte i Excel. Fra den helt simple percentil-tilgang til mere avanceret filtrering og bootstrap, viser vi trin for trin, hvordan du:

Henter og klargør prisdata uden fejlkilder
Opsætter fleksible formler med moderne SORT, LET og dynamiske arrays
Tilpasser modellen til både enkeltaktier og komplekse porteføljer
Finjusterer resultaterne med vægtning, volatilitetsskala og resampling

Om du vil skærpe din risikostyring, imponere kollegerne med elegante Excel-løsninger eller bare nørde statistik i fritiden, så læn dig tilbage – du er kun få formler fra at mestre historisk VaR.

Indholdsfortegnelse

Metode 1: Simpel historisk VaR (percentil af afkast)

Hent prisdata
Brug f.eks. STOCKHISTORY (Microsoft 365) eller importer en CSV fra dit handelssystem.
=STOCKHISTORY("MSFT"; TODAY()-730; TODAY(); 0; 1; 0; 1)
Gem lukkekurser i kolonne B og datoer i kolonne A. Kontroller, at der ikke er tomme celler eller ikke-handelsdage, der skaber huller.
Beregn daglige afkast
I kolonne C:
=LET(P; B2:B; DROP(LN(P) - LN(OFFSET(P; -1; 0)); 1))
• Formlen anvender naturlige log-afkast (kan skiftes til simple =P/P[-1]-1).
• DROP fjerner den første #N/A-række, der opstår, fordi der ikke er et forudgående datapunkt.
Konvertér til tab (P&L)
VaR defineres som et potentielt tab, så vi vender fortegnet:
=-C2#
(hash-symbolet # udvider dynamisk til hele afkast-array’et).
Udtræk VaR som percentil
Antag et 99 % konfidensniveau (α = 1 %).
=PERCENTILE.EXC(-C2#; 0,99)
Giver dags-VaR som et positivt tal i samme enhed som afkast (dvs. 2 % betyder risiko for at tabe 2 % af positionen i morgen med 1 % sandsynlighed).
• INC vs. EXC: Ved store samples er forskellen minimal, men EXC placerer percentilen nøjagtigt mellem observationer (bias-fri).
• Konverter til monetær VaR: =VaR_afkast * position_værdi.

Valg af konfidensniveau og datasample

Branchestandarder er 95 % eller 99 % afhængigt af regulatoriske krav.
Et minimalt datasæt på 250 handledage (~1 år) anbefales; 1 000+ rækker giver mere robuste percentiler.
Overvej rullende vinduer (TAKE/DROP) for at holde analysen relevant: =PERCENTILE.EXC(TAKE(-C2#;250);0,99)

Håndtering af outliers

Problem	Løsning
Dataspikes pga. split/fejl	Visuelt tjek SORT’s top/bund: `=SORT(-C2#;1;-1)`
Ekstreme observationer	Overvej winsorisering (fast klip) eller anvend Metode 3-5 i stedet.

Simple vs. Log-afkast

Log-afkast: Additive over tid, passer til normalitetsantagelser; bruges i de fleste risikomodeller.
Simple afkast: Nemme at fortolke i %, men summerer ikke elegant. Giver næsten identiske resultater for daglige bevægelser < ±5 %.

Fortegn – Negativt tab?

VaR rapporteres typisk som et positivt tal (et tab), selvom afkastet er negativt. Derfor multipliceres afkastet med -1 inden percentilberegningen. Hvis du hellere vil rapportere VaR som et negativt tal – kompatibelt med nogle risikosystemer – spring blot 3. trin over.

Opsætning med dynamiske arrays og datatjek

=LET( kld; STOCKHISTORY("SPY"; TODAY()-1000; TODAY(); 0; 1; 0; 5); /* Prisdata */ lukk; INDEX(kld;;2); /* Lukkekurs */ r; DROP(LN(lukk) - LN(OFFSET(lukk; -1; 0)); 1); /* Logafkast */ tab; -r; /* Tab */ VaR; PERCENTILE.EXC(tab; 0,99); /* 1%-VaR */ VaR )

Indbyg fornuftskontrol: =ISNUMBER(VaR) bør være SAND, og histogrammer (DATA → Analyse → Histogram) kan visuelt bekræfte, at fordelingen ligner markedets virkelighed. Når alt stemmer, har du din første – og hurtigste – metode til historisk VaR i Excel.

Metode 2: Portefølje-historisk VaR (flere aktiver og vægte)

Hvor den simple historiske VaR kun kigger på et enkelt instruments afkastserie, kræver en portefølje‐VaR først og fremmest, at vi samler alle aktivers afkast på én fælles tidsakse og efterfølgende vægter dem korrekt. Nedenfor får du en komplet opskrift i Excel – fra rå prisfiler til det færdige VaR‐tal.

1. Saml og klargør prisdata

Importer priser fra f.eks. Yahoo Finance, Bloomberg eller et csv-ark. Læg hver aktie/ETF/obligation i sin egen kolonne (kolonne B:F i eksemplet) og datoer i kolonne A.
Harmonisér datoer – mangler et aktiv priser en given dag, få sat #N/A/NA() ind, så afkastet den dag kan springes over. Den nemme løsning er et VSTACK af alle datoer efterfulgt af UNIQUE og sortering – men Power Query eller xlookup-sløjfer er mere robuste.
Valutaomregning (hvis nødvendigt): Har du fx USD-aktier i en DKK-portefølje, tilføj en kolonne med daglige USD/DKK kurser og divider priserne, før afkastet udregnes.

2. Beregn afkastmatrixen

I første frie række under priserne (række 3 i eksemplet) indsættes formularen
```
=LN(B3:B500/B2:B499)
```
…som med Ctrl+Shift+Enter (eller automatisk ved dynamiske arrays i 365) giver logafkast for hele kolonnen. Kopiér vandret for alle aktiver. Sæt IFERROR(...;"") rundt om for at ignorere manglende priser.
Du ender med et afkastmatrix på fx 1.000 rækker × 5 aktiver (uden header).

3. Definér porteføljevægte

Læg vægtene (sum = 1) i en vandret række – eksempelvis i B1:F1:

Aktiv 1 Aktiv 2 Aktiv 3 Aktiv 4 Aktiv 5 0,25 0,25 0,20 0,15 0,15

Skal vægtene repræsentere kronebeløb, kan du også angive værdien af hele porteføljen i en celle (fx J1 = 10 000 000 DKK) og gange den ind senere.

4. Beregn daglige portefølje-p&l

Antag rebalancering til konstante procentvægtede positioner (daglig rebalancering). Indsæt i kolonne G (række 3 nedad):
```
=SUMPRODUCT($B$1:$F$1, B3:F3)
```
Eller med matrixnotation:
```
=MMULT(B3:F3, TRANSPOSE($B$1:$F$1))
```
Vil du hellere have P&L i kroner, ganges resultatet med porteføljeværdien:
```
=$J$1 * SUMPRODUCT($B$1:$F$1, B3:F3)
```
Ved buy-and-hold (ingen rebalancering) beregner du i stedet daglige positioner som en løbende værdi (cumulative return) og ganger afkastene ind i den driftede vægt. Det kræver en ekstra kolonne, der opdaterer positionernes værdi rekursivt.

5. Håndter manglende data

Rudimentær metode: Ekskludér hele dagen, hvis ét aktiv mangler afkast – brug IF(COUNT(B3:F3)<COLUMNS(B3:F3); NA(); ...).
Imputation: Lineær interpolation eller sidste kendte pris (forward fill). Husk at dokumentere, da det kan dæmpe VaR.

6. Udtræk var

Konvertér P&L til tab ved blot at sætte et minus-tegn foran (eller multiplicér med -1). Skriv i kolonne H:
```
= -G3
```
Antag et 1 % konfidensniveau (α = 1%):
```
=PERCENTILE.INC(H3:H1002; 0,99)
```
(eller PERCENTILE.EXC(...;0,99) afhængigt af konvention). Resultatet er din 1-dags, 99 % historiske VaR for hele porteføljen.

7. Tjekliste og tips

Brug SORT + DYNAMIC ARRAY TOGGLE (TAKE/DROP) for nemt at inspicere de værste observationer og spotte datofejl.
Sæt datavalidering på vægtene, så de altid summer 100 %.
Gem en statisk kopi af vægte og porteføljeværdi på VaR-datoen, hvis du vil kunne genskabe tallet senere.
Kombinér porteføljeafkastserien med Metode 3-5 for yderligere sofistikering (alder-vægte, filtrering, bootstrap).

Resultatet: ét enkelt VaR‐tal, der allerede indregner sam‐korrelationer mellem aktiverne, fordi selve afkasthistorikken naturligt indeholder perioder, hvor aktiver falder (eller stiger) samtidig. Dermed undgår du de kovarians- og distributionsantagelser, som parametiske metoder bygger på.

Metode 3: Alder-vægtet historisk VaR (EWMA-vægte)

Den alder-vægtede (EWMA) tilgang justerer den historiske VaR ved at give nylige prisbevægelser større betydning end gamle observationer. Dermed bliver riskomålet mere følsomt over for regimskift – f.eks. hvis markedet pludselig går fra ro til uro.

1. Opsæt dataserien

Importér daglige lukkekurser (f.eks. via STOCKHISTORY eller Power Query).
Beregn logafkast i kolonne B: =LN(A3/A2)
Skift fortegn, så tab er positive (valgfrit, men ofte brugt i VaR-regi): =-B3

2. Beregn eksponentielle vægte

For en daglig λ på 0,94 gælder:

Alder (dage) Vægt = (1-λ)*λ^(alder)0 (1-0,94)*0,94^0 = 0,061 0,06*0,94 = 0,05642 0,0564*0,94 = 0,0530⋯

I Excel kan du lade alder starte ved 0 for nyeste observation og bruge en SEKVENS:

=LET( n; ANTAL(B3:BX);  λ; 0,94; alpha; 0,99;  r; B3:BX;  w; (1-λ)*λ^SEKVENS(n;;;0);  ...)

3. Normaliser vægtene

De rå EWMA-vægte summerer ikke til 1, så vi skalerer dem:

w_norm = w / SUM(w)

Du kan føje det til LET-blokken:

wN; w/SUM(w);

4. Sortér afkast og vægte side-by-side

Her bruger vi SORTBY til at sortere efter afkast (r) – laveste (dårligste) først:

SORTBY( CHOOSE({1,2}; r; wN); r; 1 )

Excel returnerer en 2-kolonners matrix, hvor kolonne 1 er afkast og kolonne 2 er de tilhørende vægte.

5. Kumuler vægte og find var-percentil

Brug SCAN (eller LOPSUM i dansk Excel) til at lave kumulative summationer af vægtkolonnen.
Med XLOOKUP søger du det første punkt, hvor den kumulative sum når eller overstiger α (f.eks. 1 % eller 5 %).

=LET( n; ANTAL(r); λ; 0,94; α; 0,01;  r; B3:BX; w; (1-λ)*λ^SEKVENS(n;;;0); wN; w/SUM(w); mat; SORTBY( CHOOSE({1,2}; r; wN); r; 1 ); rf; INDEX(mat;;1);  wf; INDEX(mat;;2);  kum; SCAN(0; wf; LAMBDA(a,b; a+b)); VaR; XLOOKUP(α; kum; rf; "-"))

Resultatet (VaR) er et negativt logafkast; multiplicér evt. med porteføljens markedsværdi for at få beløbs-VaR.

6. Typiske valg af λ

Datasampling	λ-værdi	Halveringstid*
Daglig	0,94	≈ 11 dage
Ugentlig	0,97	≈ 23 uger
Månedlig	0,985	≈ 46 mdr

*Halveringstid: antal perioder før vægten falder til 50 % af den oprindelige.

7. Fordele og faldgruber

Mere rettidig VaR: reagerer hurtigere på ny volatilitet end simpel historik.
Fleksibilitet: Ved at skrue på λ kan du kalibrere en balance mellem stabilitet og reaktivitet.
Subjektiv parameter: Valget af λ påvirker resultatet kraftigt – backtest derfor flere værdier.
Databehov: Selv gamle observationer får stadig en (lille) vægt. Beskær derfor datasættet, når vægten bliver numerisk ubetydelig for at spare Excel-ressourcer.

8. Hvornår er metoden bedre end simpel historik?

Hvis markedsvolatiliteten er ikke-stationær (perioder med skiftende uro), vil en simpel, ligevægtet VaR undervurdere risikoen efter rolige perioder og overvurdere den efter chok. EWMA-metoden justerer naturligt for denne asymmetri og giver derfor typisk færre VaR-brud ved backtesting.

Tip: Indpak ovenstående i en LAMBDA-funktion (f.eks. EWMA_VAR) for at kunne genbruge logikken på tværs af ark og porteføljer.

Metode 4: Filtreret historisk VaR (volatilitetsskaleret)

Den filtrerede historiske VaR fjerner først volatilitetsskyl fra afkastserien, beregner percentilen på de ”udjævnede” afkast og skalerer den derefter op med den aktuelle volatilitet. Sådan sættes det op i Excel:

Forbered data
Hent prisdata (f.eks. via STOCKHISTORY() eller Power Query), sorter efter dato og beregn daglige log-afkast i kolonne C:
```
=LN(B3/B2)
```
hvor B er lukkekurser.
Estimér tidsvarierende volatilitet, σ_t
Den mest brugte filtrering er en EWMA:
```
σ_t² = λ · σ_t-1² + (1-λ) · r_t-1²
```
Typisk λ = 0,94 (daglig). I Excel kan du
- gemme λ i $B$1
- starte i række 3 med et frø (standardafvigelsen af de første 30 dage)
- bruge denne formel i F4 og nedad:
```
=SQRT($B$1*F3^2 + (1-$B$1)*C3^2)
```
- Med moderne Excel kan du lave det mere læsbart med LET:
```
=LET(λ,$B$1,σ₋,F3,r,C3, SQRT(λ*σ₋^2 + (1-λ)*r^2))
```
- …eller helt kapsle det i et LAMBDA kaldet EWMAvol og bruge
```
=SCAN(INIT_VOL,C3:C1000,EWMAvol)
```
Resultatet er en kolonne med daglige σ_t.
Standardisér afkast
I kolonne G:
```
=C3/F3
```
Nu burde variansen af G være tæt på 1 og volatilitetsskyl stort set væk.
Udtræk percentil på de filtrerede afkast
Antag et 99 % konfidensniveau (α = 1 %). I en celle:
```
α = 0,01 (fx i $B$2)q_α = PERCENTILE.EXC(G:G,$B$2)
```
Bemærk at q_α vil være et negativt tal (et tab).
Genskaler med den aktuelle (seneste) volatilitet
Hvis den seneste σ_t står i F1002, bliver den én-dags VaR:
```
VaR = - F1002 * q_α
```
Resultatet er et positivt beløb, der angiver det forventede maksimale tab med 99 % sandsynlighed i næste handelsdag.

Gennemsigtighed med let/lambda

Selv lange formler kan holde sig læsbare:

=LET( λ, $B$1, r, DROP(C:C,1), // Afkast uden første NA σ, SCAN(STDEV(DROP(C:C,-30)), r,  LAMBDA(σ₋,x, SQRT(λ*σ₋^2+(1-λ)*x^2))), z, r/σ, q, PERCENTILE.EXC(z,$B$2), CURRENT_σ, TAKE(σ,1),-CURRENT_σ*q )

Kopier én gang – resten opdateres dynamisk.

Fordele og ulemper ved filtreringen

Fordele
- Tilpasser sig regimeskift – VaR stiger hurtigt i volatile perioder.
- Tager højde for volatilitetsskyl, så ældre ekstremer i rolige tider får mindre vægt.
- Bedre sammenlignelighed på tværs af markeder med forskellige basissværdier.
Ulemper
- Valget af λ er subjektivt; for højt λ → langsom reaktion, for lavt λ → støj.
- I chokerende markedsbevægelser kan modellen undervurdere risikoen lige i starten (σ bygger gradvist op).
- Kræver rekursive formler – store modeller kan blive tunge i klassisk Excel (løsning: dynamiske arrays eller Power Query/Power Pivot).

Med filtreret historisk VaR får du en model, der kombinerer styrken fra historiske tabsscenarier med en real-time volatilitetstilpasning – et nyttigt kompromis mellem simpel historisk og fuld GARCH-forecasting, som desuden kan implementeres med ren vanilla-Excel.

Metode 5: Bootstrappet historisk VaR (resampling/blokbootstrap)

Bootstrapping giver dig mulighed for at generere mange alternative historiske scenarier ud fra et begrænset datasæt og dermed få en mere stabil estimering af VaR, især når du kun har få hundrede observationer at arbejde med.

1. Opsæt basisdata

Placér de historiske daglige portefølje-afkast (ΔP&L) i kolonne B fra række 2 og ned (B2:Bn). Eksempelvis kan B1 hedde Historisk P&L.
Antal observationer (n) udregnes i E2:
```
=COUNTA(B2:B10000)
```

2. Vælg bootstrap-parametre

`E4`	Antal simulationer (N)	`50 000` (typisk 10-100k)
`E5`	Seed (valgfri)	`12345`
`E6`	Bloklængde (k)	`1` = iid; sæt f.eks. 5 for en 5-dages horisont
`E7`	Konfidensniveau (α)	`0,01` (1 % VaR)

Har du Office 365 kan du fastlåse frøen (seed) til den indbyggede tilfældighedsgenerator via RANDARRAY([rows],[cols],[min],[max],[whole_number]). Den er dog ikke deterministisk fra cold-start, så brug i stedet SEQUENCE som “seed” til et pseudo-tilfældigt shuffle, hvis reproducerbarhed er strengt påkrævet.

3. Generér bootstrap-indekser

I celle G2 genereres et array af tilfældige rækker (med blokke):

=LET( n, E2, N, E4, k, E6, idx, RANDBETWEEN(1, n-k+1), sample, SEQUENCE(N, k, idx, 0), MOD(sample-1, n)+1)

Når k = 1 svarer det til klassisk (iid) bootstrap.
Ved k > 1 holdes autokorrelation og evt. volatilitetsklynger bedre intakte, fordi blokke af sammenhængende dage trækkes.

4. Map indekser til p&l-værdier

I H2 (lad cellen spilde ud til højre/ned):

=INDEX($B$2:$B$10000, G2#)

Resultatet er et 2-d array på N × k. Hvis k > 1 summeres blokken for at få den samlede P&L over horisonten:

=BYROW(H2#, LAMBDA(row, SUM(row))) ' giver et 1-d array med N scenarier

5. Udtræk var-percentilen

=-PERCENTILE.INC( BYROW(H2#, LAMBDA(r, SUM(r))) , E7)

Bemærk: Minustegnet gør værdien positiv, så VaR udtrykkes som tab.

6. Performance-tips

Store N i Excel kan gøre regnearket tungt. Overvej:
- Beregn kun ved behov (slå automatisk beregning fra).
- Udnyt LET/LAMBDA for at minimere dobbeltberegninger.
- Gem input-data i en separat fil og brug Power Query til at læse ind once.
Alternativt kan du køre bootstrappen i VBA (loop) og blot returnere de færdige VaR-tal til arket.

7. Fortolkning & faldgruber

Bootstrap antager, at de historiske afkast er repræsentative for fremtiden – du gentager dem blot i nye kombinationer.
Små samples kan stadig give misvisende VaR, især hvis ekstreme hændelser slet ikke findes i datasættet. Overvej derfor at kombinere med stress-scenarier.
Bloklængde bør matche den risikohorisont, du rapporterer VaR på. For 10-dages regulatory VaR vælg k = 10 (eller overlappende 10-dages blokke).
VaR-skøn bliver mere støjfyldt, jo lavere konfidensniveau (f.eks. 0,1 %) du vælger. Øg N eller brug importance resampling for at fastholde præcision.

Når alt er sat op, har du en fleksibel, ikke-parametrisk metode til at estimere risiko, som automatisk tager højde for ikke-lineære P&L-effekter og bevarer den historiske afhængighed (ved blokke). Justér blot parametrene i cellerne E4:E7 for hurtigt at se, hvordan antagelserne påvirker din VaR.