9 formler til porteføljeanalyse i Excel

Drømmer du om at forvandle et almindeligt Excel-ark til dit eget lille laboratorie for porteføljeanalyse – uden at investere i dyr software eller kæmpe databaser? Så er du landet det helt rigtige sted. Med blot ni gennemprøvede formler kan du på få minutter gå fra rå kursdata til skarpslebne indsigter om risiko, afkast og diversifikation.

Uanset om du er privat investor, formueforvalter eller blot nysgerrig på, hvordan professionelle måler performance, vil du opdage, at Excel rummer langt mere fire-power, end de fleste regnearksmodstandere vil indrømme. Fra det første expectede afkast til den afsluttende XIRR, får du i denne artikel en trin-for-trin-guide til at:

flette simple SUM-funktioner med lineær algebra,
bygge din egen kovarians- og korrelationsmatrix,
regne porteføljevarians og ‑volatilitet som en Wall Street-analytiker,
teste din porteføljes styrke via Sharpe-ratio, beta og pengestrømsjusteret afkast.

Lyder det teknisk? Bare rolig. Hver formel bliver forklaret i klart, jordnært sprog – med tips til absolutte cellereferencer, navngivne områder og de klassiske fælder, der får fejlmeddelelsen #NUM! til at poppe op. Resultatet er et gennemsigtigt Excel-setup, du kan tweake og skalere i det uendelige.

Klar til at give dine investeringer et power-boost? Lad os dykke ned i de ni formler, der kan gøre forskellen mellem gætværk og databaseret beslutningstagning.

Indholdsfortegnelse

Forventet porteføljeafkast med SUMPRODUCT

Det helt fundamentale mål i enhver porteføljesimulering er, hvad du forventer at tjene. I Excel kan du udregne det forventede porteføljeafkast som et vægtet gennemsnit af de enkelte aktivers forventede afkast – præcis det SUMPRODUCT er bygget til.

1. Opsæt dit dataskema

Aktiv	Vægt	Forventet afkast
Aktie A	30 %	8 %
Aktie B	50 %	6 %
Obligation C	20 %	3 %

Antag at tabellen ovenfor ligger i cellerne A2:C4, hvor vægtene står i B2:B4 og de forventede afkast i C2:C4.

2. Formlen

=SUMPRODUCT(B2:B4; C2:C4)

Resultatet returnerer 5,9 %, som beregnes sådan:

0,30 × 0,08 = 0,0240
0,50 × 0,06 = 0,0300
0,20 × 0,03 = 0,0060
Total = 0,059 (5,9 %)

3. Gode excel-vaner

Brug navngivne områder – fx =SUMPRODUCT(Vægte; ForventedeAfkast). Det reducerer fejl, når antallet af aktiver ændres.
Lås rækken – ikke kolonnen hvis du vil kopiere formlen sidelæns: =SUMPRODUCT($B2:$B4; $C2:$C4).
Tjek at vægtene summer til 100 %. En simpel kontrolformel er =SUM(B2:B4)=1. Brug betinget formatering til at farve cellen rød, hvis den ikke er SAND.

4. Årligt vs. Månedligt (én tidsenhed ad gangen!)

Afkast skal altid være opgjort i den samme tidsenhed som dine vægte og øvrige risikomål. Typiske faldgruber:

Du har månedlige afkast, men indsætter en årlig forventning – resultatet bliver meningsløst.
Du regner volatilitet på daglige afkast og anvender et årligt afkast i SUMPRODUCT – Sharpe-ratio’en vil blive forvrænget.

Enten:

Konverter alle input til årlig basis (f.eks. (1 + månedsafkast)¹² − 1), eller
Hold alt på månedsbasis og årliggør først helt til sidst.

Konsistens i tidsenheder er alfa og omega, når du skal sammenligne eller sammenflette flere nøgletal senere i analysen.

Porteføljens afkastserie pr. periode

Det første skridt i en seriøs porteføljeanalyse er at oversætte de enkelte aktivers afkast til én samlet tidsserie. Den serie – porteføljens afkast pr. periode – er fundamentet for al senere risiko‐ og performance‐måling.

1. Klargør data

En typisk opsætning ser således ud:

Dato	Asset A	Asset B	Asset C
31-01-2024	1,2 %	-0,4 %	2,1 %
29-02-2024	0,8 %	1,5 %	-0,2 %
31-03-2024	-0,6 %	0,9 %	1,7 %
Vægt	40 %	35 %	25 %

• Vægtene ligger i samme rækkefølge som aktivkolonnerne. • Sørg for, at vægtene summer til 100 % (eller 1,0, afhængigt af din formattering). • Hold tidsenheden konsekvent – månedlige afkast blandes ikke med daglige.

2. Beregn porteføljeafkast række for række

Med SUMPRODUCT – den mest direkte metode:
=SUMPRODUCT($B$5:$D$5; B6:D6)
Hvor $B$5:$D$5 er vægtrækken (låst med $) og B6:D6 er afkastene i første observationsrække.
Træk formlen ned langs alle datoer.
Med MMULT – hurtig matrixløsning:
=MMULT(B6:D8; TRANSPOSE($B$5:$D$5))
Markér først et lodret område med lige så mange rækker som afkastserien, indtast formlen og afslut med Ctrl + Shift + Enter (hvis du ikke har dynamiske arrays).
MMULT er praktisk, hvis afkastdata ligger i en Excel-tabel eller hvis du har mange aktiver.

Formlen returnerer en kolonne med porteføljens afkast, fx:

Dato	Portefølje afkast
31-01-2024	1,04 %
29-02-2024	0,73 %
31-03-2024	0,46 %

3. Håndter ændrede vægte (rebalancering)

Hvis porteføljen rebalanceres løbende, kan du tilføje en vægtrække for hver dato og referere til SUMPRODUCT(vægte_i_rækken; afkast_i_rækken). Alternativt kan du bruge OFFSET eller opbygge en udvidet MMULT-matrix med både afkast og vægte.

4. Hvad kan du nu bruge serien til?

Volatilitet: =STDEV.S(portafkast_range) (gang med √12 for at årliggøre månedlige data).
Sharpe-ratio: Brug gennemsnittet af serien minus den risikofri rente divideret med volatiliteten.
Kumulativt afkast: =PRODUCT(1+portafkast_range)-1.
Max drawdown, tracking error, Information Ratio – alle bygger på den samme porteføljeafkastserie.

Med andre ord: Når først porteføljens afkast pr. periode er på plads, åbner hele Excel-værktøjskassen sig for detaljeret risikoanalyse og performancemåling.

Kovariansmatrix med COVARIANCE.P/S

En kovariansmatrix (Σ) viser, hvordan hvert aktiv i porteføljen bevæger sig i forhold til alle de andre – inkl. sig selv. Den er derfor den uundværlige byggeklods, når du senere skal beregne porteføljevariansen via w’Σw.

1. Klargør afkast-data

Placér de historiske afkast (r_t,i) som en periode-rækker × aktiv-kolonner matrix
f.eks. B2:E121, hvor rækkerne B2:E2 er afkast for 1. måned og B121:E121 for sidste måned.
Navngiv området (f.eks. Afkast) eller lås det med absolutte referencer for at undgå fejl, når du kopierer formler.

2. Sample (covariance.s) eller population (covariance.p)?

COVARIANCE.S (sample) trækker én frihedsgrad fra (n – 1). Bruges oftest, fordi vi sjældent har hele “populationen” af fremtidige afkast.
COVARIANCE.P (population) dividerer med n. Vælg den, hvis dine data allerede dækker hele det relevante datasæt (f.eks. alle 12 måneder i et foruddefineret regnskabsår).

3. Opbyg matrixen

Læg en tom tabel et nyt sted i arket – én ekstra kolonne og én ekstra række til labels:

	Aktiv A	Aktiv B	Aktiv C
Aktiv A	=COVARIANCE.S(AfkA;AfkA)	=COVARIANCE.S(AfkA;AfkB)	=COVARIANCE.S(AfkA;AfkC)
Aktiv B		=COVARIANCE.S(AfkB;AfkB)	=COVARIANCE.S(AfkB;AfkC)
Aktiv C			=COVARIANCE.S(AfkC;AfkC)

Diagonal-elementerne (σ²_A, σ²_B …) er aktivernes varianser.
Off-diagonalerne er parvise kovarianser. De er symmetriske, så du behøver kun at beregne øverste (eller nederste) trekant og kopiere den modsat.

4. Hurtigere metode med dynamiske arrays

Har du Microsoft 365? Så kan du spare tid:

=LET( X; Afkast; n; ROWS(X); μ; AVERAGE(X); \ kolonne-gennemsnit C; (X - μ) / 1; \ centrer afkast (MMULT(TRANSPOSE(C); C)) / (n - 1) \ kovariansmatrix)

Formlen returnerer hele matrixen på én gang og udvider sig automatisk, hvis du tilføjer flere perioder eller aktiver.

5. Kontrol-tjek

Er diagonalen altid ≥ 0? (en negativ varians indikerer fejl).
Sammenlign et par manuelle kovarians-udregninger med resultatet fra matrixen for at sikre korrekt reference.

6. Hvorfor er σ vigtig?

Når kovariansmatrixen er navngivet (f.eks. Sigma), kan du øjeblikkeligt finde porteføljens samlede risiko:

=MMULT(TRANSPOSE(Vægte); MMULT(Sigma; Vægte)) ↦ σ²_portefølje

Den sidste formel – som vi gennemgår i næste afsnit – viser, at det netop er kovarianserne, der bestemmer hvor meget (eller hvor lidt) diversifikation reducerer den samlede risiko.

Korrelationsmatrix med CORREL

Korrelation (ρ) måler, hvor synkront to aktivers afkast bevæger sig. Værdien spænder fra -1 (perfekt modbevægelse) til +1 (perfekt sambevægelse). En korrelationsmatrix giver det fulde overblik over de parvise sammenhænge i porteføljen og er et nøgleinput til både varians- og VaR-beregninger.

Sådan bygger du matrixen i excel

Placér de historiske afkast for hvert aktiv i separate kolonner (samme tidsfrekvens og længde).
Eksempel: Kolonne B = OMXC25, C = S&P 500, D = Obligationer, E = Guld.
Opret en tom kvadratisk tabel, fx i området H4:K7, hvor rækker og kolonner navngives med de samme aktiv-tickere.
I den første celle under diagonalen (H5 i eksemplet) skriver du:
=CORREL($B$2:$B$121; $C$2:$C$121)
og trykker Enter. Brug altid absolutte kolonnereferencer ($B) så formlen kan kopieres.
Træk formlen hen over de øvrige celler for at udfylde hele den nederste trekant af matrixen. Kopiér derefter området og indsæt som spejlbillede over diagonalen, eller gentag CORREL for alle par, hvis du foretrækker unikke formler.
Indtast 1 på diagonalen (hver variabel har perfekt korrelation med sig selv).

Eksempeltabel

	OMXC25	S&P 500	Obligationer	Guld
OMXC25	1,00	0,74	-0,12	-0,05
S&P 500	0,74	1,00	-0,18	0,02
Obligationer	-0,12	-0,18	1,00	-0,25
Guld	-0,05	0,02	-0,25	1,00

Anvend Bet Conditional Formatting > Color Scales for øjeblikkeligt at spotte høje/lave værdier.

Sådan læser du tallene

Høj positiv korrelation (≈0,7-1,0) betyder, at aktiverne typisk stiger/falder samtidig – minimal risikoreduktion.
Nul korrelation (≈-0,2-0,2) indikerer uafhængige bevægelser og giver en vis diversifikation.
Negativ korrelation (<0) kan drastisk dæmpe porteføljesvingninger, fordi gevinster i ét aktiv udligner tab i et andet.

Hvorfor er korrelation vigtig?

Porteføljens varians beregnes som σ² = ΣΣ w_iw_jσ_iσ_jρ_ij. En lav ρ_ij sænker krydstermene og dermed den samlede risiko. Derfor kan to risikable aktiver danne en relativt stabil portefølje, så længe deres korrelation er lav.

Tips til videre analyse

Opdater matrixen løbende (f.eks. med dynamiske LET– eller LAMBDA-funktioner) for at reagere på regimeskift i markedet.
Lav et Heat Map for hurtigt at identificere diversifikationsmuligheder.
Brug Data Analysis > Correlation (ToolPak) til at generere matrixen på ét klik.
Kombinér matrixen med cluster-analyse for at gruppere aktiver med lignende korrelationsprofiler.
Husk at korrelationer er tidsvarierende; test forskellige vinduer (1 år, 3 år, 5 år) for robusthed.

Med en korrekt opdateret korrelationsmatrix kan du selektivt tilføje aktiver, der forbedrer Sharpe-ratioen uden at øge volatiliteten – selve essensen af moderne porteføljeteori.

Porteføljevarians via w’Σw

Porteføljevariansen (σ²_p) samler hele kovarians-universet i én nøgleværdi, der fortæller dig, hvor meget den samlede portefølje kan forventes at svinge omkring sit middelafkast. Excel-udtrykket er den klassiske matrixsætning

σ²_p = wʼ Σ w

hvor w er vægtvektoren (kolonne) og Σ er kovariansmatrixen.

1. Opsæt området én gang for alle

Placér vægtene i et lodret område, fx B3:B5.
Læg din symmetriske kovariansmatrix lige ved siden af, fx C3:E5.
Tildel navne:
- w → =Sheet1!$B$3:$B$5
- Cov → =Sheet1!$C$3:$E$5
Det gør formlerne meget lettere at læse (w, Cov i stedet for lange absolute cellereferencer).

2. Varians med mmult

Med Excel 365 (dynamiske arrays) skrives blot én formel, og resultatet spildes automatisk:

=MMULT(TRANSPOSE(w); MMULT(Cov; w))

Ældre Excel-versioner kræver CSE-indtastning (Ctrl + Shift + Enter).

3. Alternativet: Sumproduct

Du kan også folde hele matrixmultiplikationen ud i én linje:

=SUMPRODUCT(w; MMULT(Cov; w))

Fordel: SUMPRODUCT skal ikke bekræftes som matrixformel.

4. Eksempel med tal

	A	B	C
w	40 %	35 %	25 %
σ²	0,0225	0,0300	0,0150
Cov_AB	0,0180

I Excel ligger tallene som vist under punkt 1; den indtastede formel returnerer fx 0,0209, hvilket er porteføljevariansen. Træk kvadratroden (=SQRT(resultat)) for at få volatiliteten.

5. Praktiske tips

Absolute referencer ($) sikrer, at formlen ikke ødelægges, når du kopierer arkets layout.
Navngivning (w, Cov) øger læsbarheden og gør makroer/LAMBDA-funktioner lettere.
I dynamisk Excel kan du indkapsle alt i én LET(), fx:
=LET(w;B3:B5; Σ;C3:E5; MMULT(TRANSPOSE(w); MMULT(Σ; w)))
Kontrollér altid, at kovariansmatrixen er symmetrisk, og at vægtene summer til 1 (=SUM(w)).

Når variansen er på plads, har du fundamentet til at beregne både volatilitet, Sharpe-ratio og porteføljens Value-at-Risk.

Porteføljens volatilitet (standardafvigelse)

Standardafvigelsen – i investeringsjargon kaldet volatiliteten – er det mest brugte mål for, hvor meget en porteføljes afkast typisk svinger omkring sit gennemsnit. I Excel kan du beregne den på to måder:

Via variansen: Har du allerede beregnet porteføljevariansen (fx i celle E10), tages blot kvadratroden:
=SQRT(E10)
Direkte på afkastserien: Ligger dine porteføljeafkast i kolonne G fra række 2-61:
=STDEV.S(G2:G61)
(Brug STDEV.P, hvis afkastserien repræsenterer hele “populationen” og ikke kun et udsnit.)

Sample eller population?

Excel skelner mellem “sample” (STDEV.S, COVARIANCE.S) og “population” (.P). I de fleste porteføljeanalyser arbejder vi med historiske data som stikprøve på fremtiden, og derfor anvendes S-varianten, der dividerer med n-1.

Årliggørelse af volatilitet

Datainterval	Faktor	Formel
Daglig (≈252 handelsdage)	`SQRT(252)`	`=STDEV.S(G2:G761)*SQRT(252)`
Ugentlig	`SQRT(52)`	`=STDEV.S(G2:G105)*SQRT(52)`
Månedlig	`SQRT(12)`	`=STDEV.S(G2:G61)*SQRT(12)`

Formelt kommer faktoren fra, at varians skalerer lineært med tiden, mens standardafvigelsen (kvadratroden af variansen) skalerer med kvadratroden af tiden (σ_T = σ₁√T). Sørg for, at dine afkast (numerator) og din risikofri rente, Sharpe-ratio mv. er årliggjort på samme måde for at undgå “æbler og pærer”.

Praktiske tips

Navngiv porteføljeafkastområdet (fx Port_Returns) – det gør formlen =STDEV.S(Port_Returns)*SQRT(12) nemmere at læse.
Lock dato- og afkastområder med absolutte referencer ($G$2:$G$61), hvis du vil kopiere formlen til flere scenarier.
Tjek for tomme celler eller fejl i serien; STDEV ignorerer tekst, men ikke #N/A-værdier.

Når volatiliteten er på plads, kan du gå videre til Sharpe-ratioen og andre risikojusterede nøgletal – men husk altid, at historisk standardafvigelse kun er et estimat for fremtidig risiko.

Sharpe-ratio: risikojusteret afkast

Sharpe-ratioen sætter porteføljens merafkast i forhold til den risiko (volatilitet), du har løbet. I Excel kan den beregnes på få linjer, men tidsenheden skal være konsistent hele vejen igennem.

1. Forbered dine serier

En kolonne med porteføljens afkast pr. periode – f.eks. C2:C61 for 60 måneders afkast.
En tilsvarende kolonne med risikofri rente pr. periode – f.eks. 1-måneders statsobligation (D2:D61).

2. Fratræk den risikofri rente, række for række

I en ny kolonne (E) skriver du i E2:

=C2 - D2

Kopier for hele serien (E2:E61). Kolonnen indeholder nu porteføljens overskudsafkast (excess return).

3. Gennemsnitligt merafkast og volatilitet

Størrelse	Formel	Celletips
Gns. merafkast (μ)	`=AVERAGE(E2:E61)`	Navngiv evt. området `Excess` for lettere læsbarhed.
Volatilitet (σ)	`=STDEV.S(E2:E61)`	Brug `STDEV.P` hvis du har hele populationen.

4. Årliggør hvis dine data ikke allerede er årlige

Antag at tallene er månedlige:

Årligt merafkast: =μ * 12
Årlig volatilitet: =σ * SQRT(12)

For daglige data erstatter du 12 med 252 (handelsdage).

5. Beregn sharpe-ratioen

=Årligt_merafkast / Årlig_volatilitet

I én linje, hvis dine celler med årlige størrelser hedder G2 (merafkast) og G3 (volatilitet):

=G2 / G3

Genvejspunkter og faldgruber

Samme frekvens – portefølje- og risikofri-serier skal måles i samme periodeintervaller, ellers bliver merafkastet skævt.
Negativ Sharpe – et negativt tal indikerer, at porteføljen indtjener mindre end den risikofrie rente pr. enhed risiko.
Konsistente cellereferencer – lås intervaller med $ (absolutte referencer) hvis du kopierer formler.
Sammenlign kun æbler med æbler – Sharpe-ratioer skal altid være årliggjorte, før de sammenlignes på tværs af strategier.

Med disse få skridt har du et skarpt, risikojusteret mål, der gør det enkelt at rangere porteføljer eller fonde: højere Sharpe = bedre kompensation for risikoen.

Beta: markedsfølsomhed med SLOPE eller COV/VAR

Beta måler, hvor følsomt et aktiv (eller en portefølje) er over for udsving i markedet. En beta på 1 følger markedet én-til-én, >1 er mere volatil end markedet, og <1 er mindre volatil. I Excel kan du få tallet på to måder:

SLOPE-funktionen: =SLOPE(aktiv_afkast; markeds_afkast)
Kovarians/varians-brøken: =COVARIANCE.S(aktiv; marked) / VAR.S(marked)

Trin-for-trin

Placer periodiske afkast for aktivet i kolonne B og markedets afkast i kolonne C (samme dato-rækker).
Vælg sample-versionerne (.S), medmindre du medtager hele populationen af afkast.
Indtast én af formlerne i en tom celle:
- =SLOPE(B2:B61;C2:C61)
- =COVARIANCE.S(B2:B61;C2:C61)/VAR.S(C2:C61)
Begge giver identisk resultat (decimaltal uden enhed).
Gentag for hvert aktiv, eller lad Excel udfylde nedad via absolutte kolonne- og relative rækkehenvisninger.

Eksempel

Aktiv	Vægt	Beta	Excel-formel
Aktie A	25 %	1,20	=SLOPE(A_Afkast;Market)
Aktie B	35 %	0,85	=COVARIANCE.S(B_Afk;Market)/VAR.S(Market)
Obligation C	40 %	0,15	=SLOPE(C_Afk;Market)

Porteføljebeta

Når alle enkeltbetaer er på plads, beregnes porteføljens samlede beta som et vægtet gennemsnit:

=SUMPRODUCT(vægte; betaer)

Med tallene ovenfor bliver det:

=SUMPRODUCT({0,25;0,35;0,40}; {1,20;0,85;0,15}) → 0,67

Praktiske tips

Frekvens: Beta påvirkes ikke af daglig vs. månedlig hyppighed, så længe begge serier har samme tidsenhed.
Datakvalitet: Rens serier for manglende værdier-beta hænger hurtigt i bremsen ved huller.
Navngivne områder: Giv ranges som rngAsset og rngMarket for mere læsbare formler.
Udvidet analyse: Brug LINEST() eller regressionsværktøjet i “Data → Dataanalyse” for samtidig at få alpha, R² og standardfejl.

XIRR: pengestrømsjusteret afkast

Cash-flow vægtede afkast – også kaldet pengestrøms-justeret afkast – tager højde for, hvornår du ind- og udbetaler penge. I Excel håndteres det med funktionen XIRR():

XIRR(cashflows; datoer; [gæt])

Du skal angive to parallelle intervaller:

Cashflows – beløb med modsat fortegn:
- Negative tal = kapital du indskyder (køb).
- Positive tal = kapital du modtager (salg, udbytter, slutværdi).
Datoer – de nøjagtige datoer for hver bevægelse.

Eksempel i praksis

Dato	Cashflow (kr.)	Forklaring
01-01-2022	-100.000	Startindskud
15-04-2022	-25.000	Ekstra køb
20-07-2022	5.000	Udbytte
31-12-2022	140.000	Værdi ved årets slutning

I cellerne A2:A5 ligger datoerne, og B2:B5 beløbene. Beregningen:

=XIRR(B2:B5; A2:A5)

giver f.eks. 14,6 %. Resultatet er allokeret til 365-dages basis, så ingen yderligere årliggørelse er nødvendig.

Typiske faldgruber

Der skal være mindst ét positivt og ét negativt cashflow – ellers returnerer XIRR en fejl.
Hvis Excel ikke kan konvergere, kan du give et startgæt: =XIRR(B2:B5;A2:A5;0,10).
Brug altid eksakte kalenderdatoer; forskydninger på få dage kan rykke afkastet mærkbart.

Tid-vægtet afkast: Geomean-tilgangen

Vil du derimod måle ren investeringsperformance uden ind- og udbetalingers indflydelse, anvendes tid-vægtet afkast. Typisk:

=GEOMEAN(1+periodeafkast) - 1

Her deles året op i underperioder, hver gang der er en pengestrøm, og afkastet for hver periode indgår med samme vægt. Denne metode viser, hvor god selve porteføljestyringen har været, mens XIRR viser, hvor god din kombination af investering og timing af indbetalinger har været.

Hvornår bruger man hvad?

Situation	Anbefalet mål	Hvorfor?
Personlig investeringsdagbog	XIRR	Medtager din faktiske kontantadfærd.
Fondsforvalterrapport	Tid-vægtet (GEOMEAN)	Renset for kundernes ind-/udstrøm.
Sammenligning med benchmark	Tid-vægtet	Sikrer æbler-med-æbler.
Budgettering af fremtidig opsparing	XIRR	Viser den rente, dine penge rent faktisk har “tjent”.