Como calcular a taxa efetiva de juros sobre os títulos usando o Excel
neste post do blog, vou discutir como calcular a taxa efetiva de juros sobre os títulos usando o Excel.
Qual é a taxa de juros efetiva?
se você sabe o que é uma taxa de juros efetiva, você pode pular esta seção. Vá para a próxima seção.
a taxa de juros efetiva é a taxa de juros que você obtém ou tem que pagar realmente com seu investimento ou pelo seu empréstimo.
digamos, você tomou um empréstimo de valor $10.000 de um banco local com uma taxa percentual anual de 12%. O banco disse que seu interesse será agravado a cada mês.
você não sabe o que isso significa. Leia atentamente, é importante entender esse conceito.
taxa percentual anual/taxa de juros Nominal: 12%
assim, taxa de juros mensal: 12%/12 = 1%
no final do mês 1: Seu Principal + interesse será: $10.000 + $10.000 x 1% = $10,000 (1 + 0.01) = $10100
aqui, estou assumindo que você não pagou os juros (US$100 para este caso) para o primeiro mês. Então, para o segundo mês, Seu principal será de US $10.100.
No final do Mês 2: Principal + Juros = $10100 + $10100 x 1% = $10201
Como, eu quero fazer uma fórmula a partir da afirmação acima, então, eu estou indo para refaça a linha acima na seguinte forma:
= $10100 + $10100 x 1%
= $10100 (1 + 1%)
= $10,000 x (1 + 1%) x (1 + 1%); Como $10100 = r $10.000 x (1 + 1%)
= $10,000 x (1 + 1%) ^2; Como (1 + 1%) x (1 + 1%) = (1 + 1%) ^2
= $10,000 x (1 + 0.01) ^ 2
desta forma, no final do 3º mês, o seu Principal + Juros será de: R $10.000 x (1 + 0.01) ^ 3
… … …
… … …
… … …
desta forma, após 12 meses, o Principal e Juros será de: us $10.000 x (1 + 0.01) ^12 = $11268.25
Assim, você está indo para pagar o total de juros: ($11268.25 – $10,000) / $10,000 = 12.68%. Mas o banco disse que sua taxa percentual anual era de 12%.
o que o banco disse foi sua taxa de juros Nominal, mas devido a juros compostos mensais, você está pagando mais do que a taxa de juros nominal. Essa taxa de juros é chamada de taxa de juros efetiva.
este artigo lhe dará uma idéia mais detalhada sobre a taxa de juros nominal e efetiva: fórmula de taxa de juros Nominal Excel (calcular a partir da taxa efetiva).
vamos agora calcular a taxa de juros efetiva sobre títulos.
exemplo 1: Calculando juros efetivos sobre títulos com desconto
você tem algum dinheiro extra e deseja investir seu dinheiro em um título.
Aqui estão os detalhes da obrigação:
- Valor nominal: Us $100.000
- Estipulado Rate/Taxa Nominal/Taxa de Cupom/ABR: 5%
- Período de Vencimento: 5 anos
- Pagamento de Juros de Frequência: Anual
- Problema de Preço: $95000 (a obrigação é a venda de um desconto de)
Se você quiser saber mais sobre o bond, acesse através deste link.
Calculando a taxa de juros efetiva e calculando a taxa interna de retorno, na verdade, a mesma coisa. Então, mostrarei como calcular a taxa interna de retorno do título acima.
dê uma olhada na imagem a seguir.
A imagem é auto-explicativa. Mas deixe-me explicar isso passo a passo.
no ano 0, você está investindo $95.000 (preço de emissão do título) para comprar o título. Você está pagando US $ 95.000 porque o título está vendendo com desconto.
no final do 1º ano, você está recebendo $5.000 como pagamento de juros do emissor do título. Como isso $5.000 é calculado?
o valor nominal do título é de US $100.000 e a taxa de juros nominal anual é de 5%. Assim, você receberá o pagamento de juros do valor: $100.000 x 5% = $100.000 x 0,05 = $5.000.
da mesma forma, após 2º, 3º e 4º anos, você receberá os pagamentos de juros do valor de $5.000.
mas após o final do 5º ano, você receberá $105.000. Porque seu título amadureceu e você receberá de volta o valor nominal do título $100.000 + os juros anuais $5.000 = $105.000.
portanto, esses são os fluxos de caixa que você obtém durante o período de retenção do título acima.
agora, calculamos a TIR (Taxa Interna de retorno) Ou a taxa de juros efetiva usando a função de tir do Excel na célula C17: =TIR (C11: C16)
a função de TIR Retorna a taxa interna de retorno para uma série de fluxos de caixa.
sintaxe da função IRR: IRR (valores, )
como os valores da função IRR, inserimos os fluxos de caixa periódicos e, como o palpite, não passamos nenhum valor, pois não é necessário.
e obtemos 6,19% como a taxa efetiva desses fluxos de caixa.
então, você vê que não é difícil calcular a taxa efetiva de títulos que são vendidos com desconto.
leia mais: como calcular a TIR (Taxa Interna de retorno) no Excel (9 maneiras fáceis).
Exemplo 2: calculando juros efetivos em títulos premium
neste exemplo, nosso título está sendo vendido com um prêmio.
Aqui estão os detalhes da obrigação:
- Valor nominal: Us $100.000
- Estipulado Rate/Taxa Nominal/Taxa de Cupom/ABR: 5%
- Período de Vencimento: 5 anos
- Pagamento de Juros de Frequência: Anual
- Problema de Preço: $105,000 (a obrigação é a venda de premium)
Por este vínculo, o preço de emissão é de r $105,000. Então, no ano 0, Nosso investimento está mostrando – $105.000.
após o ano 1, 2, 3 e 4, o detentor do título receberá pagamentos de juros no valor de $5.000. Porque o valor nominal do título é de US $100.000 e sua taxa de juros nominal anual é de 5%. Assim, todos os anos, o pagamento de juros será de US $100.000 x 5% = US $ 5.000.
no final do ano 5, o vínculo amadurecerá. Assim, o titular do título receberá o valor nominal e o pagamento anual de juros= $100,000 + $5,000 = $105,000.
portanto, para esse título, esses são os fluxos de caixa que o detentor do título receberá pelos próximos 5 anos.
usamos a função IRR do Excel para calcular a taxa interna de retorno Ou taxa de juros efetiva para esses fluxos de caixa na célula C35: = IRR (C29: C34).
e obtemos a taxa interna de retorno ou a taxa efetiva de juros de 3,88%.
então, você vê a enorme diferença de obter um título com desconto e em prêmio.
mas antes de comprar um título, você não apenas calculará a taxa de juros efetiva, mas também verificará a classificação do título (por uma empresa de classificação independente). Quanto maior a classificação de um título, seu dinheiro é mais seguro com a empresa.
exemplo 3: Calculando juros efetivos quando os pagamentos não são anuais
neste exemplo, mostrarei a você um título que paga pagamentos de juros semestralmente (duas vezes por ano), não anualmente. Alguns emissores de títulos podem até pagar mensalmente, trimestralmente ou em qualquer frequência.
Aqui estão os detalhes da obrigação:
- Valor nominal: Us $100.000
- Estipulado Rate/Taxa Nominal/Taxa de Cupom/ABR: 5%
- Período de Vencimento: 5 anos
- Interesse Frequência de Pagamento: semestralmente (duas vezes por ano)
- Preço de Emissão: $95.000 (o título está vendendo com desconto)
digamos, você comprou o título em 1-Jan-2018 e aqui estarão seus fluxos de caixa para os próximos 5 anos.
Como o pagamento de juros será feito semestralmente (duas vezes por ano), temos que dividir a indicado/taxa nominal taxa de juros de 5% por 2. Isso retornará 2,5%.Portanto, nossos pagamentos de juros semestrais serão de US $100.000 x 2,5% = US $ 2.500.
e quando o título amadurecer, você receberá $102.500 (valor nominal + juros dos últimos 6 meses).
para obter o retorno da taxa interna ou a taxa efetiva desses fluxos de Caixa, temos que usar a função XIRR do Excel. Porque os fluxos de caixa não são anuais. Quando os fluxos de caixa são recebidos de maneiras discretas, você deve usar a função XIRR para obter a taxa interna de retorno dos fluxos de caixa.
a função XIRR retorna a taxa interna de retorno para um cronograma de fluxos de caixa.
sintaxe da função XIRR: XIRR (valores, datas ).
na célula G22, usei esta fórmula: =XIRR (G11: G21, F11: F21)
G11: G21 são fluxos de caixa recebidos e F11: F21 são as datas de recebimento dos fluxos de caixa.
e obtemos uma taxa de juros efetiva de 6,274% para esses fluxos de caixa.
baixar pasta de trabalho do Excel
então, este é o meu método para calcular a taxa de juros efetiva em títulos usando o Excel. Você conhece alguma maneira melhor de fazer isso? Estou ansioso para saber. Por favor, compartilhe seus pensamentos sobre este artigo na caixa de comentários.
Leituras Relacionadas
- Método de Juros Efetivos de Amortização em Excel
- Calcular o rendimento até à maturidade (YTM) no Excel (fórmulas simples + modelo)
- Taxa de Juro Efectiva o Método de Modelo do Excel (Gratuito)
- Taxa de Juros Efetiva Fórmula Excel + Calculadora Gratuita