Tipp 11.6 - Laufzeit & Effektivzins eines Kredits

Wie kann ich die Laufzeit sowie den Effektivzins eines Annuitätendarlehens (z.B. für ein Hauskauf) berechnen?

Wenn Sie sich selbst ein paar Eckdaten zu Ihrer Immobilienfinanzierung ausrechnen möchten, können Sie dies relativ schnell mit VBA-Bordmitteln sowie den dazugehörigen finanzmathematischen Funktionen realisieren. Zum Einsatz in diesem Tipp kommen die Funktionen Rate (Excel-Funktion ZINS) und NPer (Excel Funktion ZZR).

Kurze Erklärung zu den Funktionen

Die Funktion NPer gibt die Anzahl der Zahlungsperioden einer Investition zurück, die auf periodischen, gleich bleibenden Zahlungen sowie einem konstanten Zinssatz basiert. (NPer = Anzahl der Zahlungszeiträume). Folgende Parameter sind erforderlich:

Rate: Zinssatz pro Periode (Zahlungszeitraum)
Pmt: Die Rate, die in jeder Periode gezahlt wird. Dieser Betrag kann sich während der Laufzeit nicht ändern. Üblicherweise umfasst dieser Wert Tilgung und Zinsen, nicht jedoch sonstige Gebühren oder Steuern.
PV: Der Kreditbetrag
FV: Der zukünftige Endwert, den Sie nach der letzten Zahlung erreicht haben möchten (optional, standardmäßig 0)
Due: Zahlungsmodus, d.h. die Fälligkeit der Zahlung (optional standardmäßig 0 = Ende des Zahlungszeitraums bzw. 1 = Beginn des Zahlungszeitraumes).

Die Funktion Rate gibt den Zinssatz einer Annuität pro Periode zurück. Rate verwendet zur Berechnung eines Zinssatzes ein Iterationsverfahren. Es ist möglich, dass es keine oder auch mehrere Lösungen gibt. Folgende Parameter sind erforderlich:

NPer: Die Gesamtanzahl der Zahlungsräume
Pmt: Zahlungsbetrag (die Ratenzahlung)
PV: Der Kreditbetrag
FV: Der zukünftige Endwert, den Sie nach der letzten Zahlung erreicht haben möchten (optional, standardmäßig 0)
Due: Zahlungsmodus, d.h. die Fälligkeit der Zahlung (optional standardmäßig 0 = Ende des Zahlungszeitraums bzw. 1 = Beginn des Zahlungszeitraumes).
Guess: Der Schätzwert, entspricht Ihrer Schätzung bezüglich der Höhe des Zinssatzes (optional, wenn Sie keinen Wert für Schätzwert angeben, wird 10 Prozent angenommen. Wenn Rate nicht konvergiert, sollten Sie einen anderen Wert für Schätzwert angeben. Normalerweise konvergiert rate, wenn Schätzwert zwischen 0 und 1 liegt)

Ein Beispiel

Sie möchten (oder müssen) für eine neugekaufte Immobilie 212.000 Euro finanzieren. Ihre Hausbank bietet Ihnen einen Zinssatz über 4,54% (nominal) an (unter Berücksichtigung einer anfänglichen Tilgungsrate von 1,50%). Damit Sie später weitere Kreditangebote vergleich können, möchten Sie sich an Hand der Daten nun folgendes ausrechnen:

Wie hoch ist die monatliche Belastung
Wie lange zahlen Sie für den Kredit
Wie viele Zinsen fallen insgesamt an
Wie hoch ist die Schlussrate

Zur Berechnung der notwendigen Daten benötigen wir lediglich vier Angaben:

K = Kreditbetrag
p = Zinssatz pro Jahr
t = (anfänglicher) Tilgungssatz in %
zm = Zahlungsmodus (1 jährlich, 4 quartalsweise, 12 monatlich)

Die Funktion

Kopieren Sie einfach nachfolgenden Quellcode in die Zwischenablage und fügen Sie anschließend den Inhalt der Zwischenablage in ein neues Modul ein. Die Aufrufparameter finden Sie im Quellcode beschrieben.

Beispiel:

' Laufzeit einer Immobilienfinanzierung sowie montl. Rate ermitteln
Public Function CalcCreditPeriod(pcCredit As Currency, _
                                 pdbInterestRate As Double, _
                                 pdbRepaymentRate As Double, _
                                 plTermsOfPayment As Long, _
                                 pcRate As Currency) _
                                 As Double
  '// =====================================================
  '// Methode   | Ermittelt die Laufzeit eines Annuitäten-
  '               darlehens und berechnet die monatl. Rate
  '// -----------------------------------------------------
  '// Parameter | pcCredit         - Kreditsumme
  '               pdbInterestRate  - Zinssatz (nominal)
  '               pdbRepaymentRate - anfängl. Tilgungssatz
  '               plTermsOfPayment - Anzahl Raten im Jahr
  '               pcRate           - Currency Variable für
  '                                  die Rückgabe der Rate
  '// -----------------------------------------------------
  '// Rückgabe  | Double - Laufzeit Monaten (nicht gerundet)
  '// -----------------------------------------------------
  '// Erstellt  | Stefan Kulpa
  '//           | EDV Innovation & Consulting - Dormagen
  '// -----------------------------------------------------
  '// Beispielaufruf:
  '// siehe Beispielprozedur: Mortgaging
  '// =====================================================
 
  On Error GoTo CalcCreditPeriod_Err
 
  ' monatliche Belastung, gerundet auf 2 Stellen
  ' Rate = Runden(Kredit * (Zinsen + Tilgung) / Anzahl Raten), 2)
  pcRate = XLSRound((pcCredit * (pdbInterestRate + pdbRepaymentRate) / plTermsOfPayment), 2)
 
  ' Laufzeit berechnen, ohne Rundung
  ' Laufzeit ? ZZR(Zinsen / Anzahl Raten, -Rate, Kredit)
  CalcCreditPeriod = NPer(pdbInterestRate / plTermsOfPayment, pcRate * -1, pcCredit)
 
CalcCreditPeriod_Exit:
  On Error GoTo 0
  Exit Function
 
CalcCreditPeriod_Err:
  MsgBox "Fehler " & Err.Number & ": " & _
         Err.Description, vbCritical, _
         "modFinancial.CalcCreditPeriod"
  Resume CalcCreditPeriod_Exit
 
End Function
 
' Effektivzinsberechnung
Public Function CalcEffectiveYield(pdbDelay As Double, _
                                   pcRate As Currency, _
                                   pcRestRate As Currency, _
                                   pcCredit As Currency, _
                                   plTermsOfPayment As Long) _
                                   As Double
  '// =====================================================
  '// Methode   | Berechnet den Effektivzinssatz
  '// -----------------------------------------------------
  '// Parameter | pdbDelay         - Laufzeit in Jahren
  '               pcRate           - montl. Rate
  '               pcRestRate       - letzte Rate
  '               pcCredit         - Kreditsumme
  '               plTermsOfPayment - Anzahl Raten im Jahr
  '// -----------------------------------------------------
  '// Rückgabe  | Double - Effektivzinssatz
  '// -----------------------------------------------------
  '// Erstellt  | Stefan Kulpa
  '//           | EDV Innovation & Consulting - Dormagen
  '// -----------------------------------------------------
  '// Beispielaufruf:
  '// siehe Beispielprozedur: Mortgaging
  '// =====================================================
 
  On Error GoTo CalcEffectiveYield_Err
 
  ' Effektivzinsberechnung aufgerundet auf 4 Stellen
  ' EZins = Runden(Zins(
  '                     Laufzeit,
  '                     -Rate * Anzahl Raten,
  '                     Kredit - Rate * (Anzahl Raten - 1 + 2 * 0) / 2,
  '                     -Restrate + Rate * (Anzahl Raten - 1 + 2 * 0) / 2,
  '                     0),
  '               4)
 
  CalcEffectiveYield = XLSRound(Rate(pdbDelay, _
                                     -pcRate * plTermsOfPayment, _
                                     pcCredit - pcRate * (plTermsOfPayment - 1 + 2 * 0) / 2, _
                                     -pcRestRate + pcRate * (plTermsOfPayment - 1 + 2 * 0) / 2, _
                                     0), 4)
 
CalcEffectiveYield_Exit:
  On Error GoTo 0
  Exit Function
 
CalcEffectiveYield_Err:
  MsgBox "Fehler " & Err.Number & ": " & _
         Err.Description, vbCritical, _
         "modFinancial.CalcEffectiveYield"
  Resume CalcEffectiveYield_Exit
 
End Function
 
' Simuliert die Excel-Runden-Funktion
Public Function XLSRound(pvNumber As Variant, Optional piTyp As Integer = 2) As Double
  '// =====================================================
  '// Methode   | Rundet analog der Excel-Runden-Funktion
  '// Idee von  | Konrad Marfurt+Luke Chung+Karl Donaubauer
  '// -----------------------------------------------------
  '// Parameter | pvNumber - der Wert, der gerundet
  '                          werden soll
  '               piTyp - Die Art, wie gerundet werden soll
  '                       Standardmäßig auf 2 Stellen nach
  '                       dem Komma
  '// -----------------------------------------------------
  '// Rückgabe  | Double - gerundeter Wert
  '// -----------------------------------------------------
  '// Erstellt  | Manuela Kulpa
  '//           | EDV Innovation & Consulting - Dormagen
  '// -----------------------------------------------------
  '// Beispielaufruf:
  '// ?XlsRound(368.8752)    = 368,88
  '   ?XlsRound(368.8752,1)  = 368,9
  '   ?XlsRound(368.8752,-1) = 370
  '   ?XlsRound(368.8752,-2) = 400
  '// =====================================================
 
  On Error GoTo XlsRound_Err
 
  If IsNumeric(pvNumber) Then
    XLSRound = Fix("" & pvNumber * (10 ^ piTyp) + Sgn(pvNumber) * 0.5) / (10 ^ piTyp)
  End If
 
XlsRound_Exit:
  On Error GoTo 0
  Exit Function
 
XlsRound_Err:
  MsgBox "Fehler " & Err.Number & ": " & _
         Err.Description, vbCritical, _
         "modFinancial.XlsRound"
  Resume XlsRound_Exit
 
End Function

Der Beispielaufruf

Beispiel:

' Immobilienfinanzierung Berechnungsbeispiel
Public Sub Mortgaging(pcKredit As Currency, _
                      pdbZinsatz As Double, _
                      pdbTilgung As Double)
  '// =====================================================
  '// Methode   | Beispielberechnung Annuitätendarlehen
  '// -----------------------------------------------------
  '// Parameter | pcKredit   - Kreditsumme
  '               pdbZinsatz - Zinssatz p.a.
  '               pdbTilgung - anfängl. Tilgungssatz
  '// -----------------------------------------------------
  '// Erstellt  | Stefan Kulpa
  '//           | EDV Innovation & Consulting - Dormagen
  '// -----------------------------------------------------
  '// Beispielaufruf:
  '// Mortgaging 212000,0.0454,0.015
  '// =====================================================
 
  Dim dbLaufzeit As Double
  Dim dbEffektivzins As Double
 
  Dim cRate As Currency
  Dim cRestRate As Currency
  Dim cZinsen As Currency
 
  Dim lJahre As Long
  Dim lMonate As Long
 
  Dim sFormat As String
  Dim sProzent As String
  Dim sMsg As String
 
  On Error GoTo Mortgaging_Err
 
  sFormat = "###,##0.00"
  sProzent = "0.00%"
 
  ' Laufzeit ermitteln
  dbLaufzeit = CalcCreditPeriod(pcKredit, pdbZinsatz, pdbTilgung, 12, cRate)
  ' Gesamtzinsen ermitteln
  cZinsen = XLSRound(dbLaufzeit * cRate - pcKredit, 2)
  ' Jahr abgerundet aufs Ganze ermitteln
  lJahre = Int((dbLaufzeit / 12) + 0.01)
  ' Monate aufgerundet aufs Ganze ermitteln
  lMonate = Int(dbLaufzeit + 0.99)
  ' Restrate für den letzen Monat ermitteln
  cRestRate = (lMonate * cRate) - (pcKredit + cZinsen)
  ' Effektivzinsen ermitteln
  dbEffektivzins = CalcEffectiveYield(dbLaufzeit / 12, cRate, cRestRate, pcKredit, 12)
 
  ' Ausgabe der Informationen
  sMsg = "Immobilienfinanzierung Berechnungsbeispiel" & vbCrLf
  sMsg = sMsg & String$(45, "=") & vbCrLf
  sMsg = sMsg & "Finanzierung:          " & Format$(pcKredit, sFormat) & vbCrLf
  sMsg = sMsg & "Zinssatz p.a.:         " & Format$(pdbZinsatz, sProzent) & vbCrLf
  sMsg = sMsg & "anfängl. Tilgungssatz: " & Format$(pdbTilgung, sProzent) & vbCrLf
  sMsg = sMsg & String$(45, "-") & vbCrLf
  sMsg = sMsg & "monatliche Belastung:  " & Format$(cRate, sFormat) & vbCrLf
  sMsg = sMsg & "letzte Kreditrate:     " & Format$(cRestRate, sFormat) & vbCrLf
  sMsg = sMsg & "Zinsbelastung:         " & Format$(cZinsen, sFormat) & vbCrLf
  sMsg = sMsg & "Laufzeit:              " & lJahre & " Jahre und " & lMonate - (lJahre * 12) & " Monat(e)" & vbCrLf
  sMsg = sMsg & "Effektivzinssatz:      " & Format$(dbEffektivzins, sProzent)
 
  ' Ausgabe im Debug-Fenster
  Debug.Print sMsg
 
Mortgaging_Exit:
  On Error GoTo 0
  Exit Sub
 
Mortgaging_Err:
  MsgBox "Fehler " & Err.Number & ": " & _
         Err.Description, vbCritical, _
         "modFinancial.Mortgaging"
  Resume Mortgaging_Exit
 
End Sub

Die Ausgabe im Direktfenster

Immobilienfinanzierung Berechnungsbeispiel
=============================================
Finanzierung:          212.000,00
Zinssatz p.a.:         4,54%
anfängl. Tilgungssatz: 1,50%
---------------------------------------------
monatliche Belastung:  1.067,07
letzte Kreditrate:     137,43
Zinsbelastung:         181.611,40
Laufzeit:              30 Jahre und 9 Monat(e)
Effektivzinssatz:      4,64%

Hinweis: Komplizierter wird es, wenn Sie auch unterjährige Tilgungen (Sondertilgungen) berücksichtigen möchten. Dies wird nicht in der Prozedur einkalkuliert. Verstehen Sie bitte dieses Beispiel als Anregung für die Berechnung einer Immobilienfinanzierung. Auch müssen Sie beachten, dass Darlehen mit einer absinkenden Rate (Tilgungsdarlehen) hiermit nicht gerechnet werden können. Trotz allem dürfte diese kleine Funktion Ihnen die tägliche Arbeit erleichtern und hoffentlich viel Zeit sparen.