Jump to content
PopOff

Historischer Backtest; Rebalancieren in Excel

Recommended Posts

PopOff
Posted

Hallo zusammen,

 

ich möchte gerne mit historischen Indexkursen ein automatisches Rebalancing in Excel durchführen. (Zum Backtesting)

 

Ich habe mir Aktienkurse von den letzten 50 Jahren besorgt und möchte nun das mir Excel jedes Jahr,

einmal die Asset Klassen wieder in Ihren Ursprünglichen Gewichtung herstellt. (Ohne Zuflüsse; Nur durch Verkäufe und Käufe)

Nur weiß ich nicht genau wie ich da vorgehen soll damit mir Excel jedes Jahr die Asset Allocation wieder herstellt.

 

Da ich in Excel nicht so sehr versiert bin muss ich euch um Hilfe fragen.

 

Wie kann man das In Excel realisieren? Müsste man da in VBA ein Makro programmieren?

 

Bin für jede Hilfe Dankbar.

 

Grüße,

PopOff

Share this post


Link to post
vanity
Posted

Nein, muss man nicht. Das geht auch mit Bordmitteln und ist sogar relativ einfach.

 

Frage: Wie verhält sich ein periodisch (z. B. jährlich) rebalanciertes Portfolio verschiedener Assets entsprechender Gewichtung?

Antwort: Es generiert pro Periode die Performance, die sich aus der Summe der entsprechend gewichteten Performance(s) der jeweiligen Periode der einzelnen Assets ergibt.

 

 

Abbildung:

 

1. Gegebene Zeitreihen (Ki) der Indices j um Performance (Pi, j) der Periode ergänzen (Pi, j = Ki, j/Ki-1, j - 1)

2. Performance der Gesamtallokation einer Periode i ermitteln als Pi, gesamt = Summe(j=1..n) (Pi, j * Gj)

3. (optional) Zeitreihe der Gesamtallokation (Ki, gesamt) ermitteln aus Periodenperformance als Ki, gesamt = Ki-1, gesamt * (1 + Pi, gesamt)

 

K = Indexreihe (TR)

P = Performancereihe (pro Periode)

G = Gewichtung (der einzelnen Indices)

 

 

Template:

 

Balancieren.xls

 

(gelb unterlegt: Eingabefelder. die eigentliche Berechnung steckt in Spalte J (und I))

 

 

Fingerübung

 

Wie wird das gemacht, wenn nicht balanciert wird?

Share this post


Link to post
PopOff
Posted

Nein, muss man nicht. Das geht auch mit Bordmitteln und ist sogar relativ einfach.

 

Frage: Wie verhält sich ein periodisch (z. B. jährlich) rebalanciertes Portfolio verschiedener Assets entsprechender Gewichtung?

Antwort: Es generiert pro Periode die Performance, die sich aus der Summe der entsprechend gewichteten Performance(s) der jeweiligen Periode der einzelnen Assets ergibt.

 

 

Abbildung:

 

1. Gegebene Zeitreihen (Ki) der Indices j um Performance (Pi, j) der Periode ergänzen (Pi, j = Ki, j/Ki-1, j - 1)

2. Performance der Gesamtallokation einer Periode i ermitteln als Pi, gesamt = Summe(j=1..n) (Pi, j * Gj)

3. (optional) Zeitreihe der Gesamtallokation (Ki, gesamt) ermitteln aus Periodenperformance als Ki, gesamt = Ki-1, gesamt * (1 + Pi, gesamt)

 

K = Indexreihe (TR)

P = Performancereihe (pro Periode)

G = Gewichtung (der einzelnen Indices)

 

 

Template:

 

Balancieren.xls

 

(gelb unterlegt: Eingabefelder. die eigentliche Berechnung steckt in Spalte J (und I))

 

 

Fingerübung

 

Wie wird das gemacht, wenn nicht balanciert wird?

 

Erstmal Danke für deine Hilfe.

 

Ich habe mir mal in deiner Tabelle die Kuse von den MSCI World und MSCI Emerging Markets hineinkopiert. (Die letzten 25 Jahre)

Hierbei habe ich die Gewichtung von 70% World und 30% E.M gewählt.

Dann habe ich das ganze versucht ohne eine Jährliches Rebalancieren zu berechnen.

Dabei kam eine höhere annualisierte Rendite wie mit Rebalancieren heraus (0,2% p.a) Kann das stimmen?

Oder ist hier meine Berechnung falsch?

Kopie von Balancieren.xls

Share this post


Link to post
lurklurk
Posted · Edited by lurklurk

Ja, das habe ich auch, wenn ich näherungsweise in die Boglehead-Excel-Datei ab 1988 40% US Large Blend, 30'% EAFE und 30% EM eintrage (da es in der Datei keinen World-Indexfonds gibt). Ich habe die Datei einmal unten angehängt, das Tabellenblatt "Portfolio" mit diesen 3 Positionen und 25 Jahren Berechung müsste beim Öffnen direkt aufgeklappt sein (linke Hälfte der Tabellen und Grafiken). Zwar in Dollar, aber egal für diesen Zweck, gleiches Ergebnis. Die p.a.-Rendite steht unter "CAGR". Aus irgendeinem historischen(?) Grund taucht in der Auswertung auch ein "CHP" (Coffeehouse Portfolio) zum Vergleich auf - können wir ignorieren.

 

Die Erklärung ist simpel: Rebalancing dient in erster Linie der Risiko-/Crashsteuerung, nicht der Renditesteigerung. Daher ist auch die Volatilität/Standardabweichung und der maximale Verlust etwas geringer, wie wir in der Datei sehen können ("Std.Dev" und "max Drawdown"). Die EM sind seit 88 besser gelaufen als die DM, und da gab's ja auch noch die Japan-Blase bei den DM. Durch das Rebalancing wurde offenbar mehr in die renditeschwächeren DM umgeschichtet als durch den geringeren EM-Anteil bei EM-Crashs eingespart wurde.

 

edit: ich sehe gerade, dass es unter http://www.boglehead...p?f=10&t=120052 eine neuere Version der Datei mit 2-3 kleineren Fehlerkorrekturen gibt.

Share this post


Link to post
PopOff
Posted · Edited by PopOff

Hallo,

 

ich habe mir nun mal ein Backtest zu den wichtigsten Indizes angefertigt.

Dabei bin ich von einer Gewichtung von 70% Aktien und 30 % Rexp ausgegangen,

und jedes Monat erfolgt ein Rebalancing der Asset Klassen.

Ist es in Excel nun möglich dass das Depot nur einmal im Jahr Rebalanciert wird?

Gibt es in Excel da irgendeine Formel das wenn ein Jahr vergangen ist, dass nur dann

das Depot Rebalanciert wird?

Dann könnte man auch berechnen im welchen Monat eine Wiederherstellung der

Asset Klassen am besten gewesen wäre.

 

thx

Depot.xls

Share this post


Link to post
vanity
Posted

Das wäre ein Ansatz (vielleicht geht es auch eleganter)

 

Balancieren_FesterMonat.xls

 

Zelle M3: in welchem Monat wird balanciert?

 

Spalte K zur Steuerung mit Bezug auf Spalte A und M3

Spalten L, M Wertentwicklung, Formel abh. von Spalte K

Spalte O Ergebnis

Spalten P, Q Hilfswerte

Share this post


Link to post
PopOff
Posted

Das wäre ein Ansatz (vielleicht geht es auch eleganter)

 

Balancieren_FesterMonat.xls

 

Zelle M3: in welchem Monat wird balanciert?

 

Spalte K zur Steuerung mit Bezug auf Spalte A und M3

Spalten L, M Wertentwicklung, Formel abh. von Spalte K

Spalte O Ergebnis

Spalten P, Q Hilfswerte

 

Hey,

 

danke für deine Antwort.

Die Excel Datei ist super :thumbsup:

Ich hätte dies alleine mit Sicherheit nicht hinbekommen.

Ich habe nun mal alle Daten seit 1989 eingetragen und festgestellt dass ein rebalancing im Monat Dezember

oder im Januar historisch am Besten war.

Kann es sein, das wenn ich in der Zelle R3 die Zahl 13 eingebe dann nicht rebalanciert wird?

Kann das stimmen?

Balancieren_FesterMonat1.xls

Share this post


Link to post
€-man
Posted

Ich habe nun mal alle Daten seit 1989 eingetragen und festgestellt dass ein rebalancing im Monat Dezember

oder im Januar historisch am Besten war.

 

Wo wird das noch hinführen - Saisonwetten in der "Passivspezerei".

 

Gruß

€-man

Share this post


Link to post
Schinzilord
Posted

 

Wo wird das noch hinführen - Saisonwetten in der "Passivspezerei".

 

Gruß

€-man

Argh, das führt uns geradewegs zum aktiven Investieren! Der Pfad des Teufels wurde beschritten! Wir sollten alle Backtester mit Schimpf- und Schande vom Hof jagen!

Share this post


Link to post
vanity
Posted

... Kann es sein, das wenn ich in der Zelle R3 die Zahl 13 eingebe dann nicht rebalanciert wird? Kann das stimmen?

Ja! (als Feature, nicht als Bug: alle Werte außerhalb des Intervalls 1..12 führen zum Nichtbalancieren. Das hängt damit zusammen, dass es im christlichen Kalender nur 12 Monate gibt. :P)

 

 

Argh, das führt uns geradewegs zum aktiven Investieren! Der Pfad des Teufels wurde beschritten! Wir sollten alle Backtester mit Schimpf- und Schande vom Hof jagen!

Die Backtester können bleiben. Sie werden ja keinen Schaden anrichten, da - wie wir gelernt haben - deren Ergebnisse ohnehin nicht auf die Zukunft übertragbar sind.

Share this post


Link to post
Schinzilord
Posted

Die Backtester können bleiben. Sie werden ja keinen Schaden anrichten, da - wie wir gelernt haben - deren Ergebnisse ohnehin nicht auf die Zukunft übertragbar sind.

Und nochdazu werden die dummen Lemminge grandenlos von den aktivistischen Investoren (aka Hedgefonds aka Heuschrecken) grandenlos durch Frontrunning abgezockt!

Und wenn beim Rebalancing auch noch die ETF Verkaufaufträge unlimitiert (siehe otto :P) eingestellt werden, haben die auch noch leichtes Spiel...die MM stecken da wohl auch mit drinnen!

Share this post


Link to post
vanity
Posted

Ich habe nun mal alle Daten seit 1989 eingetragen und festgestellt dass ein Rebalancing im Monat Dezember

oder im Januar historisch am Besten war.

Halt, halt, halt!

 

Bevor jetzt alle sich wie wild ans Balancieren machen: Im Sheet war noch ein kleiner falscher Fehler und der beste Monat ist der März.

 

post-13380-0-19763800-1386977682_thumb.png

 

und zwar unabhängig davon, ob PopOffs Gewichtung gewählt wird oder der Forenstandard (3x30+10)/2+50. Etwas mehr Signifikanz dürfte die Aussage haben, dass Rebalancieren im ersten Trimester bessere Ergebnisse liefert als in den Sommermonaten (in denen man ohnehin Besseres zu tun hat - Ansergucken z. B.). Egal, in welchem Monat balanciert wird, es ist immer besser als monatliches Anpassen und dieses wiederum besser als überhaupt nichts machen (zumindest vor Kosten und im betrachteten Zeitraum 1989/2013 und bei den gewählten Gewichtungen).

 

Balancieren_Weltdepot_FesterMonat.xls

 

Fehler beseitigt (nicht unbedingt alle, aber die bei bei Ausgangsgewichtung und fehlendes C&P) sowie REXP-Daten auf Vordermann gebracht.

Share this post


Link to post
€-man
Posted

Darf ich daraus schließen, dass wir uns (aktiv / passiv) immer mehr annähern? In Sachen Ansergucken waren wir schon immer im Konsens, jetzt kommt allmählich der Rest. Die etwas Zögerlichen sollten sich vielleicht - und ich schreibe bewusst vielleicht, weil es nicht so einfach ist - Gedanken darüber machen, ob man eine Maß Bier immer mit hundert Schlückchen austrinken muss.

 

Gruß

€-man

Share this post


Link to post
vanity
Posted

... ob man eine Maß Bier immer mit hundert Schlückchen austrinken muss.

Also, das verstehe ich jetzt nicht so richtig. Du meinst, die gansen Maßen alle im März auf einmal austrinken und für den Rest das Jahres dann im Koma oder abstinent? So kommen wir nicht zusammen. Vielleicht aber so:

 

Im März rebalancieren kann ja jeder, jetzt kommen die höheren Weihen: Das schinzilordsche situativ-bauchregelbasierte Rebalancieren. Nachdem wir andernorts schon festgestellt haben, dass der Einsatz von absoluten Triggerwerten bei Grokos zu bis zu halbstündlich Balancierungsaktivitäten ("schlückchenweise") führen kann, nehmen wir für diesen Zweck lieber prozentuale Grenzwerte. Und das geht so:

 

Übersteigt die Summe der absoluten (vorzeichenlosen) Veränderung von Istgewicht zu Sollgewicht in Prozentpunkten über die einzelnen Assetklassen einen vorgegebenen Wert, ist März. Sonst nicht.

 

Also ABS((B$4-(P8/X8)))+ABS((D$4-(Q8/X8)))+ABS((F$4-(R8/X8)))+ABS((H$4-(S8/X8)))+ABS((J$4-(T8/X8))) >= X ==> Austrinken!

 

Die Frage lautet nun, wie dieses X zu wählen ist, um das hyperoptimalste Ergebnis zu erzielen. Die Antwort ist verblüffend: Wir wissen es nicht und können es auch nicht experimentell ermitteln. Deduktiv schon überhaupt nicht - aber visualisieren:

 

 

post-13380-0-29931400-1387031068_thumb.png

 

(Standard-WPF-Portfolio 1989/2013, Outperformance in Prozent über den gesamten Zeitraum ggü. dem nicht balancierten Portfolio abhängig vom Eingriffswert X)

 

Ganz links, bei 0 Pp, finden wir den Wert für's monatliche Balancieren (4,7%)

 

Mit sukzessiver Erhöhung der Eingriffsgrenze auf ca. 10 Pp findet eine kontinuierliche Verbesserung statt, Richtung 13%

 

Nun kommt ein Bereich, in dem es völlig chaotisch zugeht, er reicht von etwa 10 Pp bis 40 Pp. Schon durch kleinste Veränderungen beim Grenzwert ergeben sich wilde Schwankungen - z. B. Grenze 33 Pp führt zu Steigerung um 17%, bei 34 Pp Ergebnis fast 0%, noch einen Pp weiter wieder 16%.

 

Ab 38 Pp sinkt der Wert abrupt ab, wird sogar einmal leicht negativ (-0,2% bei 40 Pp), um anschließend wieder leicht anzusteigen, ohne die vorherigen Höhen zu erreichen. Ab 53 Pp ist dann Schluss mit lustig, die Grenze wird nie erreicht.

 

Ganz rechts (grüner Kringel) ist der Wert für die stupide Märzregel eingezeichnet. Mit 18% liegt sie besser als fast alle anderen Werte. Das beste Ergebnis insgesamt wird bei 24,5 Pp mit fast einem viertel Verbesserung (24%, orange Kringel) erzielt.

 

Warum das so ist, wird uns Schinzi gleich verraten.

Share this post


Link to post
€-man
Posted

Da kommt doch Freude auf. Danke, vani!

 

Alle Maßen im März auszutrinken, war nicht gemeint - wenngleich Deine Berechnungen dazu schon verleiten könnten. Wäre da (bei mir zumindest) nur nicht die Angst vorm restjährlichen Delirium und der damit einhergehenden Einschränkungen beim sommerlichen Ansergucken.

 

Mein Fazit: A schöne süffige Märzenmaß, zügig getrunken, und dann die Freud' auf a Festmaß - wann immer das Fest auch läuft. Und in den börslich eher schwächeren Monaten warten Anserini auf Kennerbegutachtung.

 

Gruß

€-man

 

P.S. Mr. J...., bitte halten Sie Ausschau. Nicht nach den Anserini, das bleibt den Kennern vorbehalten, sondern nach den Festen.

Share this post


Link to post
vanity
Posted

Mr. J., der sonst auf alles dieselbe Antwort weiß, scheint kein Biertrinker zu sein. So jedenfalls interpretiere ich die ausstehende Antwort.

 

Einen hab' ich noch, dann gebe ich Ruhe. Nachdem die Prozentpunkte als Schwellenwert nicht den gewünschten Effekt gebracht haben, versuchen wir es stattdessen mit echten Prozenten, also mit

 

ABS((B$4/(P8/X8))-1)+ABS((D$4/(Q8/X8))-1)+ABS((F$4/(R8/X8))-1)+ABS((H$4/(S8/X8))-1)+ABS((J$4/(T8/X8))-1) > X etc.

 

was bei unserer 5-Komponenten-Allokation eine etwa 10-fach höhere Soll-Ist-Abweichung ergibt (und die kleineren Anteile stärker gewichtet). Das Ergebnis ist niederschmetternd:

 

post-13380-0-85387400-1387139823_thumb.png

 

Bei niedrigen Grenzwerten ist der Verlauf noch ansatzweise vergleichbar, das Chaos beginnt hier ab X=170. Ab X=230 bis X=350 liegt jetzt sogar ein recht breiter Bereich, in dem die Balancierungsbemühungen fast durchgängig zu einem schlechteren Ergebnis führen als die statische Allokation (-3%/-4%). Jenseits von X=470 findet keine Umschichtung mehr statt.

 

Wenn wir uns anhand eines Beispiels klarmachen, was das sprunghafte Verhalten auslöst, werden wir vermutlich erkennen, dass selbst ex post keine tragfähige Strategie zu ermitteln ist.

 

Beispiel Grenzwert 175 (+ 22,4%) vs. 176 (- 0,1%), blau markiert

In beiden Fällen wird 12/93 und 11/97 umgeschichtet. Durch den minimal höheren Grenzwert werden drei folgende Balancierungspunkte (04/03, 03/07 und 12/08) verpasst, was eine erhebliche Performanceeinbuße zur Folge hat.

 

Beispiel Grenzwert 168 (+ 5,6%) vs. 169 (+ 22,8%)

Hier findet im schlechteren Fall die erste Umschichtung bereits 5/92 statt, wodurch aber die Zeitpunkte 12/93 und 11/97 verpasst werden, stattdessen erst 02/98 wieder ein Eingriff erfolgt. Im besseren Fall wird dann noch zweimal balanciert (nämlich 04/03 und 01/07), bevor sich der letzte Punkt bei 03/09 resp. 12/08 bei beiden (mit tendenziell ähnlichem Erfolg) ergibt.

 

Wenn man zusätzlich bedenkt, dass hier nur eine einzige Basisallokation (und nur ein Zeitraum) ausgewertet wurde, dürfte es sich als schwieriges Unterfangen erweisen, mit einer Heuristik auf Basis von festen Grenzwerten ein außergewöhnliches Ergebnis mehr als nur zufällig zu erzielen.

Share this post


Link to post
Mr. Jones
Posted

Mr. J., der sonst auf alles dieselbe Antwort weiß, scheint kein Biertrinker zu sein. So jedenfalls interpretiere ich die ausstehende Antwort.

:rolleyes:

 

Mr. Jones ist zwar kein Excel-Akrobat, braucht er aber auch nicht zu sein, weil er anlassbezogen rebalanciert, wenn das Rebalancing-Band ausgeleiert ist.

 

PS: Ich kenne den Unterschied zwischen Radeberger und Katzenpisse.

Share this post


Link to post
€-man
Posted

Mr. Jones ist zwar kein Excel-Akrobat, braucht er aber auch nicht zu sein, weil er anlassbezogen rebalanciert, wenn das Rebalancing-Band ausgeleiert ist.

 

PS: Ich kenne den Unterschied zwischen Radeberger und Katzenpisse.

 

So wie es außer den zwei genannten Flüssigkeiten noch Erweiterungsmöglichkeiten gibt, kann ich mir gut vorstellen, dass es auch noch belastungsfähigere Bänder gibt.

 

Gruß

€-man

Share this post


Link to post
PopOff
Posted · Edited by PopOff

@vanity:Danke für deine Ausführlichen Analysen hierzu.

Diese haben mir sehr weitergeholfen.

Ausserdem kann ich nun mit deiner Excel Vorlage ein bisschen Experimentieren :rolleyes:

 

Grüße,

PopOff

Share this post


Link to post
PopOff
Posted · Edited by PopOff

Mir ist hierzu noch eine Frage eingefallen:

Bei der Excel Datei wird ja angenommen dass die Wiederherstellung der Asset Allocation durch Verkäufe erfolgt.

Wenn man aber mit frischem Geld ein rebalancing immer im März macht müsste dies ja Renditemindernd sein.

Theoretisch sollte man ja mit frischem Geld im schlechtesten Börsenmonat ein reb. durchführen, da man hier zu niedrigen Kursen nachkaufen kann.

Mache ich einen Denkfehler?:blink:

Share this post


Link to post
PopOff
Posted

Hallo,

 

ich habe die Daten seit 1969 in die Excel Tabelle eingetragen und versucht die Regionen nach BIP zu gewichten.

Laut der Excel Tabelle hätte man eine bessere Performance erzielt wenn man seit 1969 nicht rebalanciert hätte.

(Die rebalancierung habe ich immer im März vorgenommen)

 

Das kann so nicht stimmen, leider weiß ich nicht wo der Fehler liegt. Hoffe das jemand den Fehler findet ;-)

 

Grüße,

Popoff

 

 

WPF.xls

Share this post


Link to post

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

×
×
  • Create New...