So berechnen Sie den effektiven Zinssatz für Anleihen mit Excel
In diesem Blogbeitrag werde ich erläutern, wie Sie den Effektivzins für Anleihen mit Excel berechnen.
Was ist der effektive Zinssatz?
Wenn Sie wissen, was ein effektiver Zinssatz ist, können Sie diesen Abschnitt überspringen. Gehen Sie zum nächsten Abschnitt.
Der Effektivzins ist der Zinssatz, den Sie tatsächlich aus Ihrer Investition oder für Ihr Darlehen erhalten oder zahlen müssen.
Angenommen, Sie haben ein Darlehen in Höhe von 10.000 USD von einer örtlichen Bank mit einem jährlichen Prozentsatz von 12% aufgenommen. Die Bank sagte, dass Ihre Zinsen jeden Monat erhöht werden.
Sie wissen nicht, was das bedeutet. Lesen Sie sorgfältig, es ist wichtig, dieses Konzept zu verstehen.
Jährlicher Prozentsatz / Nominalzins: 12%
Also, monatlicher Zinssatz: 12%/12 = 1%
Am Ende des Monats 1: Ihr Kapital + Zinsen werden: $ 10.000 + $ 10.000 x 1% = $10,000 (1 + 0.01) = $10100
Hier gehe ich davon aus, dass Sie die Zinsen ($ 100 für diesen Fall) für den ersten Monat nicht bezahlt haben. Für den 2. Monat beträgt Ihr Kapital also 10.100 USD.
Am Ende des Monats 2: Ihr Kapital + Zinsen werden = $10100 + $ 10100 x 1% = $10201
Da ich aus der obigen Anweisung eine Formel erstellen möchte, werde ich die obige Zeile folgendermaßen neu formulieren:
= $10100 + $10100 x 1%
= $10100 (1 + 1%)
= $10,000 x (1 + 1%) x (1 + 1%); Als $ 10100 = $ 10.000 x (1 + 1%)
= $10,000 x (1 + 1%) ^ 2; Als (1 + 1%) x (1 + 1%) = (1 + 1%) ^2
= $10,000 x (1 + 0.01) ^ 2
Auf diese Weise beträgt Ihr Kapital + Zinsen am Ende des 3. Monats: 10.000 USD x (1 + 0.01) ^ 3
… … …
… … …
… … …
Auf diese Weise betragen Ihr Kapital und Ihre Zinsen nach 12 Monaten: 10.000 USD x (1 + 0.01) ^12 = $11268.25
Sie werden also Gesamtzinsen zahlen: ($11268.25 – $10,000) / $10,000 = 12.68%. Aber die Bank sagte, dass Ihr jährlicher Prozentsatz 12% betrug.
Was die Bank sagte, war Ihr nominaler Zinssatz, aber aufgrund des monatlichen Zinseszinses zahlen Sie mehr als den nominalen Zinssatz. Dieser Zinssatz wird als Effektiver Zinssatz bezeichnet.
Dieser Artikel gibt Ihnen eine detailliertere Vorstellung über den nominalen und effektiven Zinssatz: Nominalzinsformel Excel (berechnen Sie aus dem effektiven Zinssatz).
Berechnen wir nun den effektiven Zinssatz für Anleihen.
Beispiel 1: Berechnung des Effektivzinses für diskontierte Anleihen
Sie haben etwas mehr Geld und möchten Ihr Geld in eine Anleihe investieren.
Hier sind die Details der Anleihe:
- Nennwert: $ 100.000
- Angegebener Zinssatz / Nominalzins / Kupon / APR: 5%
- Laufzeit: 5 Jahre
- Zinszahlungshäufigkeit: Jährlich
- Ausgabepreis: $ 95000 (Die Anleihe wird mit einem Abschlag verkauft)
Wenn Sie mehr über die Bindung wissen wollen, gehen Sie durch diesen Link.
Berechnung des Effektivzinssatzes und Berechnung der Internen Rendite eigentlich dasselbe. Ich werde Ihnen also zeigen, wie Sie die interne Rendite der oben genannten Anleihe berechnen.
Schauen Sie sich das folgende Bild an.
Das Bild ist selbsterklärend. Aber lassen Sie es mich Schritt für Schritt erklären.
Im Jahr 0 investieren Sie 95.000 USD (Ausgabepreis der Anleihe), um die Anleihe zu kaufen. Sie zahlen 95.000 US-Dollar, weil die Anleihe mit Abschlag verkauft wird.
Am Ende des 1. Jahres erhalten Sie 5.000 USD als Zinszahlung vom Anleihenemittenten. Wie werden diese 5.000 berechnet?
Der Nennwert der Anleihe beträgt 100.000 USD und der nominale jährliche Zinssatz beträgt 5%. Sie erhalten also eine Zinszahlung in Höhe von: 100.000 USD x 5% = 100.000 USD x 0,05 = 5.000 USD.
Auf die gleiche Weise erhalten Sie nach dem 2., 3. und 4. Jahr die Zinszahlungen in Höhe von 5.000 USD.
Aber nach dem Ende des 5. Jahres erhalten Sie $ 105.000. Weil Ihre Anleihe fällig ist und Sie den Nennwert der Anleihe von 100.000 USD + die jährlichen Zinsen von 5.000 USD = 105.000 USD zurückerhalten.
Dies sind also die Cashflows, die Sie über den Zeitraum des Haltens der oben genannten Anleihe erhalten.
Nun haben wir den IRR (Internal Rate of Return) oder den Effektivzinssatz mit der Excel-Funktion IRR in der Zelle C17 berechnet: =IRR (C11: C16)
Die IRR-Funktion gibt den internen Zinssatz für eine Reihe von Cashflows zurück.
Syntax der IRR-Funktion: IRR (values, )
Als Werte der IRR-Funktion haben wir die periodischen Cashflows eingegeben und als Vermutung keinen Wert übergeben, da dies nicht erforderlich ist.
Und wir erhalten 6,19% als effektive Rate dieser Cashflows.
Sie sehen also, dass es nicht schwierig ist, den effektiven Zinssatz für Anleihen zu berechnen, die mit Abschlag verkauft werden.
Lesen Sie mehr: So berechnen Sie IRR (Internal Rate of Return) in Excel (9 einfache Möglichkeiten).
Beispiel 2: Berechnung des Effektivzinses in Premium-Anleihen
In diesem Beispiel verkauft unsere Anleihe mit einer Prämie.
Hier sind die Details der Anleihe:
- Nennwert: $ 100.000
- Angegebener Zinssatz / Nominalzins / Kupon / APR: 5%
- Laufzeit: 5 Jahre
- Zinszahlungshäufigkeit: Jährlich
- Ausgabepreis: $ 105.000 (Die Anleihe wird mit Prämie verkauft)
Für diese Anleihe beträgt der Ausgabepreis 105.000 USD. Also, im Jahr 0 zeigt unsere Investition – $ 105.000.
Nach den Jahren 1, 2, 3 und 4 erhält der Anleihegläubiger Zinszahlungen in Höhe von 5.000 USD. Weil der Nennwert der Anleihe 100.000 US-Dollar beträgt und der jährliche Nominalzins 5% beträgt. Also, jedes Jahr Zinszahlung wird $ 100.000 x 5% = $ 5.000.
Am Ende des 5. Jahres wird die Anleihe fällig. Der Anleihegläubiger erhält also den Nennwertbetrag und die jährliche Zinszahlung = $100,000 + $5,000 = $105,000.
Für diese Anleihe sind dies also die Cashflows, die der Anleihegläubiger für die nächsten 5 Jahre erhält.
Wir haben die IRR-Funktion von Excel verwendet, um den internen Zinssatz oder Effektivzinssatz für diese Cashflows in der Zelle C35 zu berechnen: =IRR (C29: C34).
Und wir erhalten den internen Zinssatz oder den effektiven Zinssatz von 3,88%.
Sie sehen also den großen Unterschied zwischen einer Anleihe in Diskont und Prämie.
Vor dem Kauf einer Anleihe berechnen Sie jedoch nicht nur den Effektivzins, sondern überprüfen auch das Rating der Anleihe (von einer unabhängigen Ratinggesellschaft). Je höher das Rating einer Anleihe ist, desto sicherer ist Ihr Geld beim Unternehmen.
Beispiel 3: Berechnung der Effektivzinsen bei Zahlungen nicht jährlich
In diesem Beispiel zeige ich Ihnen eine Anleihe, die Zinszahlungen halbjährlich (zweimal im Jahr) und nicht jährlich zahlt. Einige Emittenten von Anleihen zahlen möglicherweise sogar monatlich, vierteljährlich oder in beliebiger Häufigkeit.
Hier sind die Details der Anleihe:
- Nennwert: $ 100,000
- Angegebene Rate / Nominal Rate / Coupon Rate / APR: 5%
- Laufzeit: 5 Jahre
- Zinszahlungshäufigkeit: Halbjährlich (zweimal im Jahr)
- Ausgabepreis: $ 95.000 (die Anleihe wird mit einem Abschlag verkauft)
Angenommen, Sie haben die Anleihe am 1-Jan-2018 gekauft und hier sind Ihre Cashflows für die nächsten 5 Jahre.
Da die Zinszahlungen halbjährlich (zweimal im Jahr) erfolgen, müssen wir den angegebenen Zinssatz / Nominalzins von 5% durch 2 dividieren. Das wird 2,5% zurückgeben.
Unsere halbjährlichen Zinszahlungen betragen also 100.000 USD x 2,5% = 2.500 USD.
Und wenn die Anleihe fällig wird, erhalten Sie 102.500 USD (Nennwert + Zinsen der letzten 6 Monate).
Um die interne Zinsrendite oder den effektiven Zinssatz dieser Cashflows zu erhalten, müssen wir die XIRR-Funktion von Excel verwenden. Weil die Cashflows nicht jährlich sind. Wenn die Cashflows auf diskrete Weise empfangen werden, müssen Sie die XIRR-Funktion verwenden, um die interne Rendite der Cashflows zu ermitteln.
XIRR Funktion gibt die interne Rendite für einen Zeitplan von Cashflows.
Syntax der XIRR-Funktion: XIRR (Werte, Daten, ).
In der Zelle G22 habe ich diese Formel verwendet: =XIRR (G11: G21, F11: F21)
G11: G21 sind erhaltene Cashflows und F11: F21 sind die Daten des Empfangs der Cashflows.
Und wir erhalten 6.274% Effektivzinssatz für diese Cashflows.
Excel-Arbeitsmappe herunterladen
Dies ist meine Methode zur Berechnung des Effektivzinssatzes für Anleihen mit Excel. Kennen Sie einen besseren Weg, dies zu tun? Ich bin gespannt. Bitte teilen Sie Ihre Gedanken zu diesem Artikel im Kommentarfeld mit.
Verwandte Messwerte
- Effektivzinsmethode der Amortisation in Excel
- Berechnen Sie die Rendite bis zur Fälligkeit (YTM) in Excel (einfache Formel + Vorlage)
- Effektivzinsmethode Excel-Vorlage (kostenlos)
- Effektivzinsformel Excel + Kostenloser Rechner