Räkna ut volatilitet, standardavvikelse och Sharpe-kvot med Excel | RikaTillsammans

Räkna ut volatilitet, standardavvikelse och Sharpe-kvot med Excel

Verktyg för att räkna ut viktiga nyckeltal för din portfölj

Det här en väldigt teknisk artikel för dig som både är ekonomi- och excelnörd. Den visar hur du själv kan räkna ut nyckeltal för din portfölj såsom standardavvikelse, volatilitet, varians och sharpe-kvot.

Normalt sett räknas dessa nyckeltal ut automatiskt av bl.a. Nordnet och Avanza, men på sistone har båda dessa banker lyckats sabotera mina avkastningskurvor. Nedanstående bild visar hur Nordnet under en dag tappade värdet på en av mina fonder vilket får det att se ut som att jag gjorde en stor förlust vilket jag inte gjorde. Eftersom beräkningarna bygger på deras data, ville jag därför kontrollräkna dessa nycketal själv.

2016-07-08_16-11-43

 

En annan anledning är att jag upptäckte att det inte var helt trivialt att räkna ut dessa nyckeltal. Artiklar på nätet ger en kort introduktion men det var klurigt att omvandla det till sina egna siffror.

Standardavvikelsen från medelvärdet

Volatilitet är ett mått på hur mycket ett värde, t.ex. aktiekurs, avviker från ett medelvärde. En hög volatilitet betyder att avvikelsen från medelvärdet är stor. En låg volatilitet indikerar tvärtom en låg avvikelse och en mer stabil kursutveckling. I allra flesta fall är en låg volatilitet att föredra framför alternativet.

Illustration över volatilitet

Standardavvikelsen är relativt enkel att räkna ut. Matematiskt sett ser en approximation för formeln ut som följer:

160709-stdav

Där:

  • σ = standardavvikelse
  • σ2 = variansen
  • X = värde i talserie
  • µ = medelvärde för talserien
  • N = antalet tal i talserie

Det går att räkna enligt följande formel i Excel eller att använda Excels funktion STADAV.S(värden). I videon nedan försöker jag förklara det så bra som möjligt och visar både det manuella sättet och det automatiska via formeln. Jag visar även hur man kan räkna standardavvikelse på den procentuella förändringen.

Excel-filen som jag använder i videon ovan finns nedan:

Räkna ut volatiliteten

Volatiliteten är lite klurigare att räkna ut eftersom den ska räknas om de logaritm-normaliserade procentuella förändringarna i aktiekurserna omräknat till årsbasis genom kvadratroten av tiden. Anledningen till att den procentuella förändringen logaritmiseras beror på Black & Scholes optionsformel som gör ett antagande om att de procentuella fördelningarna är normalfördelade.

Formeln ser ut som följer:

160709-volatilitet

Där:

  • kt = slutkurs för dag t
  • kt-1 = slutkurs dagen innan dag t
  • stdav = standardavvikelse
  • ln = naturliga logaritmen med basen e
  • N = antal börsdagar på ett år, vanligtvis 252 st.

Till skillnad från det föregående exemplet har jag inte hittat någon formel för beräkning av volatiliteten i Excel, utan den får räknas manuellt på det här sättet enligt nedanstående video:

Excel-filen som jag använder i videon ovan finns nedan:

Tolkningen är att ju lägre volatilitet, desto bättre. Helst vill man ha en volatilitet som är lägre än medelavkastningen för det betyder att du med stor sannolikhet kommer att gå plus. Det har ju t.ex. fonden Catella Avkastning lyckats med ett par år i rad, vilket är en bedrift.

Räkna ut Sharpe-kvot

Sharpe-kvoten är ett mått på riskjusterad avkastning. Enkelt sagt kan man säga att det handlar om hur många procent avkastning du får för varje procent risk som du tar.

Formeln ser ut som följer:

Formel för Sharpe-kvoten

Där:

  • a= den förväntade avkastningen för din portfölj
  • ar = den avkastningen som du kan få utan risk (t.ex. bankkonto)
  • σ = din portföljs standardavvikelse

Det här har jag inte heller hittat någon bra formel för i Excel, så det blir en manuell beräkning till.

Excel-filen som jag använder i videon ovan finns nedan:

Normalt sett ges Sharpe-kvoten för alla fonder och Nordnet räknar även ut den åt dig. Det gör dock inte Avanza tyvärr. Tolkningen här är att ju högre Sharpe-kvot desto bättre. Det betyder nämligen att du får mer ”betalt” för varje procent risk som du tar.

Relaterade artiklar

Relaterade etiketter och ämnen

verktyg, video

Kommentera

6 kommentarer finns till denna artikel:

  1. Kan det vara så att Nordnet/Avanza har fler mätpunkter än en per dag som du använder i dina exempel, och att de därför får annorlunda resultat?

    Patrik
    1. Vet inte faktiskt. När det gäller Sharpe-kvoten så räknar de på 3-månader statsskuldväxlar och den datan har jag inte sammanställt. Eller tänkte du något annat?

      Jan Bolmeson
    2. Det var jag som var lite snabb – jag läste först att Sharpekvoten var felaktigt uträknad, men jag inser när jag läser igen att Nordnet räknade ut den, men inte Avanza. Ber om ursäkt!

      Patrik
  2. Hej Jan,
    Tack för en mycket bra genomgång, dock undrar jag några saker:
    1.Från vilken källa kan man läsa in till Excel alla slutkurser för OMXS30 under ett tidsintervall? Eller för den delen slutkurserna för de aktier eller fonder man vill beräkna stdavvikelse eller volatilitet på? Hur läser du in detta till excel och varifrån läser du datat?

    2.Om man vill beräkna t.ex volatiliteten för en portfölj med 15 akter eller 15 fonder, ska man då alltså först beräkna volatiliteten för vart och ett utav de 15 innehaven och sedan beräkna det viktade medelvärdet av dessa volatiliteter för att på så sätt erhålla den totala portföljens volatilitet?

    Tack och Mvh Stefan

    Stefan A
    1. Jag brukar hämta från lite olika datakällor. En favorit är netfonds.no där man kan ladda ner det i .csv format. Sedan publicerar t.ex. Affärsvärlden sitt AFGX index på sin hemsida.

      Jag skulle beräkna hela portföljens volatilitet. Fonders volatilitet redovisas på Morningstar och för aktier så kan man räkna på samma sätt som för fonder eller enligt exemplet ovan.

      Lycka till!

      Jan Bolmeson