Wie man Excel verwendet, um Aktienkurse zu simulieren

Einige aktive Anleger modellieren Variationen einer Aktie oder eines anderen Vermögenswerts, um deren Preis und den der darauf basierenden Instrumente, wie beispielsweise Derivate, zu simulieren. Die Simulation des Wertes eines Vermögenswerts in einer Excel-Tabelle kann eine intuitivere Darstellung seiner Bewertung für ein Portfolio bieten.

Erstellen einer Preismodellsimulation

Unabhängig davon, ob wir über den Kauf oder Verkauf eines Finanzinstruments nachdenken, kann die Entscheidung sowohl numerisch als auch grafisch unterstützt werden. Diese Daten können uns helfen, die nächste wahrscheinliche Bewegung des Vermögenswerts und die weniger wahrscheinlichen Bewegungen zu beurteilen.

Zunächst erfordert das Modell einige vorherige Hypothesen. Wir nehmen beispielsweise an, dass die täglichen Renditen oder „r(t)“ dieser Vermögenswerte normalverteilt sind mit dem Mittelwert „(μ)“ und der Standardabweichung Sigma „(σ).“ Dies sind die Standardannahmen, die wir hier verwenden werden, obwohl es viele andere gibt, die verwendet werden könnten, um die Genauigkeit des Modells zu verbessern.

Was gibt:

r(t)=S(t)−S(t−1)S(t−1)=μδt+σφδtwhere:δt=1 day=1365 of a yEeineRμ=mEeinenφ≅Nein(0,1)σ=annualized volatility\begin{ausgerichtet} &r (t) = \frac { S (t) – S (t – 1 ) }{ S (t – 1 ) } = \mu \delta t + \sigma \phi \sqrt {\delta t } \\ &\textbf{wobei:} \\ &\delta t = 1 \ \text{Tag} = \frac { 1 }{ 365 } \ \text{eines Jahres} \\ &\mu = \text{ Mittelwert} \\ & \ phi \ cong N (0, 1) \\ & \ sigma = \ text {annualisierte Volatilität} \\ \ end {align}​r(t)=S(t−1)

-10,-9.5,-14c0,-2,0.3,-3.3,1,-4c1.3,-2.7,23.83,-20.7,67.5,-54c44.2,-33.3,65.8,
-50.3,66.5,-51c1.3,-1.3,3,-2,5,-2c4.7,0,8.7,3.3,12,10s173,378,173,378c0.7,0,
35.3,-71,104,-213c68.7,-142,137.5,-285,206.5,-429c69,-144,104.5,-217.7,106.5,
-221c5.3,-9.3,12,-14,20,-14H400000v40H845.2724s-225.272,467,-225.272,467
s-235,486,-235,486c-2.7,4.7,-9,7,-19,7c-6,0,-10,-1,-12,-3s-194,-422,-194,-422
s-65,47,-65,47z M834 80H400000v40H845z“>

Was dazu führt:

Schließlich:

S(t)−S(t−1)= S(t−1)μδt+S(t−1)σφδtS(t)= S(t−1)+S(t−1)μδt + S(t−1)σφδtS(t)= S(t−1)(1+μδt+σφδt)\begin{ausgerichtet} S ( t ) – S ( t – 1 ) = & \ S ( t – 1 ) \mu \delta t + S ( t – 1 ) \sigma \phi \sqrt { \delta t } \\ S ( t ) = & \ S ( t – 1 ) + S ( t – 1 ) \mu \delta t \ + \\ & \ S ( t – 1 ) \sigma \phi \sqrt { \delta t } \\ S ( t ) = & \ S ( t – 1 ) ( 1 + \mu \delta t + \sigma \phi \sqrt { \delta t } ) \\ \end{ausgerichtet}S(t)−S(t−1)=S(t)=S(t)=​ S(t−1)μδt+S(t−1)σϕδt
-10,-9.5,-14c0,-2,0.3,-3.3,1,-4c1.3,-2.7,23.83,-20.7,67.5,-54c44.2,-33.3,65.8,
-50.3,66.5,-51c1.3,-1.3,3,-2,5,-2c4.7,0,8.7,3.3,12,10s173,378,173,378c0.7,0,
35.3,-71,104,-213c68.7,-142,137.5,-285,206.5,-429c69,-144,104.5,-217.7,106.5,
-221c5.3,-9.3,12,-14,20,-14H400000v40H845.2724s-225.272,467,-225.272,467
s-235,486,-235,486c-2.7,4.7,-9,7,-19,7c-6,0,-10,-1,-12,-3s-194,-422,-194,-422
s-65,47,-65,47z M834 80H400000v40H845z“>
-10,-9.5,-14c0,-2,0.3,-3.3,1,-4c1.3,-2.7,23.83,-20.7,67.5,-54c44.2,-33.3,65.8,
-50.3,66.5,-51c1.3,-1.3,3,-2,5,-2c4.7,0,8.7,3.3,12,10s173,378,173,378c0.7,0,
35.3,-71,104,-213c68.7,-142,137.5,-285,206.5,-429c69,-144,104.5,-217.7,106.5,
-221c5.3,-9.3,12,-14,20,-14H400000v40H845.2724s-225.272,467,-225.272,467
s-235,486,-235,486c-2.7,4.7,-9,7,-19,7c-6,0,-10,-1,-12,-3s-194,-422,-194,-422
s-65,47,-65,47z M834 80H400000v40H845z“>

Und jetzt können wir den Wert des heutigen Schlusskurses mit dem Schlusskurs des Vortages ausdrücken.

• Berechnung von μ:

Um μ, den Mittelwert der täglichen Renditen, zu berechnen, nehmen wir die n aufeinander folgenden letzten Schlusskurse und wenden den Durchschnitt der Summe der n vergangenen Kurse an:

• Die Berechnung der Volatilität σ – Volatilität

φ ist eine Volatilität mit einem Durchschnitt aus Zufallsvariable Null und Standardabweichung Eins.

Berechnung der historischen Volatilität in Excel

Für dieses Beispiel verwenden wir die Excel-Funktion „= NORMSINV (RAND()).“ Auf Basis der Normalverteilung berechnet diese Funktion eine Zufallszahl  mit einem Mittelwert von null und einer Standardabweichung von eins. Um μ zu berechnen, mitteln Sie einfach die Ausbeuten mit der Funktion Ln (.): die Log-Normalverteilung.

Geben Sie in Zelle F4 „Ln (P (t) / P (t-1)“ ein“

In der F19-Zellensuche „= AVERAGE (F3:F17)“

Geben Sie in Zelle H20 „= AVERAGE (G4: G17)“ ein.

Geben Sie in Zelle H22 „= 365*H20“ ein, um die annualisierte Varianz zu berechnen

Geben Sie in Zelle H22 „= SQRT(H21)“ ein, um die annualisierte Standardabweichung zu berechnen

Wir haben jetzt also den „Trend“ der vergangenen Tagesrenditen und die Standardabweichung (die Volatilität ). Wir können unsere oben gefundene Formel anwenden:

S(t)−S(t−1)= S(t−1)μδt+S(t−1)σφδtS(t)= S(t−1)+S(t−1)μδt + S(t−1)σφδtS(t)= S(t−1)(1+μδt+σφδt)\begin{ausgerichtet} S ( t ) – S ( t – 1 ) = & \ S ( t – 1 ) \mu \delta t + S ( t – 1 ) \sigma \phi \sqrt { \delta t } \\ S ( t ) = & \ S ( t – 1 ) + S ( t – 1 ) \mu \delta t \ + \\ & \ S ( t – 1 ) \sigma \phi \sqrt { \delta t } \\ S ( t ) = & \ S ( t – 1 ) ( 1 + \mu \delta t + \sigma \phi \sqrt { \delta t } ) \\ \end{ausgerichtet}S(t)−S(t−1)=S(t)=S(t)=​ S(t−1)μδt+S(t−1)σϕδt
-10,-9.5,-14c0,-2,0.3,-3.3,1,-4c1.3,-2.7,23.83,-20.7,67.5,-54c44.2,-33.3,65.8,
-50.3,66.5,-51c1.3,-1.3,3,-2,5,-2c4.7,0,8.7,3.3,12,10s173,378,173,378c0.7,0,
35.3,-71,104,-213c68.7,-142,137.5,-285,206.5,-429c69,-144,104.5,-217.7,106.5,
-221c5.3,-9.3,12,-14,20,-14H400000v40H845.2724s-225.272,467,-225.272,467
s-235,486,-235,486c-2.7,4.7,-9,7,-19,7c-6,0,-10,-1,-12,-3s-194,-422,-194,-422
s-65,47,-65,47z M834 80H400000v40H845z“>
-10,-9.5,-14c0,-2,0.3,-3.3,1,-4c1.3,-2.7,23.83,-20.7,67.5,-54c44.2,-33.3,65.8,
-50.3,66.5,-51c1.3,-1.3,3,-2,5,-2c4.7,0,8.7,3.3,12,10s173,378,173,378c0.7,0,
35.3,-71,104,-213c68.7,-142,137.5,-285,206.5,-429c69,-144,104.5,-217.7,106.5,
-221c5.3,-9.3,12,-14,20,-14H400000v40H845.2724s-225.272,467,-225.272,467
s-235,486,-235,486c-2.7,4.7,-9,7,-19,7c-6,0,-10,-1,-12,-3s-194,-422,-194,-422
s-65,47,-65,47z M834 80H400000v40H845z“>

Wir werden eine Simulation über 29 Tage durchführen, also dt = 1/29. Unser Ausgangspunkt ist der letzte Schlusskurs: 95.

• Geben Sie in die Zelle K2 „0“ ein.
• Geben Sie in Zelle L2 „95“ ein.
• Geben Sie in Zelle K3 „1“ ein.
• Geben Sie in die Zelle L3 ein „= L2 * (1 + $F$19 * (1/29) + $H$22 *SQRT(1/29)*NORMSINV (RAND ())).“

Als Nächstes ziehen wir die Formel in der Spalte nach unten, um die gesamte Reihe simulierter Preise zu vervollständigen.

Dieses Modell ermöglicht es uns, eine Simulation der Vermögenswerte bis zu 29 angegebenen Daten zu finden, mit der gleichen Volatilität wie die vorherigen 15 von uns ausgewählten Preise und mit einem ähnlichen Trend.

Schließlich können wir auf „F9“ klicken, um eine weitere Simulation zu starten, da wir die Randfunktion als Teil des Modells haben.