Was wäre das mathematische Äquivalent der CUMPRINC-Formel in Excel

Question

Ich arbeite an einer Anwendung, mit der ich die nach n Jahren getilgte Tilgung einer Hypothek berechnen möchte.

Hypothek (M) von 100.000 USD Zinsen (I) von 5 % Tilgungsdauer (T) von 25 Jahren Monatliche Zahlung (P) beträgt 581,60 USD (kanadische Hypothek) Szenario: 5 Jahre oder 60 Monate

Die CUMPRINC-Formel lautet:

CUMPRINC((I/2+1)^(2/12)-1, (T * 12), M, 1, 60, 0) = 11.492,49 $

Ich habe versucht, die Formel zu finden, aber keine der bisher gefundenen Quellen liefert ein Beispiel, mit dem ich etwas anfangen konnte.

Danke!

Answer 1

Ich fand eine ausgezeichnete Antwort auf den Beitrag
Wie berechne ich die Tilgung einer Hypothek?

Ich zitiere diese Antwort unten:

Die Frage lautet: „Ich möchte wissen, wie viel Kapital ich nach n Perioden von der Hypothek abbezahlt hätte.“

Es ist nicht ganz klar, ob Sie die Tilgung oder die Resttilgung wünschen, daher hier die Formeln für die Resttilgung im Monat n, die Tilgung im Monat n und die aufgelaufene Tilgung im Monat n.

p[n] = (d + (1 + r)^n (r s - d))/r

pr[n] = (d - r s) (r + 1)^(n - 1)

accpr[n] = (d - r s) ((1 + r)^n - 1)/r  

Wo

p[n] is the principal remaining in month n, i.e the balance
pr[n] is the principal repayment in month n
accpr[n] is the accumulated principal repaid in month n

s is the initial loan principal
r is the monthly interest rate i.e. nominal annual rate ÷ 12
d is the regular monthly payment

Beispiel

Wenn Sie einen Kredit über 1000 Pfund über 3 Jahre mit 10 % Zinsen pro Monat aufnehmen (ziemlich hoch, aber das ist nur ein Beispiel), dbeträgt die monatliche RückzahlungStandardformelIst

s = 1000
r = 0.1
n = 36

d = r s/(1 - (1 + r)^-n) = 103.34306381837332

Mithilfe dieser Zahlen lässt sich die verbleibende Kapitalsumme, also der Saldo, berechnen:

s = 1000
r = 0.1
d = 103.34306381837332

n = 36
p[n] = (d + (1 + r)^n (r s - d))/r = 0 as expected

Darstellung des über die 3-Jahres-Laufzeit verbleibenden Kapitals

p[n] = (d + (1 + r)^n (r s - d))/rfür n = 0zun = 36

Ebenso gilt für die Berechnung der Tilgung:

Diagramm der Tilgungsraten über die 3-jährige Laufzeit

pr[n] = (d - r s) (r + 1)^(n - 1)für n = 1zun = 36

Die kumulierten Tilgungsraten nach 36 Monaten:

n = 36
accpr[36] = (d - r s) ((1 + r)^n - 1)/r = 1000

verglichen mit Gesamtrückzahlungen von 36 d = 3720.35.

Beispiel Tilgungstabelle

month  interest   principal repayment =          accumulated     balance
n      at 10%     payment - interest repayment   princ. repmt.   p[n]
0                                                                1000
1      100        103.34306 - 100 = 3.34306        3.34306       996.657
2      99.6657    103.34306 - 99.6657 = 3.67737    7.02043       992.98
3      99.2979    103.34306 - 99.2979 = 4.04511    11.0655       988.934
...
35     17.9356    103.34306 - 17.9356 = 85.4075    906.052       93.9482
36     9.39482    103.34306 - 9.39482 = 93.9482    1000          0

Ableitung

Der Saldo eines Kredits folgt dieser Wiederholungsgleichung.

p[n + 1] = p[n] (1 + r) - d

Wo

p[n] is the balance of the loan in month n
r is the monthly interest rate
d is the regular monthly payment

Dies kann wie folgt gelöst werden (mitMathematicain diesem Fall).

RSolve[{p[n + 1] == p[n] (1 + r) - d, p[0] == s}, p[n], n]

Wos is the initial loan principal

nachgebendp[n_] := (d + (1 + r)^n (r s - d))/r

prDiese Notation drückt eine Formel für den Saldo im Monat n aus, die in einer Funktion für die Tilgung des Kapitals (also die reguläre Rückzahlung abzüglich der Zinszahlung auf den Saldo des Vormonats) verwendet werden kann .

pr[n_] := d - (p[n - 1] r)

Durch Kombination dieser Ausdrücke entsteht ein Ausdruck in Bezug auf d, r, s und n.

pr[n_] := (d - r s) (r + 1)^(n - 1)

Nach Ablauf nder Perioden beträgt die angesammelte Kapitalrückzahlung:

accpr[n] = Σ(d - r s) (r + 1)^(k - 1)für k = 1zuk = n

∴ durch Induktion,accpr[n] = (d - r s) ((1 + r)^n - 1)/r

Nachtrag

Die obigen Ergebnisse können einfacher erreicht werden durch Verwendung derStandardformel für den Barwert einer gewöhnlichen Rente, wobei der verbleibende Teil der Hypothek selbst als kleines Darlehen behandelt wird.

Beispielsweise das Abrufen von Werten für den 28. Monat.

s = 1000
r = 0.1
n = 36

P = r s/(1 - (1 + r)^-n) = 103.34306381837332

Der Restbetrag im Monat 28

x = 36 - 28 = 8

balance = P(1 - (1 + r)^-x)/r = 551.328

principal paid = principal - balance = 448.672

Was mit der vorherigen Formulierung übereinstimmt

accpr[28] = 448.672

und wie Wick für Excel und Google Sheets bereitstellt

=CUMPRINC(0.1,36,1000,1,28,0)

-448.672