Cómo calcular la tasa de interés efectiva de los bonos utilizando Excel
En esta publicación de blog, analizaré cómo calcular la tasa de interés efectiva de los bonos utilizando Excel.
¿Cuál es la tasa de interés efectiva?
Si sabe qué es una tasa de interés efectiva, puede omitir esta sección. Vaya a la siguiente sección.
La tasa de interés efectiva es la tasa de interés que obtiene o tiene que pagar realmente de su inversión o de su préstamo.
Digamos que tomó un préstamo de amount 10,000 de un banco local con una tasa porcentual anual del 12%. El banco dijo que sus intereses se incrementarán cada mes.
No sabes lo que eso significa. Lea atentamente, es importante entender este concepto.
Tasa Porcentual Anual / Tasa de Interés Nominal: 12%
So, tasa de interés mensual: 12%/12 = 1%
Al final del Mes 1: su Principal + Interés será: 1 10,000 + x 10,000 x 1% = $10,000 (1 + 0.01) = $10100
Aquí, estoy asumiendo que usted no pagó los intereses ($100 para este caso) durante el primer mes. Por lo tanto, para el segundo mes, su director será de 1 10,100.
al final de Mes 2: su Principal + Intereses = $10100 + $10100 x 1% = $10201
Como, quiero hacer una fórmula a partir de la declaración de arriba, así que, voy a reformular la línea anterior de la siguiente manera:
= $10100 + $10100 x 1%
= $10100 (1 + 1%)
= $10,000 x (1 + 1%) x (1 + 1%); Como $10100 = $10.000 x (1 + 1%)
= $10,000 x (1 + 1%) ^2; (1 + 1%) x (1 + 1%) = (1 + 1%) ^2
= $10,000 x (1 + 0.01) ^ 2
De esta manera, al final del 3er mes, su Principal + Intereses será: $10.000 x (1 + 0.01) ^ 3
… … …
… … …
… … …
De esta manera, después de 12 meses, su capital y los Intereses serán: $10.000 x (1 + 0.01) ^12 = $11268.25
Así, usted va a pagar el total de intereses: ($11268.25 – $10,000) / $10,000 = 12.68%. Pero el banco dijo que su Tasa de Porcentaje Anual era del 12%.
Lo que el banco dijo fue su Tasa de Interés Nominal, pero debido al interés compuesto mensual, está pagando más que la tasa de interés nominal. Esta tasa de interés se denomina Tasa de Interés Efectiva.
Este artículo le dará una idea más detallada sobre la tasa de interés nominal y efectiva: Fórmula de tasa de interés nominal Excel (calcule a partir de la tasa efectiva).
Calculemos ahora la tasa de interés efectiva de los bonos.
Ejemplo 1: Cálculo del interés efectivo sobre bonos con descuento
Usted tiene algo de dinero extra y desea invertir su dinero en un bono.
Aquí están los detalles del bono:
- Valor nominal:
- Tasa Establecida / Tasa Nominal / Tasa de Cupón / TAE: 5%
- Período de vencimiento: 5 años
- Frecuencia de pago de intereses: Anual
- Precio de emisión: $95000 (el bono se vende con descuento)
Si quieres saber más sobre el vínculo, ve a través de este enlace.
Calcular la tasa de interés Efectiva y calcular la Tasa Interna de Rendimiento en realidad lo mismo. Por lo tanto, le mostraré cómo calcular la Tasa Interna de Retorno del bono anterior.
Eche un vistazo a la siguiente imagen.
La imagen se explica por sí misma. Pero déjame explicarlo paso a paso.
En el año 0, está invirtiendo 9 95,000 (precio de emisión del bono) para comprar el bono. Estás pagando 9 95,000 porque el bono se vende con descuento.
Al final del primer año, recibirá 5 5,000 como pago de intereses del emisor del bono. ¿Cómo se calculan estos 5 5.000?
El valor nominal del bono es de 1 100,000 y la tasa de interés nominal anual es del 5%. Así, usted recibirá el pago de intereses de la cantidad: $100.000 x 5% = $100.000 x 0.05 = $5,000.
De la misma manera, después del 2do, 3ro y 4to año, obtendrá los pagos de intereses de la cantidad de 5 5,000.
Pero después del final del quinto año, recibirá $105,000. Debido a que su relación ha madurado y que se recupere el valor nominal del bono de $100.000 + interés anual de $5,000 = $105,000.
Por lo tanto, estos son los flujos de efectivo que obtiene durante el período de tenencia del bono anterior.
Ahora, hemos calculado la TIR (tasa de retorno interna) o la Tasa de Interés Efectiva utilizando la función TIR de Excel en la celda C17: =TIR (C11: C16)
La función TIR devuelve la tasa de retorno interna para una serie de flujos de efectivo.
Sintaxis de la función IRR: IRR (valores, )
Como los valores de la función IRR, hemos ingresado los flujos de efectivo periódicos y, como conjetura, no pasamos ningún valor, ya que no es necesario.
Y obtenemos el 6,19% como la tasa efectiva de estos flujos de efectivo.
Por lo tanto, verá que no es difícil calcular la tasa efectiva de los bonos que se venden con descuento.
Más información: Cómo calcular la tasa de retorno interna (TIR) en Excel (9 formas sencillas).
Ejemplo 2: Cálculo del interés efectivo en bonos premium
En este ejemplo, nuestro bono se vende con una prima.
Aquí están los detalles del bono:
- Valor nominal:
- Tasa Establecida / Tasa Nominal / Tasa de Cupón / TAE: 5%
- Período de vencimiento: 5 años
- Frecuencia de pago de intereses: Anual
- Precio de emisión: $105,000 (el bono se vende con prima)
Para este bono, el precio de emisión es de 1 105,000. Por lo tanto, en el año 0, nuestra inversión es de showing 105,000.
Después de los años 1, 2, 3 y 4, el tenedor de bonos recibirá pagos de intereses por un monto de 5 5,000. Porque el valor nominal del bono es de 1 100,000 y su tasa de interés nominal anual es del 5%. Por lo tanto, cada año el pago de intereses será de 1 100,000 x 5% = 5 5,000.
Al final del año 5, el bono vencerá. Por lo tanto, el titular del bono obtendrá la cantidad del valor nominal y el pago de intereses anual= $100,000 + $5,000 = $105,000.
Por lo tanto, para este bono, estos son los flujos de efectivo que el titular del bono recibirá durante los próximos 5 años.
Hemos utilizado la función TIR de Excel para calcular la tasa interna de rendimiento o la tasa de interés efectiva para estos flujos de efectivo en la celda C35: = TIR (C29: C34).
Y obtenemos la tasa de rendimiento interna o la tasa de interés efectiva del 3,88%.
Por lo tanto, puede ver la gran diferencia de obtener un bono en descuento y en prima.
Pero antes de comprar un bono, no solo calculará la tasa de interés efectiva, sino que también comprobará la calificación del bono (de una compañía de calificación independiente). Cuanto mayor sea la calificación de un bono, su dinero estará más seguro con la compañía.
Ejemplo 3: Cálculo de intereses efectivos cuando los pagos no son anuales
En este ejemplo, le mostraré un bono que paga pagos de intereses semestrales (dos veces al año), no anuales. Algunos emisores de bonos podrían incluso pagar mensualmente, trimestralmente o con cualquier frecuencia.
Aquí están los detalles del bono:
- Valor nominal: $100,000
- Tasa Establecida / Tasa Nominal / Tasa de Cupón / TAE: 5%
- Período de vencimiento: 5 años
- Frecuencia de pago de intereses: Semestral (dos veces al año)
- Precio de emisión: $95,000 (el bono se vende con descuento)
Digamos que compró el bono el 1 de enero de 2018 y aquí estarán sus flujos de efectivo para los próximos 5 años.
Como los pagos de intereses se realizarán semestralmente (dos veces al año), tenemos que dividir la tasa de interés establecida/la tasa de interés nominal del 5% por 2. Eso devolverá un 2,5%.
Por lo tanto, nuestros pagos de intereses semestrales serán de $100,000 x 2.5% = $2,500.
Y cuando el bono venza, obtendrá $102,500 (valor nominal + el interés de los últimos 6 meses).
Para obtener el retorno de la tasa interna o la tasa efectiva de estos flujos de efectivo, tenemos que usar la función XIRR de Excel. Porque los flujos de efectivo no son anuales. Cuando los flujos de efectivo se reciben de manera discreta, debe usar la función XIRR para obtener la tasa interna de retorno de los flujos de efectivo.
La función XIRR devuelve la tasa de retorno interna para una programación de flujos de efectivo.
Sintaxis de la función XIRR: XIRR (valores, fechas,).
En la celda G22, he utilizado esta fórmula: = XIRR (G11: G21, F11: F21)
G11: G21 son flujos de efectivo recibidos, y F11: F21 son las fechas de recepción de los flujos de efectivo.
Y obtenemos una tasa de interés efectiva del 6,274% para estos flujos de efectivo.
Descargar el libro de Excel
Por lo tanto, este es mi método para calcular la tasa de interés efectiva de los bonos utilizando Excel. ¿Conoces alguna forma mejor de hacerlo? Estoy ansioso por saberlo. Por favor, comparta sus pensamientos sobre este artículo en el cuadro de comentarios.
Lecturas relacionadas
- Método de Amortización de Intereses Efectivos en Excel
- Calcular el rendimiento hasta el vencimiento (YTM) en Excel (fórmula simple + plantilla)
- Método de Tasa de Interés Efectiva Plantilla de Excel (Gratuita)
- Fórmula de Tasa de interés efectiva Excel + Calculadora gratuita