Hur man beräknar effektiv ränta på obligationer med Excel
i det här blogginlägget kommer jag att diskutera hur man beräknar effektiv ränta på obligationer med Excel.
vad är den effektiva räntan?
om du vet vad en effektiv ränta är kan du hoppa över det här avsnittet. Gå till nästa avsnitt.
den effektiva räntan är den ränta som du får eller måste betala faktiskt från din investering eller för ditt lån.
säg att du tog ett lån på 10 000 dollar från en lokal bank med en årlig procentsats på 12%. Banken sa att ditt intresse kommer att förvärras varje månad.
du vet inte vad det betyder. Läs noggrant, det är viktigt att förstå detta koncept.
årlig procentsats / nominell ränta: 12%
så, månadsränta: 12%/12 = 1%
i slutet av månad 1: Din huvudsakliga + ränta kommer att vara: $10,000 + $10,000 x 1% = $10,000 (1 + 0.01) = $10100
här antar jag att du inte betalade räntan ($100 för det här fallet) för den första månaden. Så, för den 2: a månaden, din rektor kommer att vara $10,100.
i slutet av månad 2: Din huvudsakliga + ränta kommer att vara = $10100 + $10100 x 1% = $10201
som jag vill göra en formel från ovanstående uttalande, så kommer jag att upprepa ovanstående rad på följande sätt:
= $10100 + $10100 x 1%
= $10100 (1 + 1%)
= $10,000 x (1 + 1%) x (1 + 1%); Som $10100 = $10,000 x (1 + 1%)
= $10,000 x (1 + 1%) ^2; som (1 + 1%) x (1 + 1%) = (1 + 1%) ^2
= $10,000 x (1 + 0.01) ^ 2
på detta sätt, i slutet av den 3: e månaden, kommer ditt huvud + ränta att vara: $10,000 x (1 + 0.01) ^ 3
… … …
… … …
… … …
på detta sätt, efter 12 månader, kommer din huvudman och ränta att vara: $10,000 x (1 + 0.01) ^12 = $11268.25
så, du kommer att betala total ränta: ($11268.25 – $10,000) / $10,000 = 12.68%. Men banken sa att din årliga procentsats var 12%.
vad banken sa var din nominella ränta, men på grund av sammansatt ränta varje månad betalar du mer än den nominella räntan. Denna ränta kallas effektiv ränta.
den här artikeln ger dig en mer detaljerad uppfattning om nominell och effektiv ränta: nominell Ränteformel Excel (beräkna från effektiv ränta).
låt oss nu beräkna den effektiva räntan på obligationer.
exempel 1: Beräkning av effektiv ränta på rabattobligationer
du har lite extra pengar och du vill investera dina pengar i en obligation.
här är detaljerna i obligationen:
- nominellt värde: $100,000
- angiven ränta/nominell ränta/kupongränta / APR: 5%
- löptid: 5 år
- Räntebetalningsfrekvens: årlig
- emissionskurs: $95000 (obligationen säljer på rabatt)
om du vill veta mer om obligationen, gå igenom den här länken.
beräkning av effektiv ränta och beräkning av intern avkastning faktiskt samma sak. Så jag ska visa dig hur du beräknar den interna avkastningen för ovanstående obligation.
ta en titt på följande bild.
bilden är självförklarande. Men låt mig förklara det steg för steg.
vid år 0 investerar du $95,000 (emissionskursen på obligationen) för att köpa obligationen. Du betalar $95,000 eftersom obligationen säljer i rabatt.
i slutet av 1: A året får du $5000 som räntebetalning från obligationsutgivaren. Hur beräknas dessa 5 000 dollar?
obligationens nominella värde är $100,000 och nominell årlig ränta är 5%. Så får du räntebetalning av belopp: $100,000 x 5% = $100,000 x 0,05 = $5,000.
på samma sätt, efter 2: a, 3: e och 4: e år, kommer du att få räntebetalningar av belopp $5000.
men efter slutet av 5: e året får du $105,000. Eftersom din obligation har mognat och du kommer att få tillbaka det nominella värdet av obligationen $100,000 + den årliga räntan $5,000 = $105,000.
så det här är de kassaflöden du får under perioden för att hålla ovanstående obligation.
nu har vi beräknat IRR (internal rate of return) eller den effektiva räntan med hjälp av Excels IRR-funktion i cellen C17: =IRR (C11: C16)
IRR-funktionen returnerar den interna avkastningen för en serie kassaflöden.
Syntax för IRR-funktion: IRR (värden, )
som värdena för IRR-funktionen har vi inmatat de periodiska kassaflödena och som gissning passerade vi inget värde eftersom det inte krävs.
och vi får 6.19% som den effektiva räntan på dessa kassaflöden.
så du ser att det inte är svårt att beräkna den effektiva räntan på obligationer som säljs i rabatt.
Läs mer: Hur man beräknar IRR (intern avkastning) i Excel (9 enkla sätt).
exempel 2: Beräkning av effektiv ränta i premieobligationer
i det här exemplet säljer vår obligation med en premie.
här är detaljerna i obligationen:
- nominellt värde: $100,000
- angiven ränta/nominell ränta/kupongränta / APR: 5%
- löptid: 5 år
- Räntebetalningsfrekvens: årlig
- emissionskurs: $105,000 (obligationen säljer med premie)
för denna obligation är emissionskursen $105,000. Så vid år 0 visar vår investering -$105,000.
efter år 1, 2, 3 och 4 kommer obligationsinnehavaren att få räntebetalningar på belopp $5,000. Eftersom obligationens nominella värde är $100,000 och dess årliga nominella ränta är 5%. Så varje år kommer räntebetalningen att vara $100,000 x 5% = $5,000.
i slutet av år 5 kommer obligationen att mogna. Så kommer obligationsinnehavaren att få det nominella värdet och den årliga räntebetalningen = $100,000 + $5,000 = $105,000.
så för denna obligation är det de kassaflöden som obligationsinnehavaren kommer att få för de kommande 5 åren.
vi har använt Excels IRR-funktion för att beräkna internräntan eller effektiv ränta för dessa kassaflöden i cellen C35: =IRR (C29: C34).
och vi får den interna avkastningen eller den effektiva räntan på 3,88%.
så du ser den enorma skillnaden att få en obligation i rabatt och i premium.
men innan du köper en obligation kommer du inte bara att beräkna den effektiva räntan, du kommer också att kolla in obligationens rating (av ett oberoende ratingföretag). Ju högre rating av en obligation, är dina pengar säkrare med företaget.
exempel 3: Beräkning av effektiv ränta när betalningar inte är årliga
i det här exemplet kommer jag att visa dig en obligation som betalar räntebetalningar halvårsvis (två gånger om året), inte årligen. Vissa emittenter av obligationer kan till och med betala månadsvis, kvartalsvis eller vid vilken frekvens som helst.
här är detaljerna i obligationen:
- nominellt värde: $100,000
- angiven ränta/nominell ränta/kupongränta / APR: 5%
- löptid: 5 år
- Räntebetalningsfrekvens: halvårsvis (två gånger om året)
- emissionspris: $95,000 (obligationen säljer på en rabatt)
säg, du köpte obligationen på 1-Jan-2018 och här kommer dina kassaflöden för de kommande 5 åren.
eftersom räntebetalningarna kommer att göras halvårsvis (två gånger om året) måste vi dela upp den angivna räntan/nominella räntan på 5% med 2. Det kommer att returnera 2,5%.
så, våra halvårsräntor kommer att vara $100,000 x 2.5% = $2,500.
och när obligationen kommer att mogna får du $102,500 (nominellt värde + de senaste 6 månadernas ränta).
för att få internräntan eller den effektiva räntan för dessa kassaflöden måste vi använda Excels XIRR-funktion. Eftersom kassaflödena inte är årliga. När kassaflödena tas emot på diskreta sätt måste du använda XIRR-funktionen för att få den interna avkastningen på kassaflödena.
XIRR-funktionen returnerar den interna avkastningen för ett schema över kassaflöden.
Syntax för XIRR-funktion: XIRR (värden, datum, ).
i cellen G22 har jag använt denna formel: = XIRR (G11: G21, F11: F21)
G11: G21 är mottagna kassaflöden och F11: F21 är datum för mottagande av kassaflödena.
och vi får 6.274% effektiv ränta för dessa kassaflöden.
ladda ner Excel-arbetsbok
så det här är min metod för att beräkna den effektiva räntan på obligationer med Excel. Vet du något bättre sätt att göra det? Jag är angelägen om att veta. Vänligen dela dina tankar om den här artikeln i kommentarrutan.
relaterade avläsningar
- effektiv Räntemetod för avskrivningar i Excel
- beräkna avkastning till löptid (YTM) i Excel (enkel formel + Mall)
- effektiv Räntemetod Excel-mall (Gratis)
- effektiv Ränteformel Excel + gratis kalkylator