Hoe effectieve rente op obligaties te berekenen met behulp van Excel
In deze blogpost zal ik bespreken hoe de effectieve rente op obligaties te berekenen met behulp van Excel.
Wat is de effectieve rentevoet?
als u weet wat een effectieve rente is, kunt u deze sectie overslaan. Ga naar de volgende sectie.
de effectieve rentevoet is de rentevoet die u ontvangt of moet betalen uit uw investering of voor uw lening.
zeg, u nam een lening van $10.000 van een lokale bank met een jaarlijks percentage van 12%. De bank zei dat uw rente elke maand verhoogd zal worden.
u weet niet wat dat betekent. Lees goed, het is belangrijk om dit concept te begrijpen.
jaarlijks percentage / nominale rentevoet: 12%
So, maandelijkse rentevoet: 12%/12 = 1%
aan het einde van maand 1: uw hoofdsom + rente zal zijn: $ 10.000 + $ 10.000 x 1% = $10,000 (1 + 0.01) = $10100
hier ga ik ervan uit dat je de rente ($100 voor deze zaak) voor de eerste maand niet hebt betaald. Dus, voor de tweede maand, zal je directeur $10.100 zijn.
aan het einde van maand 2: uw hoofdsom + rente is = $ 10100 + $ 10100 x 1% = $10201
omdat ik een formule wil maken uit de bovenstaande verklaring, dus ik ga de bovenstaande regel op de volgende manier herhalen:
= $10100 + $10100 x 1%
= $10100 (1 + 1%)
= $10,000 x (1 + 1%) x (1 + 1%); Als $10100 = de $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
Op deze manier, aan het einde van de 3e maand, uw Hoofdsom + Rente: De $10.000 x (1 + 0.01) ^ 3
… … …
… … …
… … …
Op deze manier, na 12 maanden, uw Hoofdsom en Rente zullen worden: de $10.000 x (1 + 0.01) ^12 = $11268.25
Dus, bent u van plan om te betalen rente totaal: ($11268.25 – $10,000) / $10,000 = 12.68%. Maar de bank zei dat je jaarlijkse percentage 12% was.
wat de bank zei was uw nominale rente, maar als gevolg van samengestelde rente maandelijks, betaalt u meer dan de nominale rente. Deze rente wordt de effectieve rente genoemd.
dit artikel geeft u een meer gedetailleerd idee over de nominale en effectieve rente: nominale Renteformule Excel (bereken op basis van effectieve rente).
laten we nu de effectieve rente op obligaties berekenen.
Voorbeeld 1: Berekening van de effectieve rente op disconteringsobligaties
u hebt wat extra geld en u wilt uw geld beleggen in een obligatie.
hier zijn de details van de obligatie:
- nominale waarde: $ 100.000
- opgegeven rente / nominale rente / couponrente / JKP: 5%
- looptijd: 5 jaar
- frequentie van de rentebetaling: jaarlijks
- uitgifteprijs: $ 95000 (de obligatie wordt met disconto verkocht))
als je meer wilt weten over de band, ga dan via deze link.
het berekenen van de effectieve rente en het berekenen van het interne rendement in feite hetzelfde. Ik zal je dus laten zien hoe je de interne Opbrengstvoet van de bovenstaande obligatie kunt berekenen.
bekijk de volgende afbeelding.
het beeld spreekt voor zich. Maar laat me het stap voor stap uitleggen.
in jaar 0 investeert u $ 95.000 (uitgifteprijs van de obligatie) om de obligatie te kopen. Je betaalt 95.000 dollar omdat de obligatie met korting verkocht wordt.
aan het einde van het eerste jaar krijgt u $5.000 als rentebetaling van de obligatie-emittent. Hoe wordt die 5000 dollar berekend?
de nominale waarde van de obligatie is $100.000 en de nominale jaarlijkse rente is 5%. Zo, zult u rentebetaling van bedrag krijgen: $ 100.000 x 5% = $ 100.000 x 0,05 = $5.000.
op dezelfde manier krijgt u na het 2e, 3e en 4e jaar de rentebetalingen van een bedrag van $ 5.000.
maar na het einde van het 5e jaar krijgt u $105.000. Omdat uw obligatie is gerijpt en u krijgt de nominale waarde van de obligatie terug $ 100.000 + de jaarlijkse rente $ 5.000 = $ 105.000.
dit zijn dus de kasstromen die u krijgt gedurende de periode van het aanhouden van de bovengenoemde obligatie.
nu hebben we de IRR (internal rate of return) of de effectieve rente berekend met behulp van de IRR-functie van Excel in de cel C17: =IRR (C11: C16)
IRR-functie retourneert de internal rate of return voor een reeks kasstromen.
syntaxis van IRR-functie: IRR (waarden, )
als de waarden van de IRR-functie hebben we de periodieke kasstromen ingevoerd en als gok hebben we geen waarde doorgegeven omdat deze niet vereist is.
en we krijgen 6,19% als de effectieve rente van deze kasstromen.
u ziet dus dat het niet moeilijk is om de effectieve rente te berekenen op obligaties die met korting worden verkocht.
Lees meer: hoe IRR (internal rate of return) in Excel te berekenen (9 eenvoudige manieren).
Voorbeeld 2: berekening van de effectieve rente op premieobligaties
In dit voorbeeld wordt onze obligatie verkocht met een premie.
hier zijn de details van de obligatie:
- nominale waarde: $ 100.000
- opgegeven rente / nominale rente / couponrente / APR: 5%
- looptijd: 5 jaar
- frequentie van rentebetaling: jaarlijks
- uitgifteprijs: $ 105.000 (de obligatie wordt verkocht met premie)
voor deze obligatie is de uitgifteprijs $105.000. Dus, in jaar 0, onze investering toont – $ 105.000.
na jaar 1, 2, 3 en 4 ontvangt obligatiehouder rentebetalingen van $ 5.000. Omdat de nominale waarde van de obligatie $100.000 is en de jaarlijkse nominale rente 5% is. Dus, elk jaar rente betaling zal $ 100.000 x 5% = $ 5.000.
aan het einde van jaar 5 vervalt de obligatie. De obligatiehouder krijgt dus het nominale bedrag en de jaarlijkse rentebetaling = $100,000 + $5,000 = $105,000.
voor deze obligatie zijn dit de kasstromen die de obligatiehouder de komende vijf jaar zal ontvangen.
we hebben de IRR-functie van Excel gebruikt om het interne rendement of de effectieve rente voor deze kasstromen in cel C35 te berekenen: = IRR (C29: C34).
en we krijgen het interne rendement of de effectieve rente van 3,88%.
u ziet dus het enorme verschil tussen het krijgen van een obligatie in disconto en in premie.
maar voordat u een obligatie koopt, berekent u niet alleen de effectieve rente, u bekijkt ook de rating van de obligatie (door een onafhankelijk ratingbedrijf). Hoe hoger de rating van een obligatie, uw geld is veiliger bij het bedrijf.
Voorbeeld 3: Berekening van de effectieve rente wanneer betalingen niet jaarlijks zijn
In dit voorbeeld laat ik u een obligatie zien die rentebetalingen halfjaarlijks (tweemaal per jaar), niet jaarlijks betaalt. Sommige emittenten van obligaties kunnen zelfs maandelijks, driemaandelijks of op elke frequentie betalen.
hier zijn de details van de obligatie:
- nominale waarde: $ 100.000
- opgegeven rente / nominale rente / couponrente / JKP: 5%
- looptijd: 5 jaar
- frequentie van rentebetaling: halfjaarlijks (tweemaal per jaar)
- uitgifteprijs: $ 95,000 (de obligatie wordt verkocht op een korting)
zeg, je kocht de obligatie op 1-Jan-2018 en hier zijn je kasstromen voor de komende 5 jaar.
te berekenen aangezien de rentebetalingen halfjaarlijks (tweemaal per jaar) zullen plaatsvinden, moeten we de opgegeven rente/nominale rente van 5% delen door 2. Dat levert 2,5% op.
onze halfjaarlijkse rentebetalingen zullen dus $100.000 x 2,5% = $2.500 bedragen.
en wanneer de obligatie vervalt, krijgt u $102.500 (nominale waarde + de rente van de laatste 6 maanden).
om het interne renterendement of de effectieve rente van deze kasstromen te krijgen, moeten we de XIRR-functie van Excel gebruiken. Omdat de kasstromen niet jaarlijks zijn. Wanneer de kasstromen op discrete manieren worden ontvangen, moet u de XIRR-functie gebruiken om de interne opbrengstvoet van de kasstromen te krijgen.
XIRR functie geeft het interne rendement voor een schema van kasstromen.
syntaxis van XIRR functie: XIRR (waarden, datums, ).
in de cel G22 heb ik deze formule gebruikt: =XIRR (G11: G21, F11: F21)
G11: G21 zijn ontvangen kasstromen, en F11: F21 zijn de data waarop de kasstromen worden ontvangen.
en we krijgen 6.274% effectieve rente voor deze kasstromen.
Download Excel-werkmap
dit is dus mijn methode om de effectieve rente op obligaties te berekenen met behulp van Excel. Weet je een betere manier om dat te doen? Ik wil het graag weten. Deel uw gedachten over dit artikel op het opmerkingenveld.
gerelateerde metingen
- effectieve rentemethode van afschrijving in Excel
- Bereken rendement tot looptijd (YTM) in Excel (eenvoudige formule + sjabloon)
- effectieve rentemethode Excel-sjabloon (vrij)
- effectieve rentemethode Excel + vrije Calculator