Microsoft Office

Excel-Kalkulation: Objekt, Monatszahlen, Zinsen & Tilgung

© connected-home

Hauskauf oder mieten?

Konkretes Objekt

Sie interessieren sich für eine Immobilie (zum Beispiel Kaufpreis inkl. Nebenkosten 380.000 Euro). 30.000 Euro sollen vom Sparkonto beigesteuert werden. Entsprechend wird ein Kredit in Höhe von 350.000 Euro benötigt. 

Zunächst sollen die jährlichen Belastungen eines Annuitätendarlehens (Tilgungsdarlehen mit gleich hohen Rückzahlungsbeträgen) grob überschlagen werden. Damit Sie mit den Zahlen besser jonglieren können, wird in dieser Rechnung mit Zellbezügen gearbeitet. 

Folgende Eckdaten werden in einer leeren Tabelle erfasst:
1,5 % Zinsen p. a. (Zelle B5), zehnjährige Laufzeit (B6), Restschuld Zw - 250.000 Euro (B4), Kredithöhe (B3). 

Hier wird mit RMZ(Zins;Zzr;Bw;[Zw];[F])), ebenfalls zu finden unter Formeln/Funktionsbibliothek/Finanzmathematik, gearbeitet.

F (Fälligkeit) unterscheidet Zahlungen am Ende (F=0) oder Anfang des Betrachtungszeitraums (F=1). Für das Beispiel ist die Zahlung vom Kreditnehmer jeweils am Jahresende zu leisten.

Die Formel =RMZ(B5;B6;B3;B4;0) weist ein Ergebnis pro Jahr von 14.593,42 Euro (Zinsen inkl. Tilgung) aus. Das würde einer monatlichen Miete von rund 1.200 Euro entsprechen.

© Screenshot & Montage: PC Magazin

Identische Belastung: Bei Annuitätendarlehen sinkt der Zinsanteil im Laufe der Zeit, der Tilgungsanteil steigt.

Monatszahlen

Im Rahmen von Überschlagsrechnungen reichen jährliche Betrachtungen aus. Wird die Sache konkreter, wird in der Regel auf Monatsbasis gerechnet. Dazu muss der Zinssatz (Zins) durch 12 dividiert, der Kreditzeitraum (ZZr) mit 12 multipliziert werden. Die Eingabe für Zins lautet B5/12, ZZr: B6*12

Die Formel =RMZ(B5/12;B6*12;B3;B4;0) liefert ein Ergebnis von -1.210,41 Euro. Ist Ihnen die monatliche Belastung zu hoch, können Sie sich jetzt an Ihre Belastungsgrenze herantasten. Setzt man die Restschuld bspw. auf -300.000 Euro, reduziert sich die Zahlung auf -823,96 Euro im Monat.

Zins- und Tilgungsanteil

RMZ liefert die komplette Belastung, die für den Kredit zu zahlen ist. Um den Zins- und Tilgungsanteil zu ermitteln, arbeiten Sie mit ZINSZ(Zins;Zr;Zzr;Bw;[Zw];[F]) (berechnet den Zinsbetrag) und KAPZ (Zins;Zr;Zzr;Bw;[Zw];[F]) (liefert die Tilgung). 

Hier gibt es eine Besonderheit. Die Funktionen beziehen sich immer auf eine bestimmte Periode, zum Beispiel den ersten Monat oder das letzte Jahr. Die Periode müssen Sie im Fenster Funktionsargumente explizit im Argument Zr angeben. 

Für die erste Periode auf Monatsbasis lauten die Formeln =ZINSZ(B5/12;1;B6*12;B3;B4). Das ergibt bei einer Restschuld von 300.000 Euro einen Zinsanteil in Höhe von -437,50 Euro, =KAPZ(B5/12;1;B6*12;B3;B4) liefert 386,46 Euro.

© Screenshot & Montage: PC Magazin

ZZR, KUMZINSZ und KUMKAPITAL liefern Informationen zu Laufzeit, gezahlten Zinsen und getilgten Schulden.

Zinsen und Tilgung für die gesamte Laufzeit ermitteln 

Sie wollen wissen, wie viel Zinsen Sie während der gesamten Laufzeit eines Kredits zahlen? Dabei unterstützt Sie die Funktion KUMZINSZ(Zins;Zzr;Bw;Zeitraum_Anfang;Zeitraum_Ende;F), eine weitere Funktion aus der Rubrik Finanzmathematik. 

Sie ermittelt die Zinsen jedoch nur dann, wenn der Kredit vollständig getilgt wird. Hier hilft ein kleiner Trick. Hilfsweise wird mit ZZR(Zins,Rmz,Bw,[Zw],[F]) berechnet, wie viele Perioden es dauern würde, bis der Kredit zu den angebotenen Konditionen getilgt wäre. 

Für das vorangegangene Beispiel ergibt =ZZR(0,015/12;-823,96;350000;0;0) eine Laufzeit von aufgerundet 606,1 Monaten, also mehr als 50 Jahre! Mit dieser Angabe kann mit KUMZINSZ (Zins;Zzr;Bw;Zeitraum_Anfang;Zeitraum_Ende;F) die Zinslast, analog mit KUMKAPITAL(Zins;Zzr;Bw;Zeitraum_Anfang;Zeitraum_Ende;F) die Tilgung, für einen bestimmten Zeitraum berechnet werden.

Die beiden Funktionen erfordern zwei weitere Argumente, nämlich Zeitraum_Anfang und Zeitraum_Ende. Sollen die Werte für die ersten zehn Jahre auf monatlicher Basis berechnet werden, entspricht Zeitraum_Anfang dem Wert 1 und Zeitraum_Ende dem Wert 120 (10x12 Monate). =KUMZINSZ(0,015/12;606,1;350000;1;120;0) ergibt - 48.875,27 Euro zu zahlende Zinsen. 

KUMKAPITL können Sie analog einsetzen. Achtung: Werden im Anschluss an die Laufzeit von 10 Jahren neue Kredite aufgenommen, kommen weitere Zins- und Tilgungszahlungen hinzu.

© Screenshot & Montage: PC Magazin

Ein Tilgungsplan vermittelt einen genauen Überblick über den Verlauf von Zinszahlungen, Tilgungsleistungen und Restschulden.

Prozentuale Tilgung vorgeben

In den vorangegangenen Beispielen wurde die Tilgung immer als Geldbetrag berechnet. Banken geben diese häufig als Prozentwert an. Welche Auswirkung der Prozentsatz hat, ermitteln Sie am schnellsten mithilfe einer manuellen Formel. 

Beispiel: Darlehenshöhe 250.000 Euro (B3), Zinssatz (0,95 % in B4), Tilgung (3,5 % in B5). Die Formel zur Berechnung der monatlichen Rate lautet =C3/12*(C4+C5). Aus diesen Angaben lässt sich mit wenig Aufwand ein Tilgungsplan erstellen. 

Zins- und Tilgungsanteil können Sie ganz unkompliziert manuell berechnen (etwa Zinsen in C9: =$C$4*C3%12*-1). Die Tilgung ist nichts anderes als die Differenz aus Annuität und Zinsen (etwa D9: B9-C9). Alternativ geht‘s auch mit den bereits vorgestellten Funktionen ZINSZ() und KAPZ()

Eine Besonderheit gibt es bei der Restschuldberechnung. Diese ergibt sich für das Ende des ersten Monats in E9 mit Hilfe der Formel =C3+D9. Ab dem zweiten Monat lautet die Formel =E9+D10. Diese Formel können Sie in die nachfolgenden Zellen kopiern.

Mehr lesen

Chronologische Liste und Netflix-Links -

Neuerscheinungen in der Übersicht -

Vorschau auf Film- und Serien-Highlights -

Mehr zum Thema

Office-Tipp

Mit der Teilergebnis-Funktion in Excel rechnen Sie auch in gefilterten Listen korrekt. Unser Tipp zeigt, wie es geht.
MS Office - Excel

Mit Tastenkombinationen beschleunigen Sie die Arbeit in Excel und machen früher Feierabend. Wir haben die wichtigsten Kombinationen zusammengetragen.
Anleitung

Stundenpläne, Tages- und Wochenplaner kann man immer wieder sehr gut gebrauchen. Mit Excel können Sie sich diese Planungshilfen sehr schnell selbst…
Office Tipps & Tricks

Gelegentlich möchte man zwei Texte oder Tabellen miteinander vergleichen. Das geht mit Word und Excel sehr einfach von der Hand. Wir zeigen Ihnen wie.
Microsoft Office

Vieles lässt sich in Excel intuitiv lösen. Für einige Probleme müssen die Anwender aber tief in die Trickkiste greifen und Funktionen in der richtigen…