Cum se calculează rata efectivă a dobânzii la obligațiuni utilizând Excel
în această postare pe blog, voi discuta despre cum se calculează rata efectivă a dobânzii la obligațiuni folosind Excel.
care este rata dobânzii efective?
dacă știți ce este o rată efectivă a dobânzii, puteți sări peste această secțiune. Mergeți la secțiunea următoare.
rata efectivă a dobânzii este rata dobânzii pe care o obțineți sau trebuie să o plătiți efectiv din investiția dvs. sau pentru împrumutul dvs.
spuneți, ați luat un împrumut de 10.000 USD de la o bancă locală cu o rată procentuală anuală de 12%. Banca a spus că dobânda dvs. va fi agravată în fiecare lună.
nu știi ce înseamnă asta. Citiți cu atenție, este important să înțelegeți acest concept.
rata anuală procentuală / rata nominală a dobânzii: 12%
deci, rata lunară a dobânzii: 12%/12 = 1%
la sfârșitul lunii 1: principalul dvs. + dobânda va fi: $ 10,000 +$ 10,000 x 1% = $10,000 (1 + 0.01) = $10100
aici, presupun că nu ați plătit dobânda (100 USD pentru acest caz) pentru prima lună. Deci, pentru a 2-a lună, principalul dvs. va fi de 10.100 USD.
la sfârșitul lunii 2: principalul dvs. + dobânda va fi = $10100 + $10100 x 1% = $10201
ca, vreau să fac o formulă din Declarația de mai sus, asa de, am de gând să reafirme linia de mai sus în felul următor:
= $10100 + $10100 x 1%
= $10100 (1 + 1%)
= $10,000 x (1 + 1%) x (1 + 1%); Ca $10100 = $ 10,000 x (1 + 1%)
= $10,000 x (1 + 1%) ^2; ca (1 + 1%) x (1 + 1%) = (1 + 1%) ^2
= $10,000 x (1 + 0.01) ^ 2
în acest fel, la sfârșitul lunii a 3-a, dobânda principală + va fi: 10.000 USD x (1 + 0.01) ^ 3
… … …
… … …
… … …
în acest fel, după 12 luni, principalul și dobânda dvs. vor fi: 10.000 USD x (1 + 0.01) ^12 = $11268.25
deci, aveți de gând să plătească dobândă totală: ($11268.25 – $10,000) / $10,000 = 12.68%. Dar banca a spus că rata procentuală anuală a fost de 12%.
ceea ce banca a spus a fost rata nominală a dobânzii, dar din cauza dobânzii compuse lunar, plătiți mai mult decât rata nominală a dobânzii. Această rată a dobânzii se numește rata efectivă a dobânzii.
acest articol vă va oferi o idee mai detaliată despre rata nominală și efectivă a dobânzii: Formula nominală a ratei dobânzii Excel (calculați din rata efectivă).
să calculăm acum rata efectivă a dobânzii la obligațiuni.
Exemplul 1: Calcularea dobânzii efective pe obligațiuni cu discount
aveți niște bani în plus și doriți să investească banii într-o obligațiune.
iată detaliile legăturii:
- valoarea nominală: 100.000 $
- rata declarată / rata nominală / rata cuponului / aprilie: 5%
- perioada de scadență: 5 ani
- frecvența plății dobânzii: anual
- prețul emisiunii: 95000 USD (obligațiunea se vinde cu reducere)
dacă doriți să aflați mai multe despre legătură, accesați ACEST link.
calcularea ratei dobânzii efective și calcularea ratei interne de rentabilitate de fapt același lucru. Deci, vă voi arăta cum să calculați rata internă de rentabilitate a obligațiunii de mai sus.
uitați-vă la următoarea imagine.
imaginea este auto-explicativă. Dar permiteți-mi să explic pas cu pas.
în anul 0, investiți 95.000 USD (prețul de emisiune al obligațiunii) pentru a cumpăra obligațiunea. Plătești 95.000 de dolari pentru că obligațiunea se vinde cu reducere.
la sfârșitul anului 1, Primiți 5.000 USD ca plată a dobânzii de la emitentul obligațiunilor. Cum se calculează acești 5.000 de dolari?
valoarea nominală a obligațiunii este de 100.000 USD, iar rata nominală anuală a dobânzii este de 5%. Deci, veți primi plata dobânzii sumei: 100.000 USD x 5% = 100.000 USD x 0,05 = 5.000 USD.
în același mod, după 2, 3, și 4 ani, veți obține plăți de dobânzi de suma $5,000.
dar după sfârșitul anului 5, veți primi $105,000. Deoarece obligațiunea dvs. a ajuns la scadență și veți primi înapoi valoarea nominală a obligațiunii $100,000 + dobânda anuală $5,000 = $105,000.
deci, acestea sunt fluxurile de numerar pe care le obțineți pe perioada deținerii obligațiunii de mai sus.
acum, am calculat IRR (rata internă de rentabilitate) sau rata efectivă a dobânzii folosind funcția IRR Excel în celula C17: =IRR (C11: C16)
funcția IRR returnează rata internă de rentabilitate pentru o serie de fluxuri de numerar.
sintaxa funcției IRR: IRR(valori,)
ca valori ale funcției IRR, am introdus fluxurile de numerar periodice și, ca presupunere, nu am trecut nicio valoare, deoarece nu este necesară.
și obținem 6,19% ca rată efectivă a acestor fluxuri de numerar.
Deci, vedeți că nu este greu să calculați rata efectivă a obligațiunilor care sunt vândute cu discount.
citiți mai multe: cum se calculează IRR (rata internă de rentabilitate) în Excel (9 moduri ușoare).
Exemplul 2: calcularea dobânzii efective pentru obligațiunile premium
în acest exemplu, obligațiunile noastre se vând cu o primă.
iată detaliile legăturii:
- valoarea nominală: 100.000 $
- rata declarată / rata nominală / rata cuponului / aprilie: 5%
- perioada de scadență: 5 ani
- frecvența plății dobânzii: anual
- prețul emisiunii: 105.000 USD (obligațiunea se vinde cu premium)
pentru această obligațiune, prețul de emisiune este de 105.000 USD. Deci, la anul 0, investiția noastră arată – $ 105,000.
după anul 1, 2, 3 și 4, deținătorul de obligațiuni va primi plăți de dobânzi în valoare de 5.000 USD. Deoarece valoarea nominală a obligațiunii este de 100.000 USD, iar rata nominală anuală a dobânzii este de 5%. Deci, în fiecare an plata dobânzii va fi de 100.000 USD x 5% = 5.000 USD.
la sfârșitul anului 5, obligațiunea se va maturiza. Deci, deținătorul obligațiunii va primi valoarea nominală și plata anuală a dobânzii = $100,000 + $5,000 = $105,000.
deci, pentru această obligațiune, acestea sunt fluxurile de numerar pe care deținătorul obligațiunii le va primi în următorii 5 ani.
am folosit funcția IRR Excel pentru a calcula rata internă de rentabilitate sau rata dobânzii efective pentru aceste fluxuri de numerar în celula C35: = IRR (C29: C34).
și obținem rata internă de rentabilitate sau rata efectivă a dobânzii de 3,88%.
Deci, vedeți diferența uriașă de a obține o legătură în reducere și în primă.
dar înainte de a cumpăra o obligațiune, nu veți calcula doar rata efectivă a dobânzii, veți verifica și ratingul obligațiunii (de către o companie independentă de rating). Cu cât este mai mare ratingul unei obligațiuni, banii dvs. sunt mai siguri cu compania.
Exemplul 3: Calcularea dobânzii efective atunci când plățile nu sunt anuale
în acest exemplu, vă voi arăta o obligațiune care plătește plăți de dobânzi semestrial (de două ori pe an), nu anual. Unii emitenți de obligațiuni ar putea plăti chiar lunar, trimestrial sau la orice frecvență.
iată detaliile legăturii:
- valoarea nominală: 100.000 $
- rata declarată / rata nominală / rata cuponului / aprilie: 5%
- perioada de scadență: 5 ani
- frecvența plății dobânzii: semestrial (de două ori pe an)
- prețul de emisiune: 95.000 USD (obligațiunea se vinde cu reducere)
spune, Ai cumpărat obligațiunea pe 1-Jan-2018 și aici vor fi fluxurile de numerar pentru următorii 5 ani.
deoarece plățile dobânzilor se vor face semestrial (de două ori pe an), trebuie să împărțim rata declarată/rata nominală a dobânzii de 5% la 2. Aceasta va returna 2,5%.
deci, plățile noastre de dobânzi semestriale vor fi de 100.000 USD x 2,5% = 2.500 USD.
și când obligațiunea se va maturiza, veți primi 102.500 USD (valoarea nominală + dobânda ultimelor 6 luni).
pentru a obține randamentul ratei interne sau rata efectivă a acestor fluxuri de numerar, trebuie să folosim funcția XIRR Excel. Deoarece fluxurile de numerar nu sunt anuale. Când fluxurile de numerar sunt primite în moduri discrete, trebuie să utilizați funcția XIRR pentru a obține rata internă de rentabilitate a fluxurilor de numerar.
funcția XIRR returnează rata internă de rentabilitate pentru un program de fluxuri de numerar.
sintaxa funcției XIRR: XIRR (valori, date, ).
în celula G22, am folosit această formulă: =XIRR (G11: G21, F11: F21)
G11: G21 sunt fluxuri de numerar primite, iar F11: F21 sunt datele primirii fluxurilor de numerar.
și obținem 6.274% rata dobânzii efective pentru aceste fluxuri de numerar.
descărcați Excel Workbook
deci, acestea sunt metoda mea de a calcula rata efectivă a dobânzii la obligațiuni folosind Excel. Știi vreo modalitate mai bună de a face acest lucru? Sunt nerăbdător să știu. Vă rugăm să vă împărtășiți gândurile despre acest articol în caseta de comentarii.
lecturi conexe
- metoda dobânzii efective de amortizare în Excel
- calculați randamentul până la scadență (YTM) în Excel (formulă simplă + șablon)
- metoda ratei dobânzii efective Excel șablon (gratuit)
- Formula ratei dobânzii efective Excel + Calculator gratuit