Finanzfunktionen in Excel: Barwert, Rate, Zins und IKV

Wie viel ist eine Zahlung in fünf Jahren heute wert? Welche Rate hat ein Kredit, wie lange dauert die Tilgung, welche Rendite bringt eine Investition? Excel beantwortet solche Fragen mit eingebauten Finanzfunktionen. Wer zwei Regeln kennt, die Vorzeichen und die Perioden, rechnet damit sicher.

Die Kurzlösung

FrageFunktionBeispielErgebnis
Monatliche Rate eines KreditsRMZ=RMZ(3%/12;120;20000)-193,12 €
Endwert eines SparplansZW=ZW(4%/12;120;-100)14.724,98 €
Barwert einer späteren ZahlungBW=BW(5%;5;0;10000)-7.835,26 €
Laufzeit eines Kredits in MonatenZZR=ZZR(4%/12;-250;15000)67,05
Zinsanteil einer RateZINSZ=ZINSZ(5%/12;1;24;5000)-20,83 €
Tilgungsanteil einer RateKAPZ=KAPZ(5%/12;1;24;5000)-198,52 €
Kapitalwert einer InvestitionNBW=NBW(8%;B3:B6)+B24.586,47 €
Interner Zinsfuß (Rendite)IKV=IKV(B2:B6)24,89 %
Effektiver JahreszinsEFFEKTIV=EFFEKTIV(5%;12)5,12 %

Im Beispiel zu NBW und IKV stehen in B2:B6 die Zahlungen -10.000, 3.000, 4.000, 5.000 und 6.000 €.

Die zwei Regeln

Vorzeichen: Excel rechnet aus Sicht dessen, der die Zahlungen hat. Geld, das hinausgeht, ist negativ, Geld, das hereinkommt, positiv. Nimmt man einen Kredit von 20.000 € auf (positiv, das Geld kommt herein), ist die Rate negativ (sie geht hinaus). Deshalb liefert RMZ einen negativen Wert. Wer eine positive Rate sehen möchte, schreibt ein Minus davor: =-RMZ(3%/12;120;20000).

Perioden: Zins und Anzahl der Perioden müssen zueinander passen. Bei monatlichen Raten teilen Sie den Jahreszins durch 12 und rechnen die Laufzeit in Monaten. 3 % auf 10 Jahre mit Monatsraten heißt also 3%/12 und 120.

Kredit: Rate, Zins und Tilgung

=RMZ(Zins;Zzr;Bw;[Zw];[F])

Ein Kredit über 20.000 € mit 3 % Zins und 10 Jahren Laufzeit kostet =-RMZ(3%/12;120;20000) = 193,12 € im Monat. Über alle 120 Raten zahlen Sie rund 23.174,58 € zurück, also 3.174,58 € Zinsen. Die Zinsen liefert auch =-KUMZINSZ(3%/12;120;20000;1;120;0). Excel rechnet dabei mit der ungerundeten Rate. Mit 120 Raten zu genau 193,12 € ergeben sich 23.174,40 €.

Jede Rate besteht aus Zins und Tilgung. Für die erste Rate eines Kredits über 5.000 € mit 5 % und 24 Monaten:

  • Zinsanteil: =ZINSZ(5%/12;1;24;5000) = -20,83 €
  • Tilgungsanteil: =KAPZ(5%/12;1;24;5000) = -198,52 €
  • zusammen die Rate: =RMZ(5%/12;24;5000) = -219,36 €

Mit der Periode als zweitem Argument erhalten Sie die Werte für jede beliebige Rate. Einen vollständigen Tilgungsplan mit Sondertilgungen bietet unser Darlehensrechner.

Wie lange es dauert, 15.000 € mit 250 € im Monat bei 4 % zu tilgen, berechnet =ZZR(4%/12;-250;15000): 67,05 Monate, also gut fünfeinhalb Jahre. Den Zinssatz bei bekannter Rate liefert ZINS: =ZINS(120;-193,12;20000)*12 ergibt rund 3 %.

Sparen und abzinsen: ZW und BW

Wer 10 Jahre lang am Monatsende 100 € zu 4 % anlegt, hat am Ende =ZW(4%/12;120;-100) = 14.724,98 €. Wird jeweils am Monatsanfang eingezahlt, setzen Sie das letzte Argument auf 1: =ZW(4%/12;120;-100;0;1) = 14.774,06 €.

Umgekehrt zinst BW eine spätere Zahlung auf heute ab. 10.000 €, die Sie in 5 Jahren erhalten, sind bei 5 % heute =BW(5%;5;0;10000) = 7.835,26 € wert (Excel zeigt -7.835,26 €, weil man diesen Betrag heute anlegen müsste).

Investitionen: NBW und IKV

Eine Investition kostet heute 10.000 € und bringt in den folgenden vier Jahren 3.000, 4.000, 5.000 und 6.000 €.

Kapitalwert: NBW zinst alle Werte ab, und zwar schon den ersten um eine Periode. Die Anfangsinvestition gehört deshalb nicht in NBW, sondern wird danach addiert:

=NBW(8%;B3:B6)+B2

Das ergibt 4.586,47 €. Ein positiver Kapitalwert heißt: Die Investition verdient mehr als die geforderten 8 %. Wer die -10.000 in den Bereich von NBW aufnimmt, erhält 4.246,73 €, weil dann auch die Anfangsinvestition um ein Jahr abgezinst wird. Das ist ein häufiger Fehler.

Interner Zinsfuß: =IKV(B2:B6) liefert 24,89 %. Bei diesem Zinssatz ist der Kapitalwert genau null. Die Werte müssen in zeitlicher Reihenfolge stehen und mindestens eine negative und eine positive Zahlung enthalten.

Den Rentabilitätsindex (Kapitalwert der Rückflüsse geteilt durch die Investition) gibt es nicht als eigene Funktion: =NBW(8%;B3:B6)/-B2 ergibt 1,46. Werte über 1 bedeuten eine lohnende Investition.

Liegen die Zahlungen nicht in gleichen Abständen, verwenden Sie XKAPITALWERT und XINTZINSFUSS. Sie erhalten zusätzlich einen Bereich mit den Datumswerten.

Nominal- und Effektivzins

Ein Nominalzins von 5 % mit monatlicher Verzinsung entspricht einem Effektivzins von =EFFEKTIV(5%;12) = 5,12 %. Umgekehrt rechnet NOMINAL vom Effektiv- auf den Nominalzins zurück. Der effektive Jahreszins eines Kreditangebots enthält darüber hinaus Gebühren, die EFFEKTIV nicht kennt.

Typische Fehler

  • Vorzeichen: Haben Bw und Rmz dasselbe Vorzeichen, liefern ZZR und ZINS falsche Ergebnisse oder #ZAHL!. =ZZR(4%/12;250;15000) ergibt zum Beispiel -54,79 statt 67,05 Monate. Zahlungen hinaus und herein brauchen unterschiedliche Vorzeichen.
  • Jahreszins mit Monatsraten: =RMZ(3%;120;20000) rechnet mit 3 % pro Monat und liefert 617,80 € statt 193,12 €. Teilen Sie den Zins durch 12.
  • Anfangsinvestition in NBW: Siehe oben, sie wird nach NBW addiert.
  • Funktionsnamen: In der deutschen Excel-Version heißt die Funktion für den effektiven Zins EFFEKTIV, nicht EFFEKT. Ein falscher Name ergibt #NAME?.
  • Ergebnis als Zinssatz anzeigen: IKV, ZINS und EFFEKTIV liefern Dezimalzahlen wie 0,2489. Formatieren Sie die Zelle als Prozent mit zwei Nachkommastellen.

Passende Beiträge

Lieber machen lassen?

Berichte, die sich selbst aktualisieren.

Monatsberichte, Plan-Ist-Vergleiche und Liquiditätsplanung in Excel, mit geprüften Daten und Kennzahlen, die jeder nachvollziehen kann.