Wie das DIY Portfolio Analyse Tool funktioniert

Ich nutze für die Berechnung meiner Portfolio-Rendite sowie für die Beobachtung meines Portfolios und das Nachverfolgen meiner Transaktionen ein simples Portfolio Analyse Tool, welches ich mir selbst in Excel bzw. Google Sheets zusammengebaut habe. Im Internet gibt es noch ein paar weitere Tools, die ähnlich funktionieren. Bisher habe ich allerdings bei keinem dieser Tools eine Funktionalität entdeckt, die die Berechnung einer jährlichen Rendite bzw. einer zeitgewichteten Rendite für mein Portfolio ermöglicht. Im folgenden Artikel möchte ich die Funktionsweise des Tools einmal im Detail vorstellen.

Du kannst das DIY Portfolio Analyse Tool hier kostenlos herunterladen.

Hier gibts eine detaillierte Version der Bedienungsanleitung.

Das Portfolio Analyse Tool besteht zunächst mal aus 4 wesentlichen Tabellenblättern:

• der Watchlist
• der Transaktionsübersicht (inkl. Renditeberechnung des Gesamtportfolios)
• der Portfolioanalyse (Renditeberechnung der Einzelwerte)
• dem Benchmarkvergleich

Es berechnet die Rendite für das Portfolio sowie die Einzelwerte unter Berücksichtigung aller Return-Komponenten, also realisierte und nicht realisierte Gewinne und Dividenden (siehe Abbildung).

Die Transaktionsübersicht und Portfolio-Return

Dieses Tabellenblatt beinhaltet alle Transaktionen, d.h. Aktienkäufe, -verkäufe sowie Dividendenzahlungen. Hier werden zunächst einmal die für jede Transaktion relevanten Daten eingetragen. Wenn du nicht gerade ein Daytrader bist, dann sollte die Anzahl an Einträgen und auch der Zeitaufwand recht überschaubar sein. Auf diesem Tabellenblatt wird außerdem die jährliche Rendite des Gesamtportfolios berechnet.

Transaktionsdaten

Die wesentlichen Informationen je Transaktion, die vom Nutzer eingegeben werden müssen, befinden sich in den Spalten A-N und beinhalten

• Börse und Ticker: In Google Finance Nomenklatur, sodass wir die historischen und aktuellen Kurse direkt von Google Finance in unser Tool ziehen können
• Name des Unternehmens
• Branche
• Datum der Transaktion: Für Google Tabellen im US-amerikanischen Format erforderlich
• den Transaktionstyp: Handelt es sich um einen Kauf, einen Verkauf oder eine Dividendenzahlung
• die Art der Transaktion: Z.B. extern, wenn du dem Portfolio Geld entnimmst (d.h. nach einem Aktienverkauf den Erlös nicht reinvestierst) oder hinzufügst (z.B. indem du mit deinem Ersparten weitere Aktien kaufst)
• Aktiensplit: Wenn ein Aktiensplit stattfindet die Info über das Verhältnis, damit die Kurse und Stückzahlen der Vergangenheit richtig korrigiert werden können
• Währung
• Stückzahl
• Kauf- bzw. Verkaufspreis bzw. Dividende je Aktie
• Gezahlte Entgelte je Transaktion

Dann kommen in den Spalten O-V ein paar weitere berechnete Felder, die wir für die Portfolioanalyse und die Aggregation der Transaktionen auf die Portfoliowerte benötigen.

• Stückzahl und Kaufpreis nach Aktiensplit: Korrigierte Stückzahl und korrigierter Preis, damit die Zahlen aus der Vergangenheit zu den Echtzeitinformationen von Google Finance passen, falls es einmal einen Aktiensplit gegeben hat
• Gesamter Kauf- bzw. Verkaufswert: Stückzahl mit Preis/Aktie ausmultipliziert
• Anzahl Aktien bereits im Portfolio sowie Änderung der Stückzahl: Relevant bei Zukäufen, Verkäufen und Dividendenzahlungen
• Tage seit Transaktion: Anzahl an Tagen (vom heutigen bzw. aktuellen Datum ausgehend), die seit der Transaktion vergangen sind. Dies ist wichtig für die Ermittlung der durchschnittlichen Haltedauer und somit für die Berechnung des Returns p.a.
• Tage zwischen Transaktionen: Anzahl an Tagen zwischen zwei Transaktionen
• Hilfsspalte Tage x Stückzahl: Wird benötigt, um für die Portfolioanalyse die durchschnittliche Haltedauer je Aktie zu ermitteln

Berechnung des Portfolio-Returns

Schlussendlich enthalten die Spalten W-AA die Berechnung des jährlichen Portfolio-Returns, also der Zahl, die wir am Ende für unsere Vergleiche, z.B. mit externen Benchmarks und Alternativanlagen nutzen können.

Die Rendite-Berechnung wird für das Gesamtportfolio zeitgewichtet berechnet (TWRR – Time Weighted Rate of Return). Die zeitgewichtete Renditekalkulation berechnet die Rendite für jeden Zeitraum zwischen 2 Transaktionen. Dies geschieht anhand des zu Beginn des jeweiligen Zeitraums investierten Kapitals. Anschließend wird die Rendite der jeweiligen Einzelzeiträume zu einer Gesamtrendite aggregiert. Deshalb müssen wir für das Jahresende, also den 31.12., noch jeweils eine fiktive Transaktion einfügen, damit wir die Rendite für das Gesamtjahr ausrechnen können (sei sei denn, du hast an Silvester tatsächlich noch gehandelt).

Hier siehst du ein paar Screenshots aus unserem “Benutzerhandbuch” für das Portfolio Tool. Du kannst die zugehörige Präsentation auch komplett herunterladen.

Um den Portfolio-Return richtig berechnen zu können, benötigen wir für den Zeitpunkt jeder Transaktion den jeweiligen Portfoliowert. Das heißt im Klartext: Für jedes Datum, an dem eine Transaktion in unserem Portfolio stattgefunden hat, benötigen wir die jeweiligen Aktienkurse der Werte in unserem Depot!

Im Portfolio Analyse Tool nutzen wir dafür das Tabellenblatt “Matrix”. Hier werden für alle Depotwerte und Zeitpunkte die jeweiligen Kurse von Google Finance gezogen, die dann für die Berechnung des Portfoliowertes in der Transaktionsübersicht genutzt werden. Weil die Verbindung zu Google Finance aber schonmal etwas instabil ist, haben wir die historischen Formeln mit den Werten der Aktienkurse überschrieben.

Eine neue Transaktion hinzufügen

Für jede neue Transaktion muss dementsprechend eine neue Zeile sowie eine neue Spalte in der Matrix hinzugefügt werden. In der Hilfsspalte G der Transaktionsübersicht müssen wir festlegen, in welcher Spalte der Matrix die Kurse für das jeweilige Datum zu finden sind.

Die Portfolioanalyse

Auf dem Tabellenblatt Portfolioanalyse sehen wir eine Übersicht über alle Aktien, die wir derzeit im Portfolio haben bzw. einmal im Portfolio hatten. Für jede Aktie wird auf Basis der Transaktionen und des jeweiligen Kauf- und Verkaufsdatums eine durchschnittliche Haltedauer ermittelt.

Das Tabellenblatt ist grob in 5 Abschnitte eingeteilt:

• Spalten A bis G: Allgemeine Informationen
  • Börse und Ticker: In Google Finance Nomenklatur, sodass wir die historischen und aktuellen Kurse direkt von Google Finance in unser Tool ziehen können
  • Name des Unternehmens
  • Branche
  • Kategorie der Aktie: Dividende, Value, Wachstum, Zyklisch, Spezielle Situation
  • Unsere Strategie: Buy-and-Hold (Long-term Hold), kurzfristig halten (Short-term Hold), Beobachten (Watch)
  • Währung: Standardmäßig EUR
• Spalten H bis K: Infos zum derzeitigen Portfoliowert
  • Anzahl an Aktien, die sich derzeit von diesem Unternehmen in unserem Depot befinden
  • Anzahl an Tagen, die wir die Aktien dieses Unternehmens durchschnittlich im Portfolio haben
  • Aktueller Aktienkurs (wird automatisch über den Link zu Google Finance aktualisiert)
  • Aktueller Wert der Position in unserem Portfolio
• Spalten L bis P: Infos zum Kaufwert
  • Gesamte Anzahl Aktien, die wir jemals von diesem Unternehmen im Depot hatten (inkl. der Aktien, die wir bereits verkauft haben)
  • Anzahl an Tagen, die sich die Aktien durchschnittlich in unserem Depot befinden/befanden. Dies ist die durchschnittliche Haltedauer, die sich ergibt, wenn wir annehmen, dass wir alle Aktien noch im Depot haben
  • Durchschnittlicher Kaufpreis der Aktien (inkl. derjenigen, die wir nicht mehr im Depot haben)
  • Durchschnittlicher Kaufwert der Position (inkl. der bereits verkauften Anteile)
  • Performance = (Aktueller Preis / Durchschnittl. Kaufpreis) -1
• Spalten Q bis T: Infos zu den realisierten Gewinnen bzw. Verlusten
  • Anzahl Aktien, die wir von diesem Unternehmen bereits verkauft haben
  • Durchschnittliche Anzahl an Tagen seit Verkauf
  • Gesamter Verkaufswert der verkauften Aktien
  • Durchschnittlicher Verkaufspreis
• Spalten U bis AA: Renditekalkulation
  • Realisierter Gewinn / Verlust in EUR
  • Nicht realisierter Gewinn / Verlust in EUR
  • Gesamte bisher ausgeschüttete Dividenden in EUR
  • Gesamte Transaktionskosten bzw. beim Kauf angefallene Entgelte in EUR
  • Gesamtgewinn / -verlust in EUR, Summe der Spalten U, V, W, X
  • Bisherige Gesamtrendite = Gesamtgewinn / Kaufpreis
  • Gesamtrendite pro Jahr, berechnet basierend auf der durchschnittlichen Haltedauer der Aktie

Im Portfolio Tool werden 3 verschiedene Definitionen von Haltedauern verwendet, die zum Kaufwert, zu den realisierten Gewinnen und zum aktuellen Portfoliowert korrespondieren.

Zunächst mal definieren wir die gewichtete Haltedauer aller Aktien (in Spalte M des Tabellenblattes). Diese definiert die Haltedauer unseres Portfolios so, als hätten wir nie eine Aktie verkauft. Das heißt für jede Aktie wird die theoretische Haltedauer zwischen heute und dem Tag des Kaufes zu Grunde gelegt. Die verschiedenen Haltedauern werden dann jeweils mit der Anzahl an Aktien gewichtet.

Für die bereits realisierten Gewinne bzw. Verluste definieren wir die Anzahl Tage ex-Depot (Spalte R). Das bedeutet nichts anderes, als dass wir für alle Aktien, die wir bereits verkauft haben, die Anzahl an Tagen seit Verkaufsdatum berechnen und wiederum mit der Anzahl an (verkauften) Aktien gewichten.

Die durchschnittliche Haltedauer des derzeitigen Portfolios (Spalte I des Tabellenblattes) ergibt sich dann analog zur folgenden Abbildung aus der Haltedauer des Gesamtportfolios abzüglich der Anzahl an Tagen ex-Depot für die bereits verkauften Werte.

Für die Renditekalkulation werden zunächst alle Rendite-Bestandteile, das heißt realisierte und nicht realisierte Gewinne/Verluste, Dividenden und Entgelte (negativer Einfluss auf unsere Rendite) aufsummiert. Dieser Gesamtgewinn bzw. -verlust wird dann für jede Aktie durch den jeweiligen Gesamtkaufpreis geteilt. Damit erhalten wir zunächst die Gesamtrendite unabhängig vom Zeitpunkt des Kaufs der Position(en).

Um das Ganze noch als Rendite pro Jahr darzustellen, gewichten wir die Rendite noch mit der durchschnittlichen Haltedauer nach folgender Formel:

Rendite pro Jahr = ((Gesamtgewinn + Kaufpreis) / Kaufpreis)^{(1/(Haltedauer /365))} – 1

Wenn wir eine Position also genau vor einem Jahr, also vor 365 Tagen gekauft haben, dann ergibt sich genau die Portfoliorendite ohne Berücksichtigung der Haltedauer. Je länger wir die Position bereits haben, desto geringer wird unser Return pro Jahr im Vergleich zu Gesamtrendite.

Der Benchmarkvergleich

Mit dem Benchmarkvergleich können wir unseren Portfolio-Return mit einem Benchmark, z.B. dem DAX, vergleichen. Das Tabellenblatt ist recht einfach aufgebaut. Es zieht sich unsere Portfoliodaten vom Tabellenblatt “Transaktionsübersicht” und den jeweiligen Stand des Benchmarks zum Jahresende direkt von Goolge Finance.

Du kannst das DIY Portfolio Analyse Tool hier kostenlos herunterladen.

Um das Tool für dich zu nutzen, musst du zunächst folgende Schritte befolgen:

1. Logge dich in deinen Google Drive bzw. Google Sheets Account ein. Falls du bereits einen Account bei Google hast, dann kannst du die gleichen Login Daten nutzen.
2. Öffne das Portfolio Analyse Tool mit obigem Link.
3. Gehe auf File > Make a Copy
4. Gib dem Tool einen anderen Namen und klicke auf OK, um eine Kopie des Tools in deinem Account zu erstellen