Microsoft Office
Wie funktioniert Zinsberechnung mit Excel?
Sie sollten genau nachrechnen, bevor Sie sich für einen Kredit entscheiden. Wir zeigen, wie Excel bei der Zinsberechnung hilft.
Sie möchten zum Beispiel einen Kredit über 100.000 Euro aufnehmen. Sie bekommen den Kredit für fünf Prozent Zinsen pro Jahr. Am Ende jedes Jahres möchten Sie den Kredit durch eine Zahlung von 10.000 Euro inklusive der aufgelaufenen Zinsen tilgen. Eine Excel-Tabelle zeigt, wie das Darlehen in den nächsten zehn Jahren kleiner wird und was Sie in dieser Zeit an Zinsen bezahlen. Geben Sie dazu in eine leere Excel-Tabelle Folgendes ein:
"A1": Kreditsumme; "B1": 100000
"A2": Zinssatz; "B2": 5%
"A3": Jährliche Zahlung; "B3": 10000
Formatieren Sie die Zellen B1 und B3 in das Euroformat. Markieren Sie dazu den gewünschten Bereich. Zellen, die nicht direkt zusammenstehen, markieren Sie, indem Sie in die erste Zelle klicken (also "B1"), dann die Taste "Strg" gedrückt halten und in die nächste zu markierende Zelle klicken ("B3"). Klicken Sie im Register "Start" in der Gruppe "Zahl" auf den Auswahlpfeil des Feldes "Standard".
- Lesetipp: Excel: Nur jede n-te Zelle addieren
In der Auswahl klicken Sie auf den Eintrag "Währung". Um die Feldbreiten für die Anzeige zu optimieren, markieren Sie den Bereich von "A1" bis "B3", klicken in der Gruppe "Zellen" auf den Eintrag "Format" und wählen den Befehl "Spaltenbreite automatisch anpassen".
So erstellen Sie das Gerüst der Tabelle für das gewünschte Darlehen
Nachdem der Eingabebereich fertiggestellt ist, geht es an den Aufbau der Tabelle. Damit die Tabelle vom Eingangsbereich getrennt ist, beginnen Sie in der Zeile fünf die Überschriften einzugeben:
"A5": Jahr;
"B5": Anfangsschuld;
"C5": Zinsen;
"D5": Zahlung;
"E5": Restdarlehen
Weiter geht es in der nächsten Zeile:
"A6": "1. Jahr";
"B6": "=B1";
hier wird Bezug auf den Wert in der Zelle "B"1 genommen. Wenn Sie den Wert im Eingabebereich später ändern, wird die Tabelle komplett neu durchgerechnet.
C6: "=B6*$B$2";
hier werden die Zinsen berechnet. Da die Formel später kopiert werden soll und Excel grundsätzlich relativ kopiert, müssen Sie dem Programm mitteilen, dass beim Kopieren immer mit der Zelle "B2" gerechnet werden soll. Daher ist es wichtig, die Dollarzeichen zu setzen. Das ist für Excel der Befehl, immer absolut zu kopieren.
D6: "=$B$3";
hier wird absolut auf das Feld "B3" verwiesen. Das Ergebnis in diesem Feld, zeigt den Betrag der jährlichen Tilgung.
E 6: "=B6+C6-D6";
hier wird die jeweilige Anfangsschuld mit den Zinsen für das Jahr addiert und die Zahlung abgezogen. Heraus kommt das Restdarlehen im laufenden Jahr.
Damit der Anfang der Tabelle schnell erkannt wird, formatieren Sie ihn anders als den Rest der Tabelle. Markieren Sie dazu die Überschriften. Klicken Sie in der Gruppe "Formatvorlagen" auf das Symbol "Zellenformatvorlagen". Wählen Sie aus den angebotenen Formatierungen zum Beispiel "Akzent1" aus.
Mit den Ausfüllfunktionen reduziert sich der Aufwand enorm
Für die weiteren Tabelleneinträge wird weitgehend auf die Ausfüllfunktionen von Excel zurückgegriffen. Das erspart viel Arbeit und Zeit. Klicken Sie auf den Eintrag in der Zelle "A6": "1.Jahr". Ziehen Sie den Cursor auf den Kopieranfasser (das ist der kleine Punkt in der unteren rechten Ecke der Zelle). Sobald ein dünnes Fadenkreuz erscheint, haben Sie den richtigen Punkt im Zugriff. Ziehen Sie mit gedrückter linker Maustaste bis in die Zeile fünfzehn und lassen Sie die linke Maustaste los. Die Zellen werden mit den Werten "2. Jahr" bis "10. Jahr" aufgefüllt.
Auch die benötigten Formeln lassen sich einfach und schnell kopieren
Alle anderen Felder sollen ebenfalls durch das Kopieren von Zellen erreicht werden. Lassen Sie sich aber nicht durch Zwischenergebnisse beim Kopiervorgang irritieren Die Tabelle kann erst richtig rechnen, wenn alle Kopierfunktionen abgeschlossen sind. Nur einen Bezug müssen Sie noch manuell eingeben. Der Rest ist danach nur noch eine Kopiersache.
- Klicken Sie in die Zelle "B7" und geben dort den Bezug auf das Restdarlehen ein: "=E6".
- Ziehen Sie das Ergebnis in Zelle "B7" per Kopieranfasser bis in die Zelle "B15".
- Klicken Sie in die Zelle "C6" und kopieren Sie die Formel bis in die Zelle "C15".
- Verfahren Sie ebenso mit der Zelle "D6" und abschließend noch mit "E6".
Nachdem alle Kopierfunktionen erledigt sind, berechnet Excel alle Werte und zeigt, wie viel Restschuld nach zehn Jahren übrig ist.
Schön zu wissen, wann der Schuldenberg weg ist
Um zu berechnen, wann die Restschuld beglichen ist, markieren Sie die Zellen "A15" bis "E15". Ziehen Sie den markierten Bereich jetzt per Kopieranfasser bis in die Zeile 20. Sie erkennen, dass nach 14 Jahren nur noch eine geringe Restschuld übrig ist und der Kredit im 15. Jahr vollständig bezahlt ist.
Planspiele sind mit der Excel-Tabelle mit nur wenig Aufwand möglich
Jetzt ist es natürlich spannend, mit unterschiedlichen Summen und Zinssätzen zu planen. Vor allem wenn Sie mehrere Angebote mit unterschiedlichen Zinssätzen haben, sind solche Vergleiche sehr aufschlussreich. Hier sollten Sie die Ergebnisse auf jeden Fall miteinander vergleichen. Bewährt hat sich dabei, mit einer Kopie der Originaltabelle zu experimentieren und dieser aussagekräftige Namen zu geben.
Bei hohen Beträgen und langen Laufzeiten machen die kleinsten Zinsunterschiede viel Geld aus. Sie kopieren eine komplette Tabelle mit allen Formatierungen indem Sie die Taste "Strg" gedrückt halten und die entsprechende Tabelle mit gedrückter linker Maustaste an eine freie Stelle auf dem Tabellenregister schieben. Zum Umbenennen einer Tabelle machen Sie einen Doppelklick auf das Tabellenregister und geben den gewünschten Tabellennamen ein.
Wenn Sie wissen möchten, was Sie an Zinsen für Ihren Kredit bezahlt haben, klicken Sie in eine freie Zelle unterhalb der Zinsspalte (zum Beispiel "C22"). Klicken Sie dann in der Gruppe "Bearbeiten" auf das Summen-Symbol. Betätigen Sie abschließend die "Enter-Taste", schon erkennen Sie die aufzubringenden Zinsen.
Monatliche Belastung mit einer Formel ermitteln
Um zu berechnen, was an monatlicher Belastung bei einer bestimmten Darlehenssumme, einer definierten Laufzeit und einem aktuellen Zinssatz herauskommt, hält Excel die finanzmathematische Funktion "RMZ" (=Regelmäßige Zahlung) bereit. Denken Sie beim Einsatz der Formel aber daran, dass zusätzliche Gebühren, eventuelle Provisionen und so weiter nicht berücksichtigt werden. Um die Funktion zu nutzen, bauen Sie zunächst einen Eingabebereich für die bekannten Werte auf.
- Tippen Sie in die Zelle "A1" die Überschrift ein: "Monatliche Kreditbelastung".
- Weiter geht es in Zelle "A3" mit der "Kredithöhe". In die Zelle "B3" kommt der Wert 10.000, den Sie gleich wieder mit dem Euroformat formatieren.
- Die Zelle "A4" bekommt den Inhalt "Monatliche Kreditbelastung", in die Zelle "B4" tragen Sie eine "3" für drei Jahre ein.
- Die Zelle "A5" bekommt den Inhalt "Zinssatz", die Zelle "B5" bekommt den Inhalt "5%".
Damit sind die Eingabeparameter festgelegt. Sie können diese Parameter später jederzeit ändern und sich das Ergebnis ansehen.
Für ein korrektes Ergebnis müssen Sie die Argumente richtig zuweisen
Die Funktion rechnet das Ergebnis allerdings nur dann korrekt aus, wenn Sie dazu die geforderten Argumente richtig eingeben. Daher ist es wichtig, deren Bedeutung genau zu kennen. In der Funktion "RMZ" haben die Argumente folgende Bedeutung:
- "Zins": ein fester Zinssatz, der jährlich angegeben wird. Bei monatlicher Belastung müssen Sie diesen durch 12 teilen (Zinssatz/12).
- "Zzr": Das Argument steht für den "Zahlungszeitraum". Hier wird mit Monaten gerechnet. Daher müssen Sie die Laufzeit in Jahren mit 12 multiplizieren.
- "Bw": steht für "Barwert". Dies ist ein konstanter Wert, in diesem Beispiel der aktuelle Gesamtwert zu Beginn der Zahlungen, also die Kredithöhe.
- "Zw": ist der "Zinswert", hier der verzinste Endwert. Da in unserem Beispiel keine Restschuld übrigbleiben soll, ist "0" der richtige Eintrag.
- "F": Dieses Argument steht für die "Fälligkeit", also ob Sie am Anfang oder am Ende eines Monats tilgen wollen. Ein leeres Feld oder eine "0" bedeutet, dass Sie am Ende des Monats die Rate zahlen. Wenn Sie als Argument eine "1" eingeben, ist der Betrag zum Beginn des Monats fällig.
Tippen Sie in die Zelle "A7" ein: "Monatliche Rückzahlung:" und klicken Sie in die Zelle "B7". Aktivieren Sie im Menüband das Register "Formeln" und in der Gruppe "Funktionsbibliothek" die Kategorie "Finanzmathematik". Aktivieren Sie die Funktion "RMZ". Das Dialogfenster "Funktionsargumente" wird eingeblendet. Füllen Sie die Funktionsargumente aus:
"Zins": "B5/12"
"Zzr": "B4*12"
"Bw": "B3"
"Zw": "0"
"F": "0"
Mit Klick auf die Schaltfläche "OK" wird der monatlich zu zahlende Betrag als negativer Wert ausgegeben.
Drehen Sie den Spieß um und erfahren Sie, was Sparen einbringt
Sie haben Geld über und möchten es anlegen? Was am Ende dabei herauskommt, berechnet Excel für Sie. Hier kommt die Funktion "ZW" (Zukünftiger Wert) zum Einsatz. Anstatt einen Kredit aufzunehmen, haben Sie die 10.000 Euro übrig und möchten dieses Geld anlegen. Zum Rechnen lassen Sie den Zinssatz von fünf Prozent zunächst stehen. Passen Sie diesen Wert später auf einen aktuellen Wert an. Wählen Sie aus der Kategorie die Funktion "ZW".
Das Argument "Zins" füllen Sie mit "B5/12", das Argument "ZZR" bekommt die Formel "B4*12". Das Argument "RMZ" bleibt bei dieser Berechnung zunächst leer. Dieses Feld können Sie füllen, wenn Sie einen monatlichen Sparbeitrag leisten wollen. In das Argument "BW" kommt der Verweis auf die 10.000 Euro, also "B3". Mit einem Klick auf die Schaltfläche "OK" wird Ihnen das Ergebnis berechnet.
