«

»

Aug 20

Monte-Carlo-Simulation von Aktienkursen Teil 2

monte-carloIm ersten Teil zur Simulation von Aktienkursen mit der Monte-Carlo Methode wurde eine einzelne, zufällige Kursbewegung erstellt.
Doch das Prinzip der Monte-Carlo-Simulation besteht nicht aus der Berechnung eines einzelnen Ergebnisses, sondern aus der Auswertung einer Vielzahl von zufälligen Ergebnissen.
Wer also den genauer Kurs einer Aktie in einem Jahr wissen will, sollte sich an den Wahrsager seiner Wahl wenden. Wer aber den wahrscheinlichen Kursbereich, in dem sich eine Aktie in einem Jahr bewegen könnte, liegt mit der Monte-Carlo Simulation richtig.

Die Grenzen der Simulation wurden bereits im ersten Teil angesprochen, und ich möchte sie an dieser Stelle wiederholen, damit keine Missverständnisse aufkommen:
Die Resultate beruhen auf historischen Daten. Ändern sich also die Rahmenbedingungen wie das Marktumfeld (z.B. Eintritt in Rezession) oder unternehmensspezifische Kriterien (z.B. Wettbewerb, Herstellungskosten, Produktakzeptanz etc.), wird eine Neubewertung erforderlich.
Zum Ende des Artikels werden wir nochmals auf einige Einsatzmöglichkeiten zurückkommen.

Doch vorab soll in vier weiteren Schritten, die Simulation und deren Auswertung fertiggestellt werden.

Schritt 9 – Kursermittlung zusammenfassen

Bisher mussten zur Kursermittlung drei Spalten verwendet werden. Im weiteren Verlauf soll dies in nur einer Spalte umgesetzt werden. Dazu schauen wir uns den Aufbau an:
9-Kursermttlung_1bDer erste zufällig ermittelten Kurs ist in der Zelle “F3″ zu finden, da in “F2″ ja der letzte Kurs der historischen Daten als Startwert verwendet werden muss. Für die Kursberechnung wird nun der Startwert (F2) und die Zufallsänderung in Spalte “E” benötigt.
Nun werfen wir einen Blick auf die Spalte “E”:

9-Kursermttlung_2bWir wollen nun in Spalte F3 den Wert von E3 durch die Formel in E3, also “KKLEINSTE($C$2:$C$253;D3)” ersetzen, was dann wie folgt aussieht:

9-Kursermttlung_3bAus Spalte “E” wird nun kein Wert verwendet, womit die Spalte gelöscht werden kann.

9-Kursermttlung_4bDer Kurs steht nun in Spalte “E”. Excel hat – wie alle mir bekannten Tabellenverarbeitungsprogramme – die schöne Eigenschaft, dass die Bezüge auf Spalten nach dem Löschen oder Hinzufügen einer Spalte angepasst werden, solange diese nicht durch die Verwendung des “$”-Zeichens einen festen Bezug erhalten haben. Somit wurde unter “F3 = F2*EXP(…” automatisch “E3 = E2*EXP(…”.

Ab Zelle “E4″ erscheint nun aber die Fehlermeldung “#BEZUG!”. Hintergrund ist ganz einfach, dass die Formel ab der Zelle noch nicht angepasst wurde und entsprechend noch nach dem Inhalt der gelöschten Spalte “E” gesucht wird. Das Problem wird im weiteren Verlauf durch Übernahme der Formel zur Kursermittlung auf die weiteren Zellen behoben werden.

Nachdem Spalte “E” eliminiert wurde, erfolgt nur noch der Aufruf des Wertes in Spalte “D”, bezogen auf “E3″ der Aufruf von “D3″ innerhalb der Formel “KKLEINSTE”.
“D3″ beinhaltet folgende Formel:

9-Kursermttlung_5bSomit müssen wir den Ausdruck “D3″ in Spalte “E” lediglich noch durch dessen Inhalt “ZUFALLSBEREICH(1;252)” ersetzen, was dann wie folgt aussieht:

9-Kursermttlung_6bNun können wir auch Spalte “D” löschen.
Um nun die Bezugsfehler zu beheben, klicken wir auf die Zelle “D3″.

9-Kursermttlung_7bEin kleines, schwarzes Quadrat wird sichtbar. Sobald Sie mit der Maus in den Bereich des Quadrates kommen, erscheint am Cursor ein schwarzes Kreuz. Halten Sie nun die linke Maustaste gedrückt und bewegen Sie den Cursor bis zur letzten Zeile mit der Fehlermeldung. Damit werden die Inhalte kopiert und die Fehlermeldungen verschwinden.

Falls Sie noch das in Teil 1 erstellte Diagramm auf dem Tabellenblatt haben, werden Sie nun einen neuen Chartverlauf sehen. Bei mir sieht er augenblicklich folgendermaßen aus:

9-Chart_8Bitte beachten Sie, dass nach jeder Änderung im Tabellendokument neue Zufallszahlen erzeugt werden und somit auch das Diagramm jedesmal angepasst wird. Deshalb sind in den Screenshots oben auch unterschiedliche Werte zu finden, obwohl die selben Zellen angezeigt werden.
Mit der Funktionstaste “F9″ können die Zufallszahlen auch ohne Änderungen aktualisiert werden.

Schritt 10 – Viele Zufallskurse anzeigen

Wie bereits mehrfach erwähnt, ist es das Ziel der Monte-Carlo-Simulation, viele zufällige Kurse zu erzeugen. Dazu haben wir nun die Voraussetzungen geschaffen. Es ist lediglich noch ein kleines Problem aus dem Weg zu räumen:

10-Kursermttlung_1b Lassen Sie uns Spalte “D” markieren, den Cursor auf das schwarze Quadrat platzieren und gedrückt halten, während wird die Kursdaten auf Spalte “E” kopieren. Unglücklicherweise kann es dabei leicht vorkommen, dass die Spaltenbreite verändert wird, anstatt dass die Spalte kopiert wird. Deshalb empfiehlt es sich, das Quadrat knapp unterhalb der Linie anzuwählen.

10-Kursermttlung_2bMit den hier verwendeten daten steht nun -0,01 € in Spalte “E”. Ein Blick auf die Formel zeigt, dass wir hier auf die Zelle “C2″ zugreifen, statt auf “B2″, wo unsere Schlusskurse zu finden sind. Das Problem lässt sich sehr einfach beheben, indem in Spalte “D2″ ein Dollarzeichen vor den Spaltenwert in der Formel gesetzt wird. Also aus “=B2″ wird “=$B2″.

10-Kursermttlung_3bNun kann Spalte “D” viele Male in die nachfolgenden Zeilen – wie beschrieben – kopiert werden.
Der Übersichtlichkeit halber habe ich nur 100 Kursdaten verwendet. In der Praxis werden es deutlich mehr sein.
Hier ein Auszug:

10-Kursermttlung_4Als Liniendiagramm sieht das ganze dann wie folgt aus:

10-Kursermttlung_5Erstellt wurde das Diagramm, indem alle Kursdaten (hier von D2 bis CY254) markiert wurden und anschließend über “Einfügen” – “Diagramm” – “Liniendiagramm” der Typ ausgewählt wurde.

Schritt 11 – Auswertung

Vorab an dieser Stelle nochmals der Hinweis, dass die Verwendung der Monte-Carlo-Simulation nur solange Sinn macht, soweit die unternehmensspezifischen und marktspezifischen Rahmenbedingungen nicht deutlich von denen der historischen Daten abweichen.

Auch lässt sich das Thema Auswertung hier nur stiefmütterlich behandeln, da diese sich letztendlich nach dem Zweck des jeweiligen Benutzers richtet.

Wie weit die historischen Daten zurückreichen sollen und über welchen Zeitraum die Simulation laufen sollte, ist ebenfalls abhängig von der Zielsetzung.
Die Monte-Carlo-Simulation wird z.B. beim Einsatz von Hebelprodukten verwendet, um das Risiko eines Totalverlustes oder eines selbst gewählten Stopp-Loss zu berechnen. Da hier meist kürzer Haltezeiten als beim Kauf von Aktien geplant sind, wird auch der Zeitraum der verwendeten historischen Daten und der Simulation kürzer sein (z.B. 3 Monate, sofern es sich nicht um Daytrading handelt). Im weiteren Verlauf werden wir zu dieser Art der Auswertung ein Beispiel sehen.

Im folgenden werden einige Möglichkeiten der Auswertung vorgestellt:

11-Auswertung_1bMit der Formel “KKLEINSTE” lässt sich der kleinste Wert aus dem gesamten Wertebereich der Simulation auslesen. In dem speziellen Fall war dies 113,59 €.

11-Auswertung_2bMit der Formel “KGRÖSSTE” lässt sich der größte Wert aus dem gesamten Wertebereich der Simulation auslesen. In dem Fall  372,85 €.

11-Auswertung_3bEine weitere Möglichkeit ist die Bildung von Mittelwerten (also Durchschnittskursen) zum Schlusstag. Dies lässt sich selbstverständlich für jeden beliebigen Tag im Beobachtungszeitraum erstellen.
Hier wurde bewusst darauf verzichtet, einen Mittelwert über den gesamten Simulationszeitraum zu erstellen, da zu Beginn die Abweichungen sehr gering sind und damit das Gesamtergebnis nicht repräsentativ ist.

11-Auswertung_4bInteressant ist die Anzahl der Schlusskurse, die höher notieren als der Startkurs. Umgesetzt wird dies mit der “ZÄHLENWENN”-Funkion. Da die Simulation über 100 Spalten abläuft, entspricht der Wert “69” hier auch dem prozentualen Verhältnis.
Anstelle des Zählkriteriums bezogen auf die Zelle B2 “>” &B2 könnte der Anfangskurs auch direkt in der Form “>175,80″ eingetragen werden.

11-Auswertung_5bHier werden aufgrund der Übersictlichkeit lediglich 100 Simulationen durchgeführt. In der Praxis sollten mindestens 1000 Simulationen pro Durchlauf erfolgen. Zusätzlich verbessern lässt sich das Ergebnis, wenn erzielte Resultate kopiert und die reinen Werte in weiteren Zellen eingefügt werden, um ein breiteres Ergebnisspektrum vorliegen zu haben.

11-Auswertung_6bIm Vorfeld wurde bereits darauf hingewiesen, dass es sinnvoll sein kann die Anzahl der Kurse unter (oder auch über) einem festgelegten Sollwert auszuwerten. Auch diese Auswertung wird mit der Funktion “ZÄHLENWENN” umgesetzt.
In diesem Simulationslauf waren 1542 Kurse unterhalb des Schwellkurses. Allerdings ist der reine Zahlenwert nicht sehr aussagekräftig.

11-Auswertung_7bDeshalb setzen wir die Anzahl der Kurse unter dem Schwellwert in Bezug zu allen Kursdaten. Da dies auf manuellem Wege sehr mühsam werden kann, verwenden wir hierzu die Funktion “ANZAHL2″, die alle Zellen im Datenbereich zählt, die nicht leer sind, was auf unsere simulierten Daten ja zutrifft.

Soweit die Ausführungen zum Thema Monte-Carlo-Simulation. Über Kritik, Fragen und Anregungen würde ich mich freuen.

 

3 Kommentare

  1. Johann

    Hallo Mathias,

    ich finde Deine Darstellung sehr gut nachvollziehbar und habe dies entsprechend umgesetzt.
    Aber beim Schritt 10 (Zufallskurse anzeigen) habe ich das Problem, dass bei den Kursen die Fehlermeldung #Zahl ab bestimmten Feldern einer Spralte folgend nach unten erscheint, bei jeder Neuberechnung wird diese Fehlermeldung dann wieder an anderen Spalten erzeugt. Die Hilfefunktion von Exxcel konnte mir dabei leider auch nicht weiterhelfen. Hast Du eine Idee woran das liegen könnte? Müssen irgendwo Rundungen vorgenommen werden oder müssen irgendwelche Einstellungen für Excel verändert werden??

    Schöne Grüße
    Johann

    1. Mathias Maier

      Hallo Johann,

      allein durch die Beschreibung kann ich das Problem nicht nachvollziehen.
      Vielleicht ragieren unterschiedliche Excel-Versionen unterschiedlich auf bestimmte Punkte. So habe ich gelesen, dass Excel unter Umständen Probleme mit Währungen hat.
      Formatiere einfach alle Werte mit Währungen in reine Zahlenwerte (am besten mit 2 Nachkommastellen zwecks Übersichtlichkeit).
      Falls der Fehler dann immer noch auftritt, könntest Du mir vielleicht einfach eine Excel-Datei mit den Fehlern zuschicken. Ich würde dann mal einen Blick darauf werfen.

      Beste Grüße
      Mathias

  2. Johann

    Hallo Mathias,

    danke für Deine schnelle Rückmeldung. Währungen hatte ich nicht definiert sondern Zahlenwerte.
    Wäre schön, wenn Du kurz einen Blick darauf werfen würdest. Manchmal ist man ja betriebsblind und findet die eigenen Fehler nicht.
    Wohin soll ich denn die Datei schicken, hier kann ich sie ja nicht anhängen.

    Schöne Grüße

    Johann

Hinterlasse eine Antwort

Deine E-Mail-Adresse wird nicht veröffentlicht. Erforderliche Felder sind markiert *

Sie können diese HTML-Tags verwenden: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>