Du kannst eine Monte-Carlo-Simulation in Excel benutzen, um verschiedene Szenarien zu erzeugen und die möglichen Ergebnisse zu betrachten. Bei einer Monte-Carlo-Simulation wird eine sehr große Anzahl gleichartiger Zufallsexperimente auf einmal ausgeführt. Für unsere Beispiel-Simulation verwende ich eine angenommene tägliche Durchschnittsrendite von 0,03% (8% p.a./252 Tage) und eine Standardabweichung von 0,95%.
Die Monte-Carlo-Simulation in Excel
Was wollen wir mit der Simulation erreichen? Durch die Monte-Carlo-Simulation in Excel soll für 10.000 Wochen die jeweils kumulierte Wochenrendite erzeugt werden. Die Renditen untersuchen wir dann im Nachgang. Im ersten Schritt benötigen wir für jeden Arbeitstag eine Zufallsrendite. Die Formel dazu lautet:
=NORMINV(ZUFALLSZAHL();Mittelwert;Standardabweichung)
Anschließend berechnen wir die kumulierte Wochenrendite. Starte dazu mit der Formel bei Dienstag und ziehe die Formel bis Freitag. Hier habe ich dir beschrieben, wie du generell Renditen in Excel berechnest.
Monte-Carlo-Simulation in Excel: Mit F9 neue Zufallszahlen erzeugen
Jedes Mal, wenn du nun F9 drückst wird das Excel-Blatt neu berechnet und die Werte ändern sich. Als nächstes brauchen wir in einer Spalte eine Nummerierung mit der Anzahl der gewünschten Simulationen. Ich habe beispielhaft von 1 bis 10.000 nummeriert, da ich 10.000 simulierte Wochen erhalten möchte. In Zelle C20 hole ich mir den Bezug (=D16) zu der kumulierten Wochenrendite aus D16. Markiert nun den Simulationsbereich (Tipp: Klicke auf eine Zahl und drücke STRG+A).
Gehe nun im Reiter „Daten/Prognose/Was-wäre-wenn-Analyse/Dropdown Menü/“ auf „Datentabelle“. Der Befehl Datentabelle führt nochmals alle Operationen aus, welche wir bereits mit der kumulativen Wochenrendite berechnet haben; und zwar bis zum Ende der Nummerierung. Ignoriere den „Wert aus Zeile“. Wähle für den Wert in Spalte einfach eine leere Zelle in dem Arbeitsblatt aus. Relevant für das Funktionieren ist nur, dass der Bezug zur kumulierten Wochenrendite in der Spalte neben der Nummerierung enthalten ist. Außerdem muss der Bezug eine Zeile über dem Beginn der Nummerierung sein. Die Excel-Funktion, die sich dahinter verbirgt nennt sich =MEHRFACHOPERATION(). Excel hat nun für uns die die einzelnen Tagesrenditen von Montag bis Freitag und die kumulierte Wochenrendite 10.000-mal simuliert und berechnet.
Lageparameter der Zufallsrenditen berechnen
Anschließend können wir noch den Mittelwert und die Standardabweichung der Wochenrenditen berechnen. Auch interessant ist, in wie vielen der 10.000 Fälle beispielsweise eine Rendite größer 5% oder kleiner -5% auftritt. Das geht ganz einfach mit =ZÄHLENWENN(C21:C10020;“<-5%“)/ANZAHL(B21:B10020). Um einen Blick über die Verteilung der Wochenrenditen zu bekommen, kann es auch Sinn machen, sich die Verteilung in einem Histogramm zu visualisieren. Markiere dazu die Wochenrenditen von 1 bis 10.000 und gehe auf „Einfügen/Alle Diagramme anzeigen“ und wähle das Histogramm aus.
Übrigens…
Die Idee zur Monte-Carlo-Simulation entstand bereits in den 1930er Jahren durch Enrico Fermi (Kernphysiker und Nobelpreisträger). Ausgeführt werden konnten die Simulationen aber erst im 2. Weltkrieg durch Stanislaw Ulam und John von Neumann. Die beiden Mathematiker arbeiteten damals an einem geheimen Projekt im Rahmen der Entwicklung der ersten Atombombe. Von Neumann wählte den Namen „Monte-Carlo“ in Anspielung auf die in Monaco ansässige Spielbank.