USD Finans

Magasinet der gør dig klogere på penge

Vidensbank

Beregn porteføljens beta i forhold til OMXC25 i Excel

0
Beregn porteføljens beta i forhold til OMXC25 i Excel

Hvor risikofyldt er din portefølje egentlig? Ét tal – beta – kan fortælle dig, om dine investeringer typisk bevæger sig som en flok får efter det danske eliteindeks OMXC25, eller om de har deres helt egen vilde dans. Alligevel kan det virke som et Excel-mareridt at nå fra rå kursdata til et pålideligt beta-estimat.

I denne artikel guider USD Finans dig trin for trin gennem processen – fra første download af historiske priser til den endelige regressionsanalyse. Undervejs deler vi praktiske tips til datakilder, formel-tricks og de klassiske faldgruber, der kan omdanne selv den bedste aktieanalyse til talmagi.

Uanset om du er nysgerrig private investor, porteføljeforvalter under tidspres eller Excel-nørd på jagt efter næste makroprojekt, får du her en hands-on opskrift på at:

  • hente og kvalitetstjekke total return-data for både portefølje og OMXC25,
  • strukturere regnearket, så du slipper for fejlslagne formler,
  • beregne afkastserier, der kan klare både helligdage og udbytter,
  • køre regressionen, der afslører din beta – komplet med alpha, R² og grafisk reality-check.

Grib en kop kaffe, åbn et frisk Excel-ark, og læs videre – på få minutter ved du præcis, hvordan du beregner porteføljens beta i forhold til OMXC25 og får tallene til at arbejde for dig.

Dataforberedelse: Portefølje- og indeksdata

Grundlaget for enhver pålidelig betaberegning er rene og konsistente dataserier. Før vi går i gang med Excel-regressionen, skal både portefølje- og OMXC25-data indsamles, harmoniseres og testes. Følg trinene nedenfor.

1. Hvor finder du historiske data?

Kilde Dækning Fordele Ulemper
Nasdaq Copenhagen Officielle indeks og aktiekurser Høj datakvalitet, total-return varianter af OMXC25 Ofte abonnements­krævende ved stor download
Databrokere (Bloomberg, Refinitiv, FactSet, Infront m.fl.) Bred global dækning inkl. corporate actions Automatiseret eksport, minutiøs justering for udbytter/splits Dyr licens; kræver API-opsætning
Yahoo Finance / Stooq Gratis EOD-filer Nemt CSV-download, tilstrækkeligt til hobbybrug Mangler ofte total return-indeks; upålidelige ferie­kalendere

2. Valg af frekvens

  1. Daglig: Mest detaljeret og standard til porteføljebeta. Vær opmærksom på støj og mange ikke-halvårige udbyttejusteringer.
  2. Ugentlig: Glatter støj, reducerer datamængde. Vælg typisk fredagsluk for at undgå weekend-gap.
  3. Månedlig: God til lange horisonter (5-10 år) og til at mindske auto­korrelation. Brug ultimo-dato.

Hold samme frekvens for portefølje og indeks, ellers ødelægges betaen.

3. Periodevalg

  • Minimum 2-3 år daglige data (≈ 500-750 observationer) giver statistisk styrke.
  • Sørg for, at perioden dækker hele den tid, porteføljen har eksisteret i sin nuværende form. Ellers introduceres survivorship-bias.
  • For stresstest: medtag finanskriser eller coronafald for at se betaens følsomhed i ekstreme markeder.

4. Brug altid total return-indeks

Beta bør måle samlet afkast, ikke kun kursgevinster. Til OMXC25 skal du derfor hente OMXC25GI (Gross Index) eller OMXC25TR, som geninvesterer udbytter. Hvis du bruger prisindekset (OMXC25), undervurderer du markedets reelle afkast og forvrænger beta.

5. Håndtering af udbytter i egen portefølje

  • Har du porteføljeværdien fra dit depot (inkl. kontantbeholdning), er udbytter allerede inde.
  • Har du kun aktiekurser, skal du selv justere for udbytter eller anvende adjusted close fra datakilden.
  • Excel-tip: læg udbytter til kontantkontoen, og brug SUM over alle positioner for hver dato.

6. Valuta og tidszoner

Beta kræver, at portefølje og indeks noteres i samme valuta og samme tidszone/markedsluk.

  • Har du USD-aktier i porteføljen, konverter daglige værdier til DKK med Danmarks Nationalbanks spotkurs (kl. 16:00 CET) eller Bloomberg FXC.
  • Skrab aldrig amerikanske EOD-priser (kl. 22:00 CET) sammen med danske (kl. 17:00 CET) uden at forskyde datoer. Brug OFFSET i Excel eller Power Query til justering.

7. Eksport til csv

  1. Gem rådata som .csv før import til Excel-ark. Undgå formatering (komma-tusindtalsseparatorer) – brug punktum som decimalseparator, så Excel læser tallene korrekt.
  2. Navngiv logisk: Portfolio_EOD.csv, OMXC25TR_EOD.csv.
  3. Indlæs via Data > Hent data > Fra tekst/CSV, og lad Power Query fastlåse datatype = Dato/time og Decimal.

8. Datakvalitetstjek

  • Dubletter: Brug Data > Fjern dubletter på datokolonnen.
  • Huller: Indsæt hjælpesøjle med =IF(ISBLANK(B2);"Mangler";"OK"). Kontroller specielt handelsfrie helligdage – disse bør fjernes i begge serier.
  • Ekstreme outliers: Anvend =ABS(Z.TEST(...)) eller diagrammer. Markér værdi > 8 % afkast som manuelt checkpunkt (typisk splitfejl).
  • Stemme-/aktiesplit: Hvis du ser −50 % eller +100 % afkast en enkelt dag, verificér korrektionen i kildefilen.
  • Formeltest: Slå stikprøvevis afkast op i originalkilden for at sikre, at adjusted close passer.

Når ovenstående tjek er bestået, er dine dataserier klar til næste skridt: at blive indlæst i et struktureret Excel-ark, hvor vi kan begynde at beregne afkast og i sidste ende porteføljens beta.

Opsætning i Excel: Struktur og rensning

Før du kaster dig over selve beta-beregningen, bør du investere et øjeblik i at give dit regneark en solid og ren struktur. Det sparer tid, reducerer risikoen for formelfejl og gør det nemt at opdatere data fremover.

1. Anbefalet arkstruktur

Kolonne Indhold Eksempel Kommentar
A: Dato Handelsdage i kronologisk rækkefølge 03-01-2022 Formatér som dd-mm-yyyy for entydighed
B: Porteføljeværdi Lukketidsværdi eller pris pr. andel 1 042 315 Brug helst total return-værdier inkl. geninvesterede udbytter
C: OMXC25 Indeksniveau samme dato 1 893,47 Sørg for at hente TR-versionen (total return)

Placér gerne regnearket i en fane kaldet RåData og lav beregninger i en separat fane Beregninger. Det adskiller inputs og formler og gør debugging lettere.

2. Datolinje-up: Sådan sikrer du synkronisering

  1. Sortér begge datasæt efter dato (Data > Sortér A→Å).
  2. Flet dato-kolonnerne:
    • Kopier portefølje-datoer til kolonne A.
    • Indsæt indeks-datoer i kolonne D.
    • Brug UNIKE() eller REMOVE DUPLICATES (Excel 365/2021) for at få én samlet datokolonne uden dubletter.
    • Kopier den rensede liste tilbage til kolonne A.
  3. VLOOKUP/XLOOKUP for at hente værdier: 
    =XLOOKUP(A2;PortoDatoKolonne;PortoVærdiKolonne;"")

    Gør tilsvarende for OMXC25. Tom streng ("") returneres hvis datoen mangler i kildetabellen.

3. Fjern helligdage og ikke-handelsdage

Danske handelsdage følger Nasdaq Copenhagen-kalenderen.

  • Filtrér på Porteføljeværdi = "" og OMXC25 = "" – rækker hvor begge felter er blanke kan slettes direkte.
  • Hvis kun den ene serie mangler værdier (fx pga. fordelingsdato for udbytte), kan du enten
    • Interpolere (=AVERAGE(oven;under)), eller
    • Slette datoen helt, hvis periodens længde stadig er tilstrækkelig.

4. Håndtér manglende og fejlbehæftede værdier

  1. Indsæt datavalidering:
    • Markér kolonnerne B og C → Data > Data Validation → Tillad kun tal > 0.
  2. Find outliers: Brug betinget formatering til at markere ændringer > ±10 %. Ekstreme spring kan skyldes fejlagtig import (komma vs. punktum) eller corporate actions.
  3. Brug IFERROR() i dine opslag: 
    =IFERROR(XLOOKUP(...);NA())

    så #N/A fremstår synligt, men uden at bryde øvrige formler.

5. Ens valuta og tidszoner

Selvom OMXC25 noteres i danske kroner, kan din portefølje indeholde aktiver i andre valutaer. Inden du beregner afkast:

  • Konverter porteføljens værdi til DKK pr. \*samme lukketidskurs\* som indeksdataene.
  • Sørg for, at både portefølje- og indeksdata repræsenterer lukkepriser for samme kalenderdag – ikke kl. 22:00 mod kl. 17:00.

6. Sidste datakvalitetstjek før afkastberegning

  1. Tæl antal observationer (=COUNTA(A:A)-1) – er tallet ens i både kolonne B og C?
  2. Scan for tomme celler: F5 > Gå til speciel > Blanks.
  3. Filtrér på værdier <= 0 (typisk fejl eller tekniske nul-punkter efter spin-offs).
  4. Gem en kopi af det rensede ark som CSV-backup, så du kan rulle tilbage uden at hente data på ny.

Når disse skridt er gennemført, har du et velforberedt og konsistent datasæt, der danner fundamentet for de næste sektioner: afkastberegning, regression og fortolkning af beta.

Beregn afkastserier i Excel

Før du kan regressere porteføljens afkast mod OMXC25, skal de daglige (eller ugentlige/månedlige) afkast først beregnes. Nedenfor finder du en praktisk step-by-step-guide, der både dækker simple og log-afkast samt de vigtigste kvalitetstjek.

1. Indsæt nye kolonner til afkast

  1. Antag, at du i kolonne A har Dato, i B har porteføljens Værdi/Pris, og i C har OMXC25-niveauet.
  2. Opret to nye kolonner:
    • D: Port_Ret
    • E: OMX_Ret

2. Vælg afkastdefinition

Type Formel i række 3* Fordele
Simpelt afkast =(B3/B2)-1
=(C3/C2)-1		 Intuitivt, matcher IRR, bruges i de fleste rapporter
Log-afkast =LN(B3/B2)
=LN(C3/C2)		 Additive over tid, gør statistiske tests lettere

*Række 2 indeholder den første prisobservation.

3. Håndter første observation

  • I den første afkastrække (typisk række 2) mangler du en foregående pris. Sæt derfor cellerne D2 og E2 til NA() eller lad dem stå tomme.
  • Hvis du vil undgå #DIV/0!, omslut formlen med IFERROR() eller brug:
    =IF(B2="";NA();(B3/B2)-1)

4. Kopiér formlen ned & formatter

  1. Træk formlen ned til sidste datasætning.
  2. Formatér kolonnerne med Procent eller Tal (4 dec.) for nem aflæsning.

5. Konsistens er kritisk!

Vælg én afkastmetode og brug den konsekvent for både portefølje og indeks – ellers bliver beta skæv. Beta-estimatet er robust over for skala, men inkonsistente definitioner kan skævvride alpha og fejlleddet.

6. Kvalitets- og outliertjek

  • Ekstreme værdier:
    • Markér kolonnerne og brug Betinget formatering > Regler for top/bund > Større end… til fx ±10 %.
    • La’ vær med blindt at slette: Undersøg om outlier skyldes udbytte, korrektion eller datasvips.
  • Resume-statistik: Indsæt f.eks. =AVERAGE(D:D), =STDEV.S(D:D), =MAX(D:D), =MIN(D:D) nederst for et hurtigt sanity-check.
  • Histogram: Brug Indsæt > Diagram > Histogram for at se fordelingen – flat tails indikerer outliers.
  • Tjek for tomme celler: =COUNTBLANK(D:D) – bør være 0 (minus første række).

7. Stabilitetskontrol (valgfrit)

Beregn et rullende gennemsnit af afkastet, fx 30-dages, for at se om volatiliteten varierer dramatisk:

=AVERAGE(D3:INDEX(D:D;ROW()-1)) trukket ned giver en simpel glidende mean.

Når afkastserierne ser sunde ud, er du klar til næste skridt: at estimere porteføljens beta via regression eller kovarians/varians-metoden.

Beta i Excel: Regression og alternativ kovarians/varians

Når dine afkastserier for porteføljen (Port_afk) og OMXC25 (Mkt_afk) ligger side om side i Excel, kan selve betaberegningen udføres på få minutter. Nedenfor gennemgår vi tre metoder – SLOPE, LINEST samt varians-/kovarians-tilgangen – og slutter af med en hurtig guide til et scatterplot med trendlinje.

1) beta med slope

  1. Anbring porteføljens afkast i kolonne B (fx B2:B253) og OMXC25-afkast i kolonne C (C2:C253). Sørg for, at rækkerne matcher dato for dato.
  2. I en tom celle skriv:
    =SLOPE(B2:B253; C2:C253)
  3. Tryk Enter. Resultatet er beta, dvs. hældningen på den lineære sammenhæng mellem porteføljens og markedets afkast.

Bemærk: Funktionen bruger (Y; X)-rækkefølgen, så porteføljeafkastet skal stå først.

2) alpha, beta, r² og standardfejl med linest

  1. Marker et område på 2 x 5 celler (fx E2:I3), da LINEST kan returnere flere output.
  2. Skriv formlen som matrixformel (i 365/2021+ skal du blot afslutte med Enter; i ældre versioner brug Ctrl+Shift+Enter):
    =LINEST(B2:B253; C2:C253; TRUE; TRUE)
  3. Excel udfylder området med:

    Kolonne 1 Kolonne 2 Kolonne 3 Kolonne 4 Kolonne 5
    Beta Alpha SE Beta SE Alpha R²

Med LINEST får du altså ikke blot beta, men også regressionskonstanten (alpha), determinanteringskoefficient (R²) og standardfejl, som giver et hurtigt kvalitetstjek af modellen.

3) alternativ: Kovarians / varians

Denne metode er nyttig, hvis du vil dobbelttjekke SLOPE-resultatet eller arbejde videre i VBA/Power Query:

  1. Beregn kovarians mellem portefølje og marked:
    =COVARIANCE.S(B2:B253; C2:C253)
  2. Beregn variansen af markedet:
    =VAR.S(C2:C253)
  3. Beta fås som:
    = [kovarians] / [varians]

Teoretisk er SLOPE og COVARIANCE.S / VAR.S identiske, hvis dataserierne er ens.

4) visuel kontrol med scatterplot og trendlinje

  1. Marker begge afkastkolonner inkl. overskrifter.
  2. Vælg Indsæt > Punkt (X,Y).
  3. Højreklik på datapunkterne > Tilføj trendlinje.
  4. Sæt hak i Vis ligning på diagram og Vis R²-værdi.
    Excel viser nu ligningen y = βx + α samt R², som bør matche dine LINEST-tal.

Tjekliste

  • Beta afhænger af afkastfrekvens og periodevalg – dokumentér altid begge dele.
  • Vær opmærksom på ekstreme observationer; overvej winsorisering eller robust regression, hvis outliers dominerer.
  • Hvis din portefølje indeholder aktier uden for Danmark, anvend et bredere marked (fx MSCI World) eller beregn separat beta pr. region.

Fortolkning, kvalitetstjek og videre analyse

Når tallene først er ude på regnearket, starter det egentlige analysearbejde. Herunder finder du en praktisk guide til at få mest mulig indsigt ud af din beta-beregning – og til at undgå de klassiske faldgruber.

1. Sådan læser du beta-, alpha- og r²-tallene

Metrik Fortolkning Husk
Beta (β)
  • β = 1: Porteføljen bevæger sig i gennemsnit som OMXC25.
  • β > 1: Porteføljen er mere volatil end markedet (højere systematisk risiko).
  • β < 1: Porteføljen er mere defensiv.
  • Negativ β: Porteføljen bevæger sig modsat markedet – kan fungere som hedge.
 Beta er kun meningsfuld i forhold til det valgte marked og tidsperiode.
Alpha (α) Skæringspunktet fra LINEST. Positiv alpha indikerer merafkast givet porteføljens beta-risiko. Test om α er signifikant forskellig fra 0 (p-værdi < 0,05) før du drager konklusioner.
R² Forklarer hvor stor en del af porteføljens afkastvariation der kan tilskrives markedet. Lav R² kan betyde, at andre faktorer (fx small cap- eller value-præmier) driver afkastet.

2. Frekvens og periodevalg

  • Daglig, ugentlig eller månedlig? Jo højere frekvens, desto flere observationer – men også mere støj. Professionelle risikostyrere tester ofte både daglige og ugentlige data.
  • Periodelængde: 3-5 år er udbredt i akademiske studier, men kortere vinduer (12-24 mdr.) fanger ny adfærd hurtigere.
  • Skift frekvens/periode som en følsomhedsanalyse og se, om beta holder sig stabil.

3. Robusthedstjek

  1. Outliers
    Visuel inspektion: Brug scatterplottet i Excel. Punkter langt fra trendlinjen kan forvrænge beta.
    Metoder: Winsoriser de 1-2 % mest ekstreme observationer, eller kør regressionsdiagnostik (studentiserede residualer).
  2. Ikke-stationaritet
    Markedet ændrer regime (fx finanskrise, COVID-19). Splits datasættet i underperioder eller anvend rullende beta (se nedenfor).
  3. Heteroskedasticitet
    Uens varians i residualerne kan underdrive standardfejl. Brug =LINEST(..., TRUE, TRUE) i nyere Excel-versioner for robuste standardfejl, eller kopier data til et statsprogram (R, Python, EViews) og brug White-korrektion.

4. Rullende beta-beregninger

For at se, om porteføljens markedsfølsomhed ændrer sig over tid, kan du beregne beta i et glidende vindue:

  1. Vælg vindueslængde (fx 60 ugentlige observationer ~ 15 måneder).
  2. Brug =SLOPE(OFFSET(...)) med dynamiske områder eller Data Analysis → Regression i et makro.
  3. Plot den rullende beta som tidsserie. Store udsving kan indikere skift i strategi eller porteføljesammensætning.

5. Klassiske faldgruber

  • Survivorship bias: Tager du kun nuværende porteføljeholdinger med, overser du faldne engle. Gem historiske vægte, ellers bliver beta kunstigt lav.
  • Forkert benchmark: OMXC25 TR er standard for danske large caps, men en global portefølje bør måles mod MSCI ACWI eller lignende. Beta er kun meningsfuld relativt til det valgte indeks.
  • Valuta: Hvis porteføljen er i USD, men benchmark i DKK, kan valutakursudsving maskere det reelle forhold. Omregn alt til samme base-valuta før regressionen.

6. Næste skridt & automatisering

  1. Automatisér datatræk: Power Query kan hente CSV/JSON fra Nasdaq eller Stooq og opdatere med ét klik.
  2. Indbyg skabeloner: Lås formler for afkast, SLOPE og LINEST i et dedikeret ark. Brug datavalideringslister til at skifte vindueslængde.
  3. Alert-system: Kombinér betaberegningen med betinget formatering: Hvis rullende beta stiger > 1,3 eller falder < 0,7, markér cellen rødt eller send en Outlook-e-mail via VBA.
  4. Udvid modellen: Tilføj faktorer som SMB (size) og HML (value) i et multifaktor-setup. LINEST kan håndtere flere forklarende varianter direkte i Excel.
  5. Dokumentér versioner: Notér datakilder, koder, filtre og periodelængder i et metadata-ark for reproducibilitet.

Med disse trin har du ikke blot en statisk betaværdi, men en dynamisk risikoradar, der kan give tidlige signaler om porteføljens eksponering – og lægge fundamentet til endnu mere avancerede analyser.

Website:

Related Story

Indholdsfortegnelse

→ Indhold