27.03.2018, 14:58
Hallo zusammen,
ich habe ein Excel in dem ich über meine Investitionen Buch führe. Jede Ivestition hat ein eigenes Blatt (z.B. jede P2P Plattform, jedes Depot, jede Immobilie, jeder Bausparvertrag, jede Kryptowährung, usw.).
Die Blätter sind immer identisch aufgebaut. Nach der Kopfzeile, in der Eckdaten der Investition zusammengefasst werden, beginnt der Verlauf in dem mit Datum jeder Cashflow erfasst wird. Negative Beträge sind Einzahlungen in die Investition und positive Beträge sind Auszahlungen. Die letzte Zeile (mit # in der A Spalte) gibt den Gegenwert der Investition zum angegeben Zeitpunkt wieder. Diese Erfassung dient der Berrechnung des XIRR (interner Zinsfuß) und wird in Zelle E3 angegeben. Also kurz gesagt die Rendite der Investition aufs Jahr gerechnet.
Jetzt würde ich gern die durchschnittliche Rendite einzelner Anlageklassen berechnen, also wie war z.B. die durchschn. Rendite aller P2P Investitionen oder über alle Depots.
Der XIRR benötigt dafür zwei Bereiche, einmal das jeweilige Datum und den dazugehörigen Cashflow nur eben über alle Anlagen hinweg. Ich sehe da keine andere Möglichkeit als das mit VBA zu lösen.
Leider wirds da bei mir etwas dünn was VBA betrifft, meistens finde ich etwas ähnliches dem was ich machen möchte im Netz und baue es um, das klappt oft ganz gut. Aber dieses System stößt hier an seine Grenzen.
Was in VBA passieren müsste (am Beispiel P2P):
- erstelle ein neues Tabellenblatt "temp. Rechenblatt_P2P" (der Schritt ist ggf. gar nicht nötig, da VBA die Daten ja evtl. gar nicht irgendwohin schreiben muss, sonder im Speicher halten kann)
- für alle Tabellenblätter zwischen Start_P2P und Ende_P2P kopiere B19:C#zeile_x# in das neue Tabellenblatt (#zeile_x# lässt sich finden durch die Zeile in Spalte A in der die # ist; die Zeile darüber ist Zeile_x)
- die kopierten Werte im neuen Tabellenblatt können einfach untereinander stehen, der XIRR braucht die Buchungen nicht in der korrekten Reihenfolge
- für alle Tabellenblätter zwischen Start_P2P und Ende_P2P bilde die Summe über D3 (da steht der aktuellste Wert der Anlage) --> =SUMME(Start_P2P:Ende_P2P!D3)
- füge der Liste auf dem neuen Tabellenblatt ans Ende von B das aktuelle Datum und rechts daneben in C die im vorherigen Schritt ermittelte Summe
- berechnte mit der Formel =XINTZINSFUSS(C1:C9999;B1:B9999;0,1) die Rendite (der Bereich muss nur alle Zahlen abdecken, kann aber beliebig groß sein)
- übertrage die Rendite und lösche das temporäre Blatt wieder
Damit das ganze besser Vorstellbar ist, habe ich im Anhang mal das Excel, gefüllt mit Dummy-Werten. Sofern ein temporäres Blatt erstellt werden muss für die Berechnung, sollte es natürlich nicht wie in meiner Beispieldatei zwischen Start_P2P und Ende_P2P eingefügt werden, da es sonst ja bei der Datenerhebung mit berücksichtig werden würde.
Also mir würden auch schon Codeschnipsel reichen wie z.B. wie adressiere ich alle Tabellenblätter zwischen 2 Blättern usw.
Schöne Grüße
Mark
ich habe ein Excel in dem ich über meine Investitionen Buch führe. Jede Ivestition hat ein eigenes Blatt (z.B. jede P2P Plattform, jedes Depot, jede Immobilie, jeder Bausparvertrag, jede Kryptowährung, usw.).
Die Blätter sind immer identisch aufgebaut. Nach der Kopfzeile, in der Eckdaten der Investition zusammengefasst werden, beginnt der Verlauf in dem mit Datum jeder Cashflow erfasst wird. Negative Beträge sind Einzahlungen in die Investition und positive Beträge sind Auszahlungen. Die letzte Zeile (mit # in der A Spalte) gibt den Gegenwert der Investition zum angegeben Zeitpunkt wieder. Diese Erfassung dient der Berrechnung des XIRR (interner Zinsfuß) und wird in Zelle E3 angegeben. Also kurz gesagt die Rendite der Investition aufs Jahr gerechnet.
Jetzt würde ich gern die durchschnittliche Rendite einzelner Anlageklassen berechnen, also wie war z.B. die durchschn. Rendite aller P2P Investitionen oder über alle Depots.
Der XIRR benötigt dafür zwei Bereiche, einmal das jeweilige Datum und den dazugehörigen Cashflow nur eben über alle Anlagen hinweg. Ich sehe da keine andere Möglichkeit als das mit VBA zu lösen.
Leider wirds da bei mir etwas dünn was VBA betrifft, meistens finde ich etwas ähnliches dem was ich machen möchte im Netz und baue es um, das klappt oft ganz gut. Aber dieses System stößt hier an seine Grenzen.
Was in VBA passieren müsste (am Beispiel P2P):
- erstelle ein neues Tabellenblatt "temp. Rechenblatt_P2P" (der Schritt ist ggf. gar nicht nötig, da VBA die Daten ja evtl. gar nicht irgendwohin schreiben muss, sonder im Speicher halten kann)
- für alle Tabellenblätter zwischen Start_P2P und Ende_P2P kopiere B19:C#zeile_x# in das neue Tabellenblatt (#zeile_x# lässt sich finden durch die Zeile in Spalte A in der die # ist; die Zeile darüber ist Zeile_x)
- die kopierten Werte im neuen Tabellenblatt können einfach untereinander stehen, der XIRR braucht die Buchungen nicht in der korrekten Reihenfolge
- für alle Tabellenblätter zwischen Start_P2P und Ende_P2P bilde die Summe über D3 (da steht der aktuellste Wert der Anlage) --> =SUMME(Start_P2P:Ende_P2P!D3)
- füge der Liste auf dem neuen Tabellenblatt ans Ende von B das aktuelle Datum und rechts daneben in C die im vorherigen Schritt ermittelte Summe
- berechnte mit der Formel =XINTZINSFUSS(C1:C9999;B1:B9999;0,1) die Rendite (der Bereich muss nur alle Zahlen abdecken, kann aber beliebig groß sein)
- übertrage die Rendite und lösche das temporäre Blatt wieder
Damit das ganze besser Vorstellbar ist, habe ich im Anhang mal das Excel, gefüllt mit Dummy-Werten. Sofern ein temporäres Blatt erstellt werden muss für die Berechnung, sollte es natürlich nicht wie in meiner Beispieldatei zwischen Start_P2P und Ende_P2P eingefügt werden, da es sonst ja bei der Datenerhebung mit berücksichtig werden würde.
Also mir würden auch schon Codeschnipsel reichen wie z.B. wie adressiere ich alle Tabellenblätter zwischen 2 Blättern usw.
Schöne Grüße
Mark
