Sagen wir, wir leihen uns einen Kredit von 1000 Dollar. Wir zahlen 100 $ pro Monat an die Bank zurück, aber aus irgendeinem dummen Grund ist die letzte Zahlung kleiner und beträgt 50 $. Dies ist ein häufiger Fall in meinem Land. Wie soll dann der effektive Jahreszins berechnet werden? Hier der Zeitplan:
Darf ich auf folgende Frage/Antwort verweisen:
So berechnen Sie den effektiven Jahreszins
https://stackoverflow.com/questions/28050109/excel-vba-formula-for-apr/28084325#28084325
Ich möchte VBA-Code erstellen, um es zu berechnen. Wenn der Autor der akzeptierten Antwort dies wünscht, kann er als Co-Autor des VBA-Codes aufgenommen werden.
Bearbeiten 23. Februar 2015
Ich habe diese Frage mit einem VBA-Code für benutzerdefinierte Excel-Funktionen beantwortet. Wie versprochen ist der Autor der akzeptierten Antwort Mitautor des Codes. Danke!
Wie im zitierten Link zur Berechnung des effektiven Jahreszinses festgelegt , ist die Summe für ein Darlehen
Dabei ist s der Darlehensbetrag, n die Anzahl der Perioden pro Jahr, t die Anzahl der Jahre, pp die periodische Zahlung und r der jährliche effektive Jahreszins (als effektiver Jahressatz ) .
Die letzte Zahlung aufzuteilen und sie fp zu nennen, sieht so aus
was nach Induktion die geschlossene Form ergibt
und das gleiche als kopierbarer Text
s = (1 + r)^-t*(fp + (pp*((1 + r)^t - (1 + r)^(1/n)))/((1 + r)^(1/n) - 1))
Mit
s = 1000.00
n = 12
t = 1
pp = 100
fp = 50
Auflösen nach r-Renditen r = 0.314391
oder 31,44 % effektivem Jahreszins.
Oder je nach Präferenz in einen Nominalsatz umwandeln
((1 + r)^(1/n) - 1)*n = 0.276511
also 27,65 % nominaler effektiver Jahreszins, der monatlich aufgezinst wird.
Bearbeiten
Alternativ nach der Periodenrate p anstelle von r auflösen. Die Summe
hat die geschlossene Form
s = ((1 + p)^(-n*t)*(fp*p + ((1 + p)^(n*t) - 1 - p)*pp))/p
Das Auflösen nach p hat zwei Lösungen:p = -1.67608 or p = 0.0230426
Aus dem positiven p*12 = 27.65 %
nominalen effektiven Jahreszins wird monatlich zusammengesetzt.
k
und wie viel kostet es? Zusammensetzen? 12?s = (pp - (1 + p)^(-n*t)*pp)/p
- wie hier verwendet: So berechnen Sie den effektiven Jahreszins . Für ungleiche Zahlungen ist die geschlossene Form s = ((1 + p)^(-n*t)*(fp*p + ((1 + p)^(n*t) - 1 - p)*pp))/p
.k/t
eine nicht-abstrakte Interpretation?s = Σ r^k for k = a to b
in eine geschlossene Formel umgewandelt wird: s = (r^(1 + b) - r^a)/(r - 1)
. Die Summierung ist der Ausgangspunkt, weil sie der klare Ausdruck der Berechnung ist, und die Formel ist der Endpunkt, weil sie in Berechnungen und Lösungen einfacher zu verwenden ist.s = (1+r)^-t*(fp+(pp*(
-(1+r)^((1/n) )+(1+r)^t))/(-1+(1+r)^(1/n)))
der fett markierte Teil wird zu (-1,314391)^(1/12) was problematisch ist:-)^ * +
in der richtigen Reihenfolge durchführe.s = ((1+r)^(-t))*(fp+(pp*(((1+r)^t)-((1+r)^(1/n))))/(((1+r)^(1/n))-1))
n
Was Sie die Anzahl der Perioden pro Jahr nennen , ist eigentlich die Anzahl der Aufzinsungen pro Jahr, nicht wahr?t
dann gleich?t
wäre 40/52
, ein Bruchteil eines Jahres.r
(effektiver effektiver Jahreszins) und nicht auf p
(periodischer Zinssatz) ausgelegts = (pp*((1+r)^(-t))*(((1+r)^t)-1))/(((1+r)^(1/n))-1)
Sie haben zwei Möglichkeiten - Verwenden Sie die IRR-Funktion einer Tabelle, um die Rendite zu berechnen. Dazu gibt es viele Tutorials im Internet. Zu beachten ist, dass Sie eine erste Zeile eingeben müssen, die -1000 anzeigt, den negativen Durchfluss.
Andernfalls können Sie es Zeile für Zeile tun. Sie müssen den Zeitwert des Geldes verwenden, um den Barwert jeder Zahlung zu einem bestimmten Satz zu erhalten, z. B. 6 %/Jahr = 1,005 (1/2 % pro Monat). Sie erhöhen dann 1,005^(# Monate) und teilen diese 100 durch diese Zahl. Die Summe aller diskontierten Ströme beträgt 1000, wenn der gewählte Satz korrekt ist.
Für die zweite Methode ist dies mein Ergebnis:
Der Code, den ich für die C-Zellen verwendet habe, ist =B2/(C$1^A2)
der, der die Zahlung nimmt, was auch immer in Spalte B ist, und sie um meine Ratenschätzung abzieht, die mit der A-Potenz erhöht wird. Beachten Sie, dass die erste Schätzung 2,5 % betrug, da 1150/1000 = 1,15 (insgesamt 15 %), aber die durchschnittliche Zahlung ist nur 6 Monate alt, also verdopple ich das. Ein paar Sekunden Anpassung nach oben/unten, und das Ergebnis erscheint mit so vielen Stellen wie nötig.
Ich weiß nicht, was VBA ist. Ich hoffe, diese Erklärung kann Sie weiterbringen.
Eine fertige Lösung für ein solches Problem (eine unregelmäßige letzte Zahlung) ist ein Online-Hypothekenrechner, der den Zinssatz ermittelt und eine positive oder negative "Ballonzahlung" berücksichtigt.
Eine Ballonzahlung in Hypothekenkonditionen ist eine zusätzliche Zahlung, die am Ende der Hypothekentilgung zusätzlich und gleichzeitig mit der letzten regulären Zahlung geleistet wird.
Ballonzahlungen entstehen aus mehreren Gründen. Wenn Sie zum Beispiel die Annuitätsformel verwenden, um die genaue monatliche Zahlung für eine bestimmte Hypothek zu berechnen, runden Sie diesen berechneten Betrag um möglicherweise einen halben Cent auf oder ab auf eine genaue Anzahl von Cent und zahlen diesen Betrag für die Laufzeit der Hypothek . Dieser kleine, unvermeidliche Fehler summiert sich mit Zinsen und fällt schließlich als etwas andere letzte Zahlung auf.
Im Fall des OP sollte der Hypothekenrechner eine 12-monatige Hypothek in Höhe von 1000 USD mit zwölf regelmäßigen Zahlungen in Höhe von 100 USD und einer Ballonzahlung in Höhe von -50 USD erhalten.
Hier ist eine benutzerdefinierte Excel-VBA-Funktion zur Berechnung des effektiven Jahreszinses (APR) auf der Grundlage des Folgenden:
Darlehenskapital – der Betrag, den wir von der Bank leihen
pp – regelmäßige Zahlung (gleiche Rate)
fp – Abschlusszahlung (Ballonzahlung)
c – Anzahl von Aufzinsungsperioden pro Jahr
k - Anzahl der Zahlungsperioden für das Darlehen
Bezug nehmend auf Chris Degnens Erklärung t=k/c . Beachten Sie, dass APR in der Funktion eine Anfangsschätzung von APR ist. Es ist optional, sodass Sie sich darüber keine Gedanken machen müssen.
Function APR_pp_fp(LoanPrincipal As Double, pp As Double, fp As Double, c As Integer, k As Integer, Optional APR As Double = 0.01, Optional step As Double = 0.000001) As Double
'Authors Przemyslaw Remin, Chris Degnen
'
'LoanPrincipal - the amount we borrow from bank
'pp - The amount of periodic payment
'fp - The amount of final payment (balloon) payment
'c - Number of compounding periods per year
'k - Number of periods to pay the loan
'APR - Starting Annual Percentage Rate starting from 1%, here it will be used as iterator to find correct Loan Principal
'step - how much we change APR, the smaller the step the more precision we get
'
Dim s1 As Double 'Calculated LoanPrincipal based on guessed APR
Dim s2 As Double 'Calculated LoanPrincipal based on guessed APR but slightly higher
Dim target1 As Double 'the squared difference between s1 and LoanPrincipal
Dim target2 As Double 'the squared difference between s2 and LoanPrincipal
Do Until target1 < target2 'we do the loop until the target falls
s1 = ((1 + APR) ^ (-(k / c))) * (fp + (pp * (((1 + APR) ^ (k / c)) - ((1 + APR) ^ (1 / c)))) / (((1 + APR) ^ (1 / c)) - 1))
target1 = (LoanPrincipal - s1) ^ 2
APR = APR + step
s2 = ((1 + APR) ^ (-(k / c))) * (fp + (pp * (((1 + APR) ^ (k / c)) - ((1 + APR) ^ (1 / c)))) / (((1 + APR) ^ (1 / c)) - 1))
target2 = (LoanPrincipal - s2) ^ 2
Loop
APR_pp_fp = APR
End Function
Für die gestellte Frage müssen Sie Folgendes eingeben:
=APR_pp_fp(1000,100,50,12,12)
und es sollte 0,314392 zurückgeben.
Vielen Dank an Chris Degnen!
mhoran_psprep
Przemyslaw Remin