Die PMT-Funktion in Excel 2013 - Dummies

Die Excel 2013 PMT Die Funktion im Dropdown-Menü der Schaltfläche Finanzen auf der Registerkarte Formeln der Multifunktionsleiste berechnet die periodische Zahlung für eine Annuität, wobei ein Strom gleicher Zahlungen und ein konstanter Zinssatz angenommen wird. Die PMT-Funktion verwendet die folgende Syntax:

= PMT (rate, nper, pv, [fv], [type])

Wie bei den anderen gängigen Finanzfunktionen ist rate der Zinssatz. pro Periode, nPer ist die Anzahl der Perioden, pv ist der Barwert oder der Betrag, den die zukünftigen Zahlungen aktuell wert sind, fv ist der zukünftige Wert oder Bargeld Saldo, den Sie nach der letzten Zahlung erhalten möchten (Excel nimmt einen zukünftigen Wert von Null an, wenn Sie dieses optionale Argument weglassen, wie Sie es bei der Berechnung von Kreditzahlungen machen würden), und type ist der Wert 0 für Zahlungen. Ende der Periode oder der Wert 1 für Zahlungen zu Beginn der Periode.

(Wenn Sie das optionale Argument vom Typ weglassen, nimmt Excel an, dass die Zahlung am Ende des Zeitraums erfolgt.)

Die PMT-Funktion wird häufig zur Berechnung verwendet. die Zahlung für Hypothekendarlehen mit einem festen Zinssatz. Die Abbildung zeigt ein Beispielarbeitsblatt, das eine Tabelle mit der PMT-Funktion enthält, um Darlehenszahlungen für einen Bereich von Zinssätzen (von 2,75 Prozent bis 4,00 Prozent) und Principals (150.000 bis 159.000 US-Dollar) zu berechnen.

Die Tabelle verwendet den anfänglichen Principal, den Sie in Zelle B2 eingeben, kopiert sie in Zelle A7 und erhöht sie dann um $ 1, 000 im Bereich A8: A16. Die Tabelle verwendet den anfänglichen Zinssatz, den Sie in Zelle B3 eingeben, in Zelle B6 kopieren und dann diese Anfangsrate um 1/4 Prozent im Bereich C6: G6 erhöhen. Der Begriff in Jahren in Zelle B4 ist ein konstanter Faktor, der in der gesamten Kreditzahlungstabelle verwendet wird.

Um sich ein Bild davon zu machen, wie einfach es ist, diese Art von Kreditzahlungstabellen mit der PMT-Funktion aufzubauen, führen Sie die folgenden Schritte aus, um sie in einem neuen Arbeitsblatt anzulegen:

Geben Sie die Titel ein. in Zelle A1, Principal in Zelle A2, Zinssatz in Zelle A3 und Term (in Jahren) in Zelle A4.
Geben Sie $ 150, 000 in Zelle B2 ein, geben Sie in Zelle B3 2. 75% ein und geben Sie 30 in Zelle B4 ein.

Dies sind die Startwerte, mit denen Sie die Tabelle "Kreditzahlungen" erstellen.
Positionieren Sie den Zellenzeiger in B6 und bauen Sie dann die Formel = B3 auf.

Wenn Sie eine Verknüpfungsformel erstellen, die den Anfangszinssatzwert in B3 mit der Formel voranbringt, stellen Sie sicher, dass der Zinssatzwert in B6 sofort alle Änderungen widerspiegelt, die Sie in Zelle B3 vornehmen.
Positionieren Sie den Zellenzeiger in der Zelle C6 und bauen Sie dann die Formel = B6 + auf. 25%.

Durch Hinzufügen von 1/4 Prozent zum Zinssatz zum Wert in B6 mit der Formel = B6 +. 25% in C6, anstatt eine Serie mit dem AutoFill-Handle zu erstellen, stellen Sie sicher, dass der Zinssatzwert in Zelle C6 immer 1/4 Prozent größer ist als jeder in Zelle B6 eingegebene Zinssatzwert.
Ziehen Sie das Füll-Handle in die Zelle C6, um die Auswahl nach rechts auf die Zelle G6 zu erweitern, und lassen Sie dann die Maustaste los.
Positionieren Sie den Zellenzeiger in der Zelle A7 und bauen Sie dann die Formel = B2 auf.

Indem Sie die Formel = B2 verwenden, um den Anfangsprinzipal zur Zelle A7 weiterzuleiten, stellen Sie sicher, dass Zelle A7 immer den gleichen Wert wie Zelle B2 hat.
Positionieren Sie den Zellenzeiger in A8 aktiv und erstellen Sie dann die Formel = A7 + 1000.

Auch hier verwenden Sie die Formel = A7 + 1000 anstatt eine Serie mit der AutoFill-Funktion zu erstellen, so dass der Hauptwert in A8 immer um 1 000 $ höher ist als jeder Wert in Zelle A7.
Ziehen Sie das Füll-Handle in Zelle A8 nach unten, bis Sie die Auswahl auf Zelle A16 erweitern und dann die Maustaste loslassen.
Klicken Sie in Zelle B7 auf die Schaltfläche Funktion einfügen in der Formelleiste, wählen Sie in der Dropdown-Liste Oder eine Kategorie auswählen die Option Finanziell aus, und doppelklicken Sie dann im Listenfeld Funktion auswählen auf die Funktion PMT.

Im Dialogfeld "Funktionsargumente" können Sie die Argumente rate, nper, und pv angeben. Vergewissern Sie sich, dass das Dialogfeld "Funktionsargumente" nach rechts verschoben wird, sodass kein Teil davon die Daten in den Spalten A und B Ihres Arbeitsblatts verdeckt, bevor Sie mit den folgenden Schritten zum Ausfüllen der Argumente fortfahren.
Klicken Sie in Zelle B6, um B6 in das Textfeld Rate einzufügen, und drücken Sie dann zweimal F4, um die relative Referenz B6 in die gemischte Referenz B $ 6 (relative Spalte, absolute Zeile) zu konvertieren, bevor Sie / 12 eingeben.

Sie konvertieren die relative Zellenreferenz B6 in die gemischte Referenz B $ 6, sodass Excel die Zeilennummer beim nicht anpasst, wenn Sie die PMT-Formel in jeder Zeile der Tabelle nach unten kopieren, aber Passen Sie den Spaltenbuchstaben an, wenn Sie die Formel über die Spalten kopieren.

Da der anfängliche Zinssatz, der in B3 eingegeben (und dann in Zelle B6 vorgezogen) ist, ein Jahres Zinssatz ist, aber Sie wollen die monatliche Darlehenszahlung wissen, brauchen Sie Um die Jahresrate in eine Monatsrate zu konvertieren, indem Sie den Wert in Zelle B6 durch 12 dividieren.
Klicken Sie auf das Textfeld Nper, klicken Sie auf Zelle B4, um diese Zellreferenz in dieses Textfeld einzufügen, und drücken Sie dann einmal F4, um die relative Zahl zu konvertieren. Referenz B4 auf die absolute Referenz $ B $ 4, bevor Sie * 12 eingeben.

Sie müssen die relative Zellenreferenz B4 in die absolute Referenz $ B $ 4 konvertieren, damit Excel weder die Zeilennummer noch den Spaltenbuchstaben anpasst, wenn Sie die PMT-Formel in den Zeilen und Spalten der Tabelle kopieren.

Da der Begriff in B3 (der dann in Zelle B6 vorgezogen wird) eine jährliche Periode ist, aber Sie wollen die monatliche Darlehenszahlung wissen, müssen Sie die Jahresperioden zu Monatsperioden durch Multiplikation des Wertes in Zelle B4 mit 12.
Klicken Sie auf das Textfeld Pv, klicken Sie auf A7, um diese Zellenreferenz in dieses Textfeld einzufügen, und drücken Sie dreimal F4, um die relative Referenz A7 in die gemischte Referenz $ A7 (Spalte absolut, relative Zeile) zu konvertieren.

Sie müssen die relative Zellenreferenz A7 in die gemischte Referenz $ A7 konvertieren, damit Excel den Spaltenbuchstaben nicht anpasst, wenn Sie die PMT-Formel über die einzelnen Spalten der Tabelle kopieren, sondern die Zeilennummer beim Kopieren anpassen. die Formel in allen Zeilen.
Klicken Sie auf OK, um die Formel = PMT (B $ 6/12, $ B $ 4 * 12, $ A7) in Zelle B7 einzufügen.

Jetzt können Sie diese ursprüngliche PMT-Formel nach unten kopieren und dann die gesamte Tabelle "Auszahlungen" ausfüllen.
Ziehen Sie das Füll-Handle in Zelle B7 nach unten, bis Sie den Füllbereich auf Zelle B16 erweitern und dann die Maustaste loslassen.

Nachdem Sie die ursprüngliche PMT-Formel in Zelle B16 kopiert haben, können Sie sie nach rechts in G16 kopieren.
Ziehen Sie das Füll-Handle nach rechts, bis Sie den Füllbereich B7: B16 auf die Zelle G16 erweitern und dann die Maustaste loslassen.

Nachdem Sie die ursprüngliche Formel mit dem Füll-Handle kopiert haben, achten Sie darauf, die Spalten B bis G ausreichend zu erweitern, um ihre Ergebnisse anzuzeigen. (Sie können dies in einem Schritt tun, indem Sie durch die Kopfzeilen dieser Spalten ziehen und dann auf den rechten Rand der Spalte G doppelklicken.)

Nachdem Sie eine solche Tabelle erstellt haben, können Sie den Anfangsprinzipal ändern. oder Zinssatz, sowie die Laufzeit, um zu sehen, was die Zahlungen unter verschiedenen anderen Szenarien sein würden. Sie können die manuelle Neuberechnung auch aktivieren, damit Sie steuern können, wann die Tabelle "Kreditzahlungen" neu berechnet wird.