Formel zur Berechnung einer Annuitätenzahlung in Excel

Darlehenszahlungen lassen sich mit Microsoft Office Excel einfacher und schneller berechnen. Viel mehr Zeit wird für die manuelle Berechnung aufgewendet. Dieser Artikel konzentriert sich auf Rentenzahlungen, die Merkmale ihrer Berechnung, Vor- und Nachteile.

Was ist eine annuitätenzahlung

Eine Methode der monatlichen Rückzahlung eines Darlehens, bei der sich der eingezahlte Betrag während der gesamten Laufzeit des Darlehens nicht ändert. Diese. An bestimmten Daten eines jeden Monats zahlt eine Person einen bestimmten Geldbetrag ein, bis der Kredit vollständig zurückgezahlt ist.

Außerdem sind die Zinsen für das Darlehen bereits im Gesamtbetrag enthalten, der an die Bank gezahlt wird.

Einstufung Rente

Rentenzahlungen können in folgende Arten unterteilt werden:

1. Fest. Zahlungen, die sich nicht ändern, haben einen festen Satz, unabhängig von äußeren Bedingungen.
2. Währung. Die Möglichkeit, den Zahlungsbetrag im Falle eines Rückgangs oder Anstiegs des Wechselkurses zu ändern.
3. indexiert. Zahlungen je nach Level, Inflationsindikator. Während der Leihfrist ändert sich oft ihre Größe.
4. Variablen. Annuität, die sich je nach Zustand des Finanzsystems ändern kann, Instrumente.

Passt auf! Feste Raten sind für alle Kreditnehmer vorzuziehen, weil sie wenig Risiko haben.

Vor- und Nachteile der Rentenzahlung

Um das Thema besser zu verstehen, ist es notwendig, die Hauptmerkmale dieser Art von Kreditzahlungen zu untersuchen. Es hat folgende Vorteile:

• Festlegen eines bestimmten Zahlungsbetrags und des Datums seiner Zahlung.
• Hohe Verfügbarkeit für Kreditnehmer. Fast jeder kann unabhängig von seiner finanziellen Situation eine Leibrente beantragen.
• Die Möglichkeit, die Höhe der monatlichen Rate bei steigender Inflation zu senken.

Nicht ohne Nachteile:

• Hohe Rate. Der Kreditnehmer wird im Vergleich zur Differenzzahlung einen größeren Geldbetrag überzahlen.
• Probleme, die sich aus dem Wunsch ergeben, die Schulden vorzeitig zu tilgen.
• Keine Neuberechnungen für vorzeitige Zahlungen.

Was ist die Darlehenszahlung?

Die Rentenzahlung hat folgende Bestandteile:

• Zinsen, die eine Person bei der Rückzahlung eines Darlehens zahlt.
• Teil des Hauptbetrags.

Infolgedessen übersteigt der Gesamtbetrag der Zinsen fast immer den Beitrag des Kreditnehmers zur Reduzierung der Schulden.

Grundrentenzahlungsformel in Excel

Wie oben erwähnt, können Sie in Microsoft Office Excel mit verschiedenen Zahlungsarten für Darlehen und Vorschüsse arbeiten. Rente ist keine Ausnahme. Im Allgemeinen lautet die Formel, mit der Sie Rentenbeiträge schnell berechnen können, wie folgt:  

Wichtig! Es ist nicht möglich, Klammern im Nenner dieses Ausdrucks zu öffnen, um ihn zu vereinfachen.

Die Hauptwerte der Formel werden wie folgt entschlüsselt:

• AP – Rentenzahlung (der Name wird abgekürzt).
• O – die Höhe der Hauptschuld des Kreditnehmers.
• PS – der von einer bestimmten Bank monatlich vorgeschlagene Zinssatz.
• C ist die Anzahl der Monate, die das Darlehen dauert.

Um die Informationen zu verarbeiten, genügt es, einige Beispiele für die Verwendung dieser Formel zu geben. Sie werden weiter besprochen.

Beispiele für die Verwendung der PMT-Funktion in Excel

Wir geben eine einfache Bedingung des Problems an. Die monatliche Darlehenszahlung muss berechnet werden, wenn die Bank einen Zins von 23% vorschlägt und der Gesamtbetrag 25000 Rubel beträgt. Die Ausleihe dauert 3 Jahre. Das Problem wird nach dem Algorithmus gelöst:

1. Erstellen Sie basierend auf den Quelldaten eine allgemeine Tabelle in Excel.

2. Aktivieren Sie die PMT-Funktion und geben Sie Argumente dafür in das entsprechende Feld ein.
3. Geben Sie im Feld „Einsatz“ die Formel „B3/B5“ ein. Dies ist der Zinssatz für das Darlehen.
4. In die Zeile „Nper“ schreiben Sie den Wert in der Form „B4*B5“. Dies ist die Gesamtzahl der Zahlungen für die gesamte Laufzeit des Darlehens.
5. Füllen Sie das Feld „PS“ aus. Hier müssen Sie den von der Bank abgezogenen Anfangsbetrag angeben und den Wert „B2“ schreiben.

6. Stellen Sie sicher, dass nach dem Klick auf „OK“ in der Quelltabelle der Wert „Monatliche Zahlung“ berechnet wurde.

Zusätzliche Information! Eine negative Zahl zeigt an, dass der Kreditnehmer Geld ausgibt.

Ein Beispiel für die Berechnung der Höhe der Überzahlung eines Darlehens in Excel

Bei diesem Problem müssen Sie den Betrag berechnen, den eine Person, die einen Kredit von 50000 Rubel zu einem Zinssatz von 27% für 5 Jahre aufgenommen hat, zu viel bezahlen wird. Insgesamt leistet der Kreditnehmer 12 Zahlungen pro Jahr. Lösung:

1. Kompilieren Sie die ursprüngliche Datentabelle.

2. Ziehen Sie vom Gesamtbetrag der Zahlungen den Anfangsbetrag gemäß der Formel ab «=ABS(ПЛТ(B3/B5;B4*B5;B2)*B4*B5)-B2». Es muss in die Formelleiste oben im Hauptmenü des Programms eingefügt werden.
3. Als Ergebnis wird der Betrag der Überzahlungen in der letzten Zeile des erstellten Schildes angezeigt. Der Kreditnehmer zahlt zusätzlich 41606 Rubel zu viel.

Die Formel zur Berechnung der optimalen monatlichen Kreditrate in Excel

Eine Aufgabe mit der folgenden Bedingung: Der Kunde hat ein Bankkonto für 200000 Rubel mit der Möglichkeit einer monatlichen Auffüllung registriert. Es ist notwendig, den Zahlungsbetrag zu berechnen, den eine Person jeden Monat leisten muss, damit sie nach 4 Jahren 2000000 Rubel auf ihrem Konto hat. Der Satz beträgt 11 %. Lösung:

1. Erstellen Sie eine Tabelle basierend auf den Originaldaten.

2. Geben Sie die Formel in die Excel-Eingabezeile ein «=ПЛТ(B3/B5;B6*B5;-B2;B4)» und drücken Sie „Enter“ auf der Tastatur. Die Buchstaben unterscheiden sich je nach den Zellen, in denen die Tabelle platziert wird.
3. Prüfen Sie, ob der Beitragsbetrag automatisch in der letzten Zeile der Tabelle berechnet wird.

Passt auf! Damit der Kunde also in 4 Jahren 2000000 Rubel mit einer Rate von 11% ansammeln kann, muss er jeden Monat 28188 Rubel einzahlen. Das Minus im Betrag zeigt an, dass der Kunde Verluste erleidet, indem er der Bank Geld gibt.

Merkmale der Verwendung der PMT-Funktion in Excel

Im Allgemeinen wird diese Formel wie folgt geschrieben: =PMT(Rate; nper; ps; [bs]; [Typ]). Die Funktion hat folgende Eigenschaften:

1. Bei der Berechnung der monatlichen Beiträge wird nur der Jahresbeitrag berücksichtigt.
2. Bei der Festlegung des Zinssatzes ist es wichtig, anhand der Anzahl der Raten pro Jahr neu zu kalkulieren.
3. Anstelle des Arguments „Nper“ in der Formel wird eine bestimmte Zahl angegeben. Dies ist die Zahlungsfrist.

Zahlungsberechnung

Im Allgemeinen wird die Rentenzahlung in zwei Schritten berechnet. Um das Thema zu verstehen, muss jede der Phasen separat betrachtet werden. Dies wird weiter diskutiert.

Stufe 1: Berechnung der monatlichen Rate

Um in Excel den Betrag zu berechnen, den Sie jeden Monat für ein Darlehen mit festem Zinssatz zahlen müssen, müssen Sie:

1. Stellen Sie die Quelltabelle zusammen und wählen Sie die Zelle aus, in der Sie das Ergebnis anzeigen möchten, und klicken Sie oben auf die Schaltfläche „Funktion einfügen“.

2. Wählen Sie in der Liste der Funktionen „PLT“ und klicken Sie auf „OK“.

3. Legen Sie im nächsten Fenster die Argumente für die Funktion fest und geben Sie die entsprechenden Zeilen in der kompilierten Tabelle an. Am Ende jeder Zeile müssen Sie auf das Symbol klicken und dann die gewünschte Zelle im Array auswählen.

4. Wenn alle Argumente ausgefüllt sind, wird die entsprechende Formel in die Zeile zur Eingabe von Werten geschrieben und das Berechnungsergebnis mit einem Minuszeichen erscheint im Feld der Tabelle „Monatliche Zahlung“.

Wichtig! Nach Berechnung der Rate kann der Betrag berechnet werden, den der Kreditnehmer für die gesamte Kreditlaufzeit zu viel bezahlen wird.

Stufe 2: Zahlungsdetails

Die Höhe der Überzahlung kann monatlich berechnet werden. Als Ergebnis wird eine Person verstehen, wie viel Geld sie jeden Monat für einen Kredit ausgeben wird. Die Detailberechnung wird wie folgt durchgeführt:

1. Erstellen Sie eine Tabelle für 24 Monate.

2. Setzen Sie den Cursor in die erste Zelle der Tabelle und fügen Sie die Funktion „OSPLT“ ein.

3. Füllen Sie die Funktionsargumente auf die gleiche Weise aus.

4. Wenn Sie das Feld „Zeitraum“ ausfüllen, müssen Sie sich auf den ersten Monat in der Tabelle beziehen und Zelle 1 angeben.

5. Überprüfen Sie, ob die erste Zelle in der Spalte „Zahlung durch die Körperschaft des Darlehens“ ausgefüllt ist.
6. Um alle Zeilen der ersten Spalte zu füllen, müssen Sie die Zelle bis zum Ende der Tabelle strecken

7. Wählen Sie die Funktion „PRPLT“, um die zweite Spalte der Tabelle auszufüllen.
8. Füllen Sie alle Argumente im geöffneten Fenster gemäß dem folgenden Screenshot aus.

9. Berechnen Sie die monatliche Gesamtzahlung, indem Sie die Werte in den vorherigen zwei Spalten addieren.

10. Um den „zu zahlenden Restbetrag“ zu berechnen, müssen Sie den Zinssatz zur Zahlung des Darlehenskörpers hinzufügen und ihn bis zum Ende des Schildes strecken, um alle Monate des Darlehens zu füllen.

Zusätzliche Information! Bei der Berechnung des Restes müssen Dollarzeichen an die Formel gehängt werden, damit sie sich bei Dehnung nicht herausbewegt.

Berechnung der Annuitätenzahlungen für ein Darlehen in Excel

Die PMT-Funktion ist für die Berechnung der Annuität in Excel verantwortlich. Das Berechnungsprinzip im Allgemeinen besteht darin, die folgenden Schritte auszuführen:

1. Kompilieren Sie die ursprüngliche Datentabelle.
2. Erstellen Sie für jeden Monat einen Schuldentilgungsplan.
3. Markieren Sie die erste Zelle in der Spalte „Zahlungen auf das Darlehen“ und geben Sie die Berechnungsformel ein „PLT ($B3/12;$B$4;$B$2)“.
4. Der resultierende Wert wird für alle Spalten der Platte gestreckt.

Berechnung in MS Excel Rückzahlung des Kapitalbetrags der Schuld

Rentenzahlungen sind monatlich in festen Beträgen zu leisten. Und der Zinssatz ändert sich nicht.

Berechnung des Saldos des Kapitalbetrags (mit BS=0, Art=0)

Angenommen, ein Darlehen von 100000 Rubel wird für 10 Jahre zu 9% aufgenommen. Die Höhe der Hauptschuld muss im 1. Monat des 3. Jahres berechnet werden. Lösung:

1. Erstellen Sie ein Datenblatt und berechnen Sie die monatliche Zahlung mit der obigen PV-Formel.
2. Berechnen Sie den Anteil der Zahlung, der zur Tilgung eines Teils der Schulden erforderlich ist, mit der Formel «=-PMT-(PS-PS1)*Artikel=-PMT-(PS +PMT+PS*Artikel)».
3. Berechnen Sie die Höhe der Hauptschuld für 120 Perioden mit einer bekannten Formel.
4. Verwenden Sie den HPMT-Operator, um den für den 25. Monat gezahlten Zinsbetrag zu ermitteln.
5. Überprüfe das Ergebnis.

Berechnung des Kapitalbetrags, der zwischen zwei Perioden gezahlt wurde

Diese Berechnung erfolgt am besten auf einfache Weise. Sie müssen die folgenden Formeln verwenden, um den Betrag im Intervall für zwei Perioden zu berechnen:

• =«-BS(item; con_period; plt; [ps]; [type]) /(1+type *item)».
• = „+ BS(Rate; Start_Periode-1; Plt; [ps]; [Typ]) /IF(Start_Periode =1; 1; 1+Typ *Rate)“.

Passt auf! Buchstaben in Klammern werden durch bestimmte Werte ersetzt.

Vorzeitige Rückzahlung mit reduzierter Laufzeit oder Zahlung

Wenn Sie die Kreditlaufzeit verkürzen müssen, müssen Sie zusätzliche Berechnungen mit dem IF-Operator durchführen. So wird es möglich, den Nullsaldo zu kontrollieren, der nicht vor Ablauf der Zahlungsfrist erreicht werden sollte.

Um Zahlungen zu reduzieren, müssen Sie den Beitrag für jeden Vormonat neu berechnen.

Kreditrechner mit unregelmäßigen Zahlungen

Es gibt mehrere Rentenoptionen, bei denen der Kreditnehmer an jedem Tag des Monats variable Beträge hinterlegen kann. In einer solchen Situation werden der Saldo der Schulden und die Zinsen für jeden Tag berechnet. Gleichzeitig benötigen Sie in Excel:

1. Geben Sie die Tage des Monats ein, für die Zahlungen geleistet werden, und geben Sie ihre Anzahl an.
2. Prüfen Sie auf negative und positive Beträge. Negative werden bevorzugt.
3. Zählen Sie die Tage zwischen zwei Terminen, an denen Geld eingezahlt wurde.

Berechnung der periodischen Zahlung in MS Excel. Festgeld

In Excel können Sie schnell die Höhe regelmäßiger Zahlungen berechnen, sofern bereits ein fester Betrag angesammelt wurde. Diese Aktion wird mit der PMT-Funktion ausgeführt, nachdem die Anfangstabelle kompiliert wurde.

Zusammenfassung

So lassen sich Rentenzahlungen in Excel einfacher, schneller und effizienter berechnen. Für deren Berechnung ist der PMT-Betreiber verantwortlich. Ausführlichere Beispiele finden Sie oben.