Excel als Finanzrechner: Wie wir Portfoliowerte auf Basis von Annuitäten ermitteln

Excel, Google Sheets und andere Tabellenkalkulationsprogramme sind sozusagen die besten Finanztaschenrechner, die es gibt. Zwar können wir mit einigen etwas spezielleren Taschenrechnern auch Barwerte mit Annuitäten etc. berechnen (z.B. mit dem Texas Instruments BA II Plus – diesen habe ich damals auch für die CFA-Examen benutzt). An Microsoft Excel kommen diese Rechner aber natürlich am Ende nicht heran.

Uns als Value Investoren interessiert im Rahmen der Unternehmensbewertung ja oft die Fragestellung, wie viel eine Zeitreihe zukünftiger Cash Flows heute wert ist

Außerdem interessiert uns z.B., wie viel Geld wir nach einer bestimmten Anzahl an Jahren angespart haben, wenn wir regelmäßig einen bestimmten Betrag anlegen und dieser zu einem bestimmten Zinssatz verzinst wird (oder wir einen bestimmten Return darauf erwirtschaften). Oder aber, wie viele Jahre wir einen Kredit noch abzahlen müssen, wenn wir Schuldenstand, Rate und Zinssatz kennen.

Wie ihr seht, gibt es viele konkrete Anwendungsfälle für Endwert- oder Barwertberechnungen mit so genannten Annuitäten (also regelmäßigen Zahlungen über Zeit).

In diesem Artikel möchte ich einmal kurz erklären, wie wir diese Fragestellungen mit Hilfe der verschiedenen finanzmathematischen Funktionen in Excel lösen können und worauf wir dabei achten sollten.

Was du in diesem Artikel lernst

• Warum wir die finanzmathematischen Funktionen von Excel benötigen
• Wie die wesentlichen finanzmathematischen Funktionen in Excel heißen und wie sie aufgebaut sind
• Welche Funktionen wir für die Berechnung von Barwerten und Endwerten etc. auf Basis von Annuitäten nutzen und wie wir die Ergebnisse kontrollieren können

Warum das Ganze? Zinseszinseffekt und Annuitäten

Im Grunde genommen geht es bei allen oben genannten Fragestellungen um den so genannten Zinseszinseffekt.

Die grundsätzliche Überlegung hinter dem Zinseszinseffekt ist, dass eine Zahlung, die wir heute erhalten, mehr wert ist, als der gleiche Betrag in 5 Jahren. Und umgekehrt.

Um Missverständnissen vorzubeugen: Es geht hier nicht um die Inflation, sondern um die Verzinsung unseres Kapitals (die aber natürlich die Inflation mit abdeckt).

Wenn wir z.B. heute 10.000 EUR erhalten und diese zu 3% über 5 Jahre anlegen können, dann haben wir nach 5 Jahren

Ausgangswert * (1 + erwarteter Return)^{Anzahl Jahre} = 10.000 * (1,03)⁵ = 11.593 EUR.

10.000 EUR heute sind für uns also identisch zu 11.593 EUR in fünf Jahren. Sie haben für uns den gleichen Wert. Umgekehrt funktioniert das natürlich genauso.

Diese einfache Rechnung – und noch weitaus kompliziertere – können wir mithilfe eines Tabellenkalkulationsprogramms wie Microsoft Excel recht einfach durchführen.

5 verschiedene Fragestellungen

Insgesamt können wir in diesem Zusammenhang fünf verschiedene Fragestellungen unterscheiden:

1. Endwertberechnung: Wir investieren einmalig oder regelmäßig (zum Beispiel monatlich) einen bestimmten Betrag, auf den wir einen bestimmten Return erwirtschaften und möchten wissen, wie viel Geld wir nach einer bestimmten Zeit angespart haben
2. Barwertberechnung: Wir möchten wissen, wie viel ein Cash Flow oder eine Reihe von Cash Flows (Annuitäten), die wir in Zukunft erwarten, heute wert sind
3. Berechnung einer Annuität: Wir möchten in einer bestimmten Zeit einen festgelegten Betrag ansparen und möchten wissen, wie viel wir monatlich investieren müssten, um bei unserem erwarteten Return unser Ziel zu erreichen
4. Zeitdauerberechnung: Wir möchten wissen, nach wie vielen Jahren wir einen bestimmten Portfoliowert erreicht haben – wenn wir einen bestimmten Betrag zu einem gewissen Zinssatz bzw. Return investieren
5. Berechnung eines Zinssatzes bzw. Returns: Wir möchten wissen, welchen Return wir mit unseren Investments erwirtschaften müssen, um bei einer festgelegten Sparrate und einem festgelegten Zeithorizont auf einen bestimmten Portfoliowert zu kommen

Speziell auf die Fragestellungen mit Annuitäten möchte ich im Folgenden einmal kurz eingehen und erläutern, wie wir diese Fragestellungen mithilfe einiger Standardformeln bzw. -funktionen in Excel beantworten können.

Bevor ich damit aber beginne, möchte ich zunächst einmal eine kleine Übersicht der wesentlichen Funktionen und ihrer Argumente (der Variablen bzw. Inputs für die Funktionen) geben.

Die wesentlichen finanzmathematischen Funktionen in Excel

Im Wesentlichen reichen eine Handvoll Excel-Funktionen aus, um den Großteil unserer Return-Probleme zu lösen. Die Funktionen sind sehr ähnlich zu den Knöpfen, die wir auf einem typischen Finanztaschenrechner finden würden:

In eckigen Klammern dargestellt sehen wir die Argumente, die für die jeweilige Funktion optional sind, also nicht zwangsläufig ausgefüllt werden müssen.

Wir müssen uns die Argumente der Funktionen übrigens nicht merken. In Excel gibt es eine Dialogbox, bzw. ein Kommentarfeld, das sich öffnet, sobald wir den ersten Teil der Funktion (bis zur ersten runden Klammer) in eine Zelle eingeben.

Hier eine kurze Erklärung der Argumente der einzelnen Funktionen:

• FV bzw. Zw: Endwert bzw. zukünftiger Wert (Wert am Ende des betrachteten Zeitraums)
• PV bzw. Bw: Barwert bzw. aktueller Wert (Wert heute)
• Rate bzw. Zins: Zinssatz pro Periode (Zahlungszeitraum)
• Nper bzw. Zzr: Zahlungszeitraum bzw. Anzahl an betrachteten Perioden
• PMT bzw. Rmz: Höhe der regelmäßigen Zahlung bzw. Annuität (d.h. dieser Wert ist über den Zeitraum konstant). Wenn Rmz nicht verwendet wird, müssen wir das Argument Zw angeben
• Type bzw. F: Fälligkeit der Zahlung entweder am Ende der Periode (Standard, Wert = 0 bzw. leer lassen) oder am Anfang (Wert = 1)

Je nachdem, nach welcher Variable wir die Gleichung auflösen möchten bzw. was wir berechnen möchten, können wir uns die passende Funktion aussuchen.

Was wir zusätzlich beachten müssen

Obwohl die Formeln grundsätzlich ziemlich leicht verständlich sind, gibt es ein paar Dinge, die wir bei der Eingabe der Daten beachten müssen. Hier die wesentlichen Aspekte:

• Die ersten 5 Funktionen folgen i.W. der gleichen Logik. Insgesamt haben wir 5 Variablen, von denen jeweils 3 bis 4 vorgegeben sind. Die fünfte möchten wir ausrechnen. Wenn wir ein Problem haben, in dem einzelne Argumente bzw. Variablen nicht vorkommen (z.B. wenn wir nur ein anfängliches Investment tätigen, dann aber nicht monatlich noch einen zusätzlichen Betrag ansparen), dann sollten wir diese in der Formel einfach gleich Null setzen. Die optionalen Argumente können wir einfach ganz weglassen, wenn wir keinen Bedarf dafür haben
• Wir nehmen standardmäßig immer an, dass Cash Flows bzw. Annuitäten im Speziellen, am Ende der jeweiligen Periode auftreten. In diesem Fall brauchen wir für das Argument Type bzw. F nichts angeben
• Cash Flows bzw. Annuitäten, Zinssätze, Anzahl Perioden müssen zueinander korrespondieren. Nutzen wir monatliche Zahlungen, dann müssen wir den Zinssatz sowie auch die Anzahl an Perioden ebenfalls auf Monatsbasis angeben
• Zinssätze werden in die Excel-Formeln als Prozentwerte eingegeben (also z.B. als “10%” oder “0,1”)
• Wir müssen zwischen Barmittelzuflüssen und -abflüssen unterscheiden. Barmittelzuflüsse bekommen ein positives Vorzeichen, Barmittelabflüsse ein negatives Vorzeichen. Wenn wir ein Investment tätigen, dann ist das ein Barmittelabfluss

Weiterer Tipp: Die Argumente der Funktionen sollten wir nie in die Formel selbst eingeben, sondern immer als Referenz auf eine separate Zelle. Auf diese Weise können wir im Nachhinein Inputs einfach ändern (z.B. aus einem Return von 5% einen Return von 10% machen) und sehen direkt das Ergebnis.

Beispiel Endwert: Welchen Wert hat unser Portfolio in 20 Jahren?

Gehen wir mal davon aus, dass wir bereits etwas Geld angespart haben – genauer gesagt 50.000 EUR – und weiterhin regelmäßig etwas ansparen möchten. Bzgl. der erwirtschafteten Rendite und der regelmäßigen Rücklage haben wir mehrere Optionen. Z.B. können wir über eine Immobilieninvestition 5% Return erwirtschaften, über einen ETF ca. 9%, mit einem aktiven Value Investing Ansatz vermutlich noch mehr.

Berechnung mit der Excel-Funktion FV

In meinem Artikel zum Erreichen der finanziellen Freiheit hatte ich ja ein ähnliches Beispiel schonmal verwendet und die folgende Tabelle erstellt:

Die Tabelle zeigt den Wert, den unser Portfolio nach 20 Jahren haben würde, wenn wir regelmäßig einen bestimmten Betrag zurücklegen (zwischen 100 und 3.000 EUR pro Monat, siehe linke Spalte) und auf unser Vermögen einen bestimmten Return erwirtschaften (zwischen 5% und 15%, siehe obere Zeile).

Wir berechnen hier also den Endwert und nutzen dafür analog zur obigen Tabelle die folgende Funktion bzw. Formel:

=FV(rate,nper,pmt,[pv],[type])

Nehmen wir z.B. einmal eine Rücklage von 100 EUR/Monat und einen Return von 15%, dann sieht die Formel (inkl. der Argumente) in Excel folgendermaßen aus:

=FV(15%,20,1200,50000,)

Als Ergebnis erhalten wir 941.259 EUR (siehe rot umrandete Zelle in obiger Tabelle).

Wenn wir 50.000 EUR zur Verfügung haben sowie 1.200 EUR pro Jahr (100 EUR pro Monat) zusätzlich investieren (Annuität) und auf alles einen Return von 15% erzielen, dann erhalten wir nach 20 Jahren also einen Betrag von 941.259 EUR.

Nachvollziehen mit unserer eigener Berechnung

Wie funktioniert nun diese Formel? Und wie können wir das Ergebnis logisch nachvollziehen? Intuitiv ist es erstmal schwierig, das zu interpretieren. Mithilfe einer kleinen Nebenrechnung wird die Logik aber schnell klar.

In der folgenden Tabelle habe ich einmal für jedes Jahr einzeln aufgelistet, was eigentlich mit unserem Geld passiert. So ähnlich ist z.B. auch der Tilgungsplan für unsere Baufinanzierung aufgebaut, den wir typischerweise von unserer Bank erhalten. Nur dass wir im Tilgungsplan anstelle der Sparrate die Tilgung und anstelle des Returns die Schuldzinsen sehen.

Im Anfang des ersten Jahres (bzw. am Ende von Jahr 0) haben wir genau 50.000 EUR zur Verfügung.

Auf diese 50.000 EUR erhalten wir dann im ersten Jahr Zinsen in Höhe von 7.500 EUR (15% von 50.000). Am Jahresende wird dann unsere Sparleistung bzw. Annuität (die 1.200 EUR) fällig, sodass wir am Ende des ersten Jahres einen Vermögensstand von 58.700 EUR haben.

Im zweiten Jahr verzinsen sich dann diese 58.700 EUR dann zunächst wieder mit 15% (8.805 EUR). Anschließend kommen am Jahresende wieder 1.200 EUR Rücklage on top. Und so weiter.

Hier zum Nachvollziehen noch ein weiteres Beispiel. Diesmal für eine andere Kombination aus Rücklage bzw. Annuität (1.000 EUR pro Monat) und Return (15%):

Wie ihr seht, ergeben sich in beiden Beispielen wieder der gleiche Wert wie in der Tabelle, die wir mit der Endwert-Funktion von Excel berechnet hatten.

Zusammenfassung

Excel oder auch andere Tabellenkalkulationsprogramme besitzen eine Reihe an finanzmathematischen Funktionen, mit deren Hilfe wir auch recht komplexe Fragestellungen, die mit dem Zeitwert des Geldes zu tun haben, beantworten können.

In Excel sind es im Wesentlichen 5 einfache Funktionen, mit denen wir einen Großteil dieser Fragestellungen beantworten können:

• Endwertberechnung
• Barwertberechnung
• Berechnung einer Annuität
• Zeitdauerberechnung
• Berechnung eines Zinssatzes bzw. Returns

Im Grunde genommen ermöglichen es uns die Excel-Funktionen, einen gesamten Tilgungsplan bzw. Sparplan inkl. Annuitäten in einer einzigen Zelle unterzubringen.

Am Beispiel der Berechnung eines zukünftigen Portfoliowertes auf Basis eines Anfangsvermögens und regelmäßiger Investitionen können wir die Funktionsweise der Excel-Funktion sehr gut mit einer entsprechenden eigenen Berechnung nachvollziehen und ggf. weiter ins Detail gehen.