Hvordan ved du egentlig, om to aktier bevæger sig i takt – eller i hver sin retning? Svaret ligger gemt i to statistiske størrelser, der ofte bliver overset af private investorer: korrelation og kovarians. Forståelsen af dem kan betyde forskellen på en portefølje, der modstår stormvejr – og en, der kuldsejler, når markederne vender.
På FCE Invest arbejder vi hver dag med at gøre komplekse investeringsværktøjer tilgængelige for alle. I denne guide viser vi dig, trin for trin, hvordan du beregner korrelation og kovarians direkte i Excel – uden at drukne i formler eller dyre softwares.
Uanset om du vil teste, hvor meget dine favoritaktier bevæger sig sammen, eller du vil bygge en mere robust portefølje, giver dagens artikel dig både den teoretiske baggrund og de praktiske tastekombinationer. Vi tager dig fra ”hvorfor det er vigtigt” til ”sådan gør du” – og slutter med, hvordan du bruger resultaterne til smartere beslutninger i markedet.
Er du klar til at skrue ned for gætteriet og op for de datadrevne indsigter? Så læs med – næste afsnit hjælper dig med at forstå præcis, hvorfor korrelation og kovarians er uundværlige værktøjer i din investeringsværktøjskasse.
Hvorfor korrelation og kovarians er vigtige i investering
En af de første lektioner i porteføljeteori er, at det ikke kun handler om afkastet på de enkelte aktiver, men om hvordan de bevæger sig sammen. Den fælles bevægelse beskrives af to beslægtede – men ikke identiske – statistiske mål: korrelation og kovarians.
Hvad er korrelation?
- Definition: Et skaleret mål for styrke og retning af lineær sammenhæng mellem to tidsserier af afkast.
- Skala: Ligger mellem -1 og +1.
-1 = perfekt negativ samvariation, 0 = ingen lineær sammenhæng, +1 = perfekt positiv samvariation. - Enhedsløs: Korrelation er allerede “standardiseret”, så den er uafhængig af aktivers volatilitet.
- Hvornår bruger du den?
• Hurtig screening af, hvorvidt et nyt aktiv kan reducere din porteføljerisiko.
• Sammenligning af aktiver på tværs af markeder og valutaer uden først at standardisere skalaen.
Hvad er kovarians?
- Definition: Det ustandardiserede mål for, hvor meget to afkastserier bevæger sig op og ned sammen i absolutte tal.
- Enhed: Måles i produktet af de to afkastseriers enheder (f.eks. %²), og tallet kan antage enhver positiv eller negativ størrelse.
- Hvornår bruger du den?
• Direkte i formlen for porteføljevarians: σ2portefølje = w12σ12 + w22σ22 + 2w1w2Cov12.
• Ved optimering (f.eks. i Excel Solver eller VBA), hvor kovariansmatrixen indgår direkte.
Forskellen på de to i praksis
| Korrelation | Kovarians | |
|---|---|---|
| Skala | -1 til +1 | -∞ til +∞ |
| Enhed | Ingen | %² (eller hvad afkastet måles i) |
| Fortolkning | Styrke & retning | Absolut sammen-variation |
| Hovedbrug | Diversificeringsscore | Risikoberegning |
Sådan hænger det sammen i en investeringskontekst
- Diversificering: Lav (helst negativ) korrelation mellem aktiver reducerer porteføljens samlede udsving. Når du sorterer potentielle tilføjelser til porteføljen, er korrelation i første omgang den hurtigste indikator.
- Risikestyring: Når du skal udregne selve risikoen (variansen) for en portefølje, har du brug for kovariansen og, for mere end to aktiver, hele kovariansmatrixen.
- Porteføljeopbygning: I en klassisk Mean-Variance Optimization (Markowitz-model) indtaster du forventede afkast, vægte og kovariansmatrix for at finde den effektive rand. Men før du kommer så langt, kigger du typisk på korrelationsmatrixen for at udpege aktivklasser, der bør indgå.
Sammenfattende bør du altså bruge korrelation til at forstå samspillet mellem aktiverne i relative termer – og bruge kovarians, når tallene skal ind i den egentlige regnemaskine for risiko og porteføljeoptimering.
Klargør dine data i Excel
Inden du begynder at udregne korrelation eller kovarians, skal dine dataserier være rent og ensartet. Følg disse trin:
- Hent og saml priser
Download historiske prisdata for begge aktiver fra samme kilde (fx Yahoo Finance, Nasdaq eller din broker) for at minimere databrud. Kopiér dem ind i ét regneark i to kolonner – én for hver aktie/ETF/indeks. - Fastlæg én periodicitet
- Vælg daglige, ugentlige eller månedlige priser alt efter dit formål (kortsigtet handel vs. langsigtet portefølje).
- Har du daglige rådata, men ønsker månedlige? Brug
DATA > Gruppér efter(PivotTable) eller funktionenAGGREGATINGi Power Query til at trække sidste pris i måneden.
- Justér datoerne, så rækkerne flugter
- Sørg for, at begge aktiver har observation samme dato. Brug f.eks.
=XLOOKUP()eller=FILTER()til at matche datoer, eller saml serierne i Power Query og vælg “Inner Join”. - Marker weekend/helligdage, hvor kun den ene aktie handler, og fjern disse rækker eller erstat dem med
#N/A.
- Sørg for, at begge aktiver har observation samme dato. Brug f.eks.
- Beregn afkast i stedet for priser
Til korrelation/kovarians skal du have afkast, ikke priser:
Dato Pris A Pris B Afkast A Afkast B 02-01-2023 101 55 =B3/B2-1=C3/C2-103-01-2023 103 57 … … - Simpelt afkast:
=NyPris/GammelPris-1 - Log-afkast (foretrækkes til længere perioder og porteføljemodeller):
=LN(NyPris/GammelPris) - Lad første observations række for afkast stå tom – korrelations-formler ignorerer tomme celler.
- Simpelt afkast:
- Behandl manglende værdier
- Erstat sporadiske mangler med
#N/A, som Excel-funktionerne automatisk springer over. - Er der mange manglende priser (fx illikvid aktie), så overvej at forkorte perioden eller skifte datapunkt.
- Kontrollér med
=COUNTBLANK()eller=COUNTIF(range;"#N/A").
- Erstat sporadiske mangler med
- Sikr samme længde
Brug f.eks.=MIN(COUNTA(AfkastA);COUNTA(AfkastB))til at dobbelttjekke, at antallet af afkast-rækker matcher. Uoverensstemmelser skaber skæve resultater. - Konverter til Excel-tabel og navngiv områder
- Marker data > Indsæt > Tabel (genvej Ctrl + T). Tabeller udvider formler automatisk, når du føjer nye observationer til.
- Omdøb kolonnerne til fx
Afkast_AogAfkast_B. Excel foreslår derefter strukturerede henvisninger (=CORREL(Tabel1[Afkast_A];Tabel1[Afkast_B])). - Vil du hellere bruge navngivne områder: Markér afkastkolonnen > Skriv et navn i Navnefeltet (venstre for formellinjen) som
retA,retB.
Når ovenstående er gjort, har du to rensede og synkroniserede afkastserier, som er klar til at blive analyseret med =CORREL() og =COVARIANCE.P() – uden overraskelser fra manglende data eller forskudte datoer.
Beregninger i Excel: formler og værktøjer
Følgende gennemgang viser, hvordan du omsætter dine forberedte afkastserier til konkrete tal i Excel – først med selve funktionerne, dernæst med de indbyggede analyseværktøjer.
1. Beregn punkt-til-punkt med formler
- Korrelation – =CORREL()
Formel:=CORREL(område1;område2)
- område1 og område2 skal være lige lange kolonner eller rækker med numeriske afkast.
- Giver et tal mellem
-1og1, hvor0er ingen lineær sammenhæng.
- Kovarians – =COVARIANCE.P() vs. =COVARIANCE.S()
Funktion Anvendes når … Formel-eksempel =COVARIANCE.P()du ser hele populationen (fx alle månedlige afkast for et fastlagt tidsrum). =COVARIANCE.P(B3:B122;C3:C122)=COVARIANCE.S()du arbejder med et udsnit (sample) og vil bruge n-1 i nævneren. =COVARIANCE.S(B3:B122;C3:C122) - Typiske fejl & kvalitetstjek
#N/Aeller tomme celler i et af områderne får hele resultatet til at returnere#N/A. Rens data eller indpak funktionen iIFERROR().- Sørg for identisk længde: COUNT(B:B) bør matche COUNT(C:C). Ellers klip de overskydende rækker væk.
- Visuelt sanity-check: indsæt et punktdiagram (Indsæt → Punktdiagram) og se, om hældningen harmonerer med den beregnede korrelation.
2. Automatiske korrelations- og kovariansmatricer med analysis toolpak
- Aktivér værktøjet
Filer → Indstillinger → Tilføjelsesprogrammer → Gå… → afkryds “Analysis ToolPak”. - Opsæt data
Placer hver aktiv-serie i sin egen kolonne med en fælles overskriftsrækkefølge, fx:A B C 1 Dato Aktie_A Aktie_B 2 … 1,2% 0,8%
- Korrelationsmatrix
- Gå til Data → Dataanalyse → Korrelations.
- Angiv Inputområde = hele afkastblokken inkl. overskrifter.
- Marker “Etiketter i første række” så Excel bruger kolonnenavne.
- Vælg Outputområde (fx et tomt ark eller celle).
- Klik OK – Excel returnerer en symmetrisk matrix med 1’ere på diagonalen.
- Kovariansmatrix
- Samme menu, vælg Kovarians i stedet.
- Output-layoutet er identisk; tallene er nu i absolutte afkastsquared-enheder (fx %²).
- Tjek & dokumentér
- Formatter tallene til 2-4 decimaler for korrelation og evt. videnskabelig notation for kovarians.
- Sammenlign et par enkeltberegninger med matrixen for at sikre, at datointervallet stemmer.
- Gem arket som “CorrCovAudit” så du altid kan genskabe tallene.
3. Hurtige genveje og bedste praksis
- Lav dine afkastområder som Excel-tabeller (Ctrl+T) – så udvides formler og ToolPak-input automatisk, når du tilføjer nye rækker.
- Navngiv intervaller (Formler → Navnestyring) f.eks.
Ret_AogRet_B; skriv derefter=CORREL(Ret_A;Ret_B). - Gem en makro, der kører ToolPak-korrelationsanalysen, når du opdaterer data – sparer mange klik.
Når formlerne fungerer, og matricerne er på plads, er du klar til næste skridt: at bruge tallene i risiko-/afkast-modeller og simuleringer af din portefølje.
Fortolkning, visualisering og praktisk brug
Når Excel har spyttet en værdi ud, begynder det egentlige analysearbejde.
| Korrelation (ρ) | Praktisk fortolkning |
|---|---|
| -1 | Perfekt negativ sammenhæng – når Aktiv A stiger 1 %, falder Aktiv B 1 % (i samme skala). |
| -0,5 til 0 | Svag til moderat negativ sammenhæng. God diversificering uden at være “spejlvendt”. |
| 0 | Ingen lineær sammenhæng. Afkast bevæger sig uafhængigt – oftest det ideelle diversificeringsmål. |
| 0 til +0,5 | Svag til moderat positiv sammenhæng. Diversificeringseffekt aftager. |
| +1 | Perfekt positiv sammenhæng – aktiverne bevæger sig ens. Ingen risikoreduktion. |
Kovarians (σ12) er den absolutte sammen-variation og måles i produktet af afkast-enheder (f.eks. procent2). Derfor gælder:
- En positiv kovarians betyder, at afkastene tenderer til at bevæge sig i samme retning.
- En negativ kovarians betyder, at de bevæger sig i modsat retning.
- Størrelsen afhænger af volatiliteten i hvert aktiv; derfor er kovarians uegnet til at sammenligne styrke på tværs af aktivpar – brug korrelation til det.
Visualisér dine resultater
- Scatterplot med trendlinje
Marker dine to afkastkolonner → Indsæt → XY (Punkt). Højreklik på datapunkterne → Tilføj trendlinje (vælg lineær). Hældningen viser, hvordan ændringer i Aktiv A påvirker Aktiv B, mens datapunkternes spredning visualiserer styrken (tæt spredning = høj |ρ|). - Rullende korrelation
Brug en glidende formel, fx i celleC31med 30-dages vindue:
=CORREL(OFFSET(A2;ROW()-32;0;30;1); OFFSET(B2;ROW()-32;0;30;1))
Plot værdierne som linjediagram for at se, hvordan sammenhængen ændrer sig gennem tiden. - Korrelationsmatrix
Data → Data Analysis → Correlation og vælg hele afkast-tabellen. Excel returnerer en matrix, der er oplagt som heat-map (betinget formatering).
Undgå de klassiske faldgruber
- Outliers: Ekstreme observationer kan forvride både korrelation og kovarians. Overvej winsorisering eller log-afkast.
- Korte perioder: Mindre datasæt giver ustabile estimater. Tommelfingerregel: mindst 50-60 observationer for månedlige data.
- Ikke-stationaritet: Markedets struktur ændrer sig. Brug rullende analyser for at fange regime-skift.
- Log vs. simple afkast: Vælg én metode og vær konsistent, især hvis du kombinerer resultater i porteføljesammenhæng.
- Sammenligning på tværs af skalaer: Husk, at kovarians er skaleringsafhængig; normaliser med korrelation hvis du sammenligner forskellige aktivpar.
Fra tal til handling i porteføljen
Korrelation og kovarians er brikkerne i formlen for porteføljens varians:
σ2port = w12σ21 + w22σ22 + 2w1w2σ12
- Find den efficiente vægt ved at minimere σ2port i Excel Solver – korrelationen er den afgørende parameter for gevinsten ved diversificering.
- Brug en lav eller negativ korrelation til at sænke porteføljerisikoen, selv hvis de enkelte aktiver har høj volatilitet.
- Hvis du kun har korrelationen, kan kovariansen hurtigt udledes: σ12 = ρ · σ1 · σ2. Det omgår skaleringsproblemet.
- Opbyg stress-scenarier: Hæv korrelationen mod +1 under panikmarkeder for at se, hvor meget diversificeringen potentielt bryder sammen.
Ved konsekvent at tolke, visualisere og anvende korrelation og kovarians i dine Excel-modeller, får du et mere robust beslutningsgrundlag – og en portefølje, der er rustet til både stille og stormfulde markeder.