Hur man använder Excel för att simulera aktiekurser

Vissa aktiva investerare modellerar varianter av en aktie eller annan tillgång för att simulera dess pris och de instrument som är baserade på den, till exempel derivat. Att simulera värdet på en tillgång i ett Excel-kalkylblad kan ge en mer intuitiv representation av dess värdering för en portfölj.

Key Takeaways

• Handlare som vill testa en modell eller strategi kan använda simulerade priser för att validera dess effektivitet.
• Excel kan hjälpa till med din back-testning med en monte carlo-simulering för att generera slumpmässiga prisrörelser.
• Excel kan också användas för att beräkna historisk volatilitet för att ansluta till dina modeller för större noggrannhet.

Bygga en prissättningssimulering

Oavsett om vi överväger att köpa eller sälja ett finansiellt instrument, kan beslutet stödjas genom att studera det både numeriskt och grafiskt. Dessa data kan hjälpa oss att bedöma nästa troliga drag som tillgången kan göra och de rörelser som är mindre troliga.

Först och främst kräver modellen några tidigare hypoteser. Vi antar till exempel att den dagliga avkastningen eller "r (t)" av dessa tillgångar normalt fördelas med medelvärdet, "(μ), " och standardavvikelse sigma, "(σ)." Dessa är standardantagandena som vi kommer att använda här, även om det finns många andra som kan användas för att förbättra modellens noggrannhet.

Vilket ger:

Vilket resulterar i:

Till sist:

Och nu kan vi uttrycka värdet på dagens stängningskurs med hjälp av dagen innan.

• Beräkning av μ:

För att beräkna μ, vilket är medelvärdet för den dagliga avkastningen, tar vi de n på varandra följande priserna för slutet och tillämpar, vilket är medelvärdet av summan av de n tidigare priserna:

• Beräkningen av volatiliteten σ - volatilitet

φ är en volatilitet med ett genomsnitt av slumpmässig variabel noll och standardavvikelse.

Beräkna historisk volatilitet i Excel

I det här exemplet kommer vi att använda Excel-funktionen "= NORMSINV (RAND ())." Med utgångspunkt från normalfördelningen beräknar denna funktion ett slumptal med ett medelvärde på noll och en standardavvikelse på en. För att beräkna μ, genomsnittligt ger utbytena med funktionen Ln (.): Log-normalfördelningen.

I cell F4 anger du "Ln (P (t) / P (t-1)"

I F19-cellsökningen "= AVERAGE (F3: F17)"

I cell H20 anger du “= AVERAGE (G4: G17)

I cell H22 anger du "= 365 * H20" för att beräkna den årliga varianten

I cell H22 anger du "= SQRT (H21)" för att beräkna den årliga standardavvikelsen

Så vi har nu "trenden" från tidigare dagliga avkastningar och standardavvikelsen (volatiliteten). Vi kan tillämpa vår formel som finns ovan:

Vi kommer att göra en simulering under 29 dagar, därför är dt = 1/29. Vår utgångspunkt är det sista nära priset: 95.

• Ange "0." i cellen K2.
• Ange "95." i cellen L2.
• Ange "1." i cellen K3.
• I cellen L3 anger du "= L2 * (1 + $ F $ 19 * (1/29) + $ H $ 22 * ​​SQRT (1/29) * NORMSINV (RAND ()))."

Därefter drar vi formeln ner i kolumnen för att slutföra hela serien med simulerade priser.

Med denna modell kan vi hitta en simulering av tillgångarna ner till 29 angivna datum, med samma volatilitet som de tidigare 15 priserna vi valde och med en liknande trend.

Slutligen kan vi klicka på "F9" för att starta ytterligare en simulering eftersom vi har randfunktionen som en del av modellen.