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 zu simulieren. wie Derivate. Die Simulation des Wertes eines Vermögenswerts in einer Excel-Tabelle kann eine intuitivere Darstellung seiner Bewertung für ein Portfolio bieten.

Die zentralen Thesen

• Händler, die ein Modell oder eine Strategie Backtesting durchführen möchten, können simulierte Preise verwenden, um ihre Effektivität zu überprüfen.
• Excel kann bei Ihrem Backtesting mit einer Monte-Carlo-Simulation helfen, um zufällige Preisbewegungen zu generieren.
• Excel kann auch verwendet werden, um die historische Volatilität zu berechnen, um sie in Ihre Modelle einzubinden, um eine höhere Genauigkeit zu erzielen.

Erstellen einer Preismodellsimulation

Ob wir den Kauf oder Verkauf eines Finanzinstruments erwägen, die Entscheidung kann unterstützt werden, indem sie sowohl numerisch als auch grafisch untersucht wird. Diese Daten können uns helfen, die nächste wahrscheinliche Bewegung des Vermögenswerts und die weniger wahrscheinlichen Bewegungen zu beurteilen.

Zuerst, das Modell erfordert einige vorherige Hypothesen. Wir nehmen an, zum Beispiel, dass die tägliche Rendite, oder "r(t), " dieser Vermögenswerte werden normalerweise mit dem Mittelwert verteilt, "(μ), " und 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.

R ( T ) = S ( T ) − S ( T − 1 ) S ( T − 1 ) ~ n ( μ , σ ) wo: S ( T ) = nah dran T S ( T − 1 ) = nah dran T − 1 \begin{ausgerichtet} &r ( t ) =\frac { S ( t ) - S ( t - 1 ) }{ S ( t - 1 ) } \sim N ( \mu, \sigma) \\ &\textbf{wobei:} \\ &S ( t ) =\text{close}_t \\ &S ( t - 1 ) =\text{close}_{t - 1} \\ \end{ ausgerichtet} ​r(t)=S(t−1)S(t)−S(t−1)​∼N(μ, σ)wobei:S(t)=Schrank​S(t−1)=Schrank−1​​

Was gibt:

R ( T ) = S ( T ) − S ( T − 1 ) S ( T − 1 ) = μ δ T + σ φ δ T wo: δ T = 1 Tag = 1 3 6 5 eines Jahres μ = bedeuten φ ≅ n ( 0 , 1 ) σ = annualisierte Volatilität \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{ausgerichtet} ​r(t)=S(t−1)S(t)−S(t−1)​=μδt+σϕδt​wobei:δt=1 Tag=3651​ eines Jahresμ=Mittelwertϕ≅N(0, 1)σ=annualisierte Volatilität​

Was dazu führt:

S ( T ) − S ( T − 1 ) S ( T − 1 ) = μ δ T + σ φ δ T \begin{ausgerichtet} &\frac { S ( t ) - S ( t - 1 ) }{ S ( t - 1 ) } =\mu \delta t + \sigma \phi \sqrt { \delta t } \\ \ Ende{ausgerichtet} ​S(t−1)S(t)−S(t−1)​=μδt+σϕδt​​

Schließlich:

S ( T ) − S ( T − 1 ) = S ( T − 1 ) μ δ T + S ( T − 1 ) σ φ δ T S ( T ) = S ( T − 1 ) + S ( T − 1 ) μ δ T + S ( T − 1 ) σ φ δ T S ( 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​ S(t−1)+S(t− 1)μδt + S(t−1)σϕδt​ S(t−1)(1+μδt+σϕδt​)​

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

• Berechnung von μ:

Um μ zu berechnen, das ist der Mittelwert der täglichen Renditen, wir nehmen die n aufeinanderfolgenden letzten Schlusskurse und wenden an, das ist der Durchschnitt der Summe der n vergangenen Preise:

μ = 1 n Σ T = 1 n R ( T ) \begin{aligned} &\mu =\frac { 1 }{ n } \sum_{ t =1 } ^ { n } r ( t ) \\ \end{aligned} ​μ=n1​t=1∑n​r(t)​

• 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, wir verwenden die Excel-Funktion "=NORMSINV (RAND ())." Mit einer Basis aus der Normalverteilung Diese Funktion berechnet eine Zufallszahl mit einem Mittelwert von null und einer Standardabweichung von eins. Um μ zu berechnen, mitteln Sie einfach die Renditen mit der Funktion Ln (.):der Log-Normalverteilung.

In Zelle F4, Geben Sie "Ln (P (t) / P (t-1)" ein"

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

In Zelle H20, Geben Sie „=DURCHSCHNITT“ ein (G4:G17)

In Zelle H22, Geben Sie "=365*H20" ein, um die annualisierte Varianz zu berechnen

In Zelle H22, Geben Sie "=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 ) σ φ δ T S ( T ) = S ( T − 1 ) + S ( T − 1 ) μ δ T + S ( T − 1 ) σ φ δ T S ( 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​ S(t−1)+S(t− 1)μδt + S(t−1)σϕδt​ S(t−1)(1+μδt+σϕδt​)​

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

• In der Zelle K2, Geben Sie "0" ein.
• In der Zelle L2, Geben Sie "95" ein.
• In der Zelle K3, Geben Sie "1" ein.
• In der Zelle L3, Geben Sie "=L2 * (1 + $F$19 * (1/29) + $H$22 *SQRT(1/29)*NORMSINV (RAND ())) ein."

Nächste, Wir ziehen 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 ersten 15 von uns ausgewählten Kurse und mit einem ähnlichen Trend.

Zuletzt, Wir können auf "F9" klicken, um eine weitere Simulation zu starten, da wir die Randfunktion als Teil des Modells haben.