Índice
- Entendendo sua hipoteca
- Calcular o pagamento mensal
- Calcular a taxa de juros anual
- Determinando a duração de um empréstimo
- Decomposição do empréstimo
- Computação de Empréstimos no Excel
- Amortização do Empréstimo
- Criando uma programação de empréstimo
O reembolso do empréstimo é o ato de devolver dinheiro anteriormente emprestado de um credor, normalmente por meio de uma série de pagamentos periódicos que incluem principal mais juros. Você sabia que pode usar o programa Excel para calcular seus pagamentos de empréstimos?
Este artigo é um guia passo a passo para configurar cálculos de empréstimos.
Principais Takeaways
- Use o Excel para controlar sua hipoteca, determinando seu pagamento mensal, sua taxa de juros e sua programação de empréstimos. Você pode examinar mais detalhadamente a composição de um empréstimo com o Excel e criar uma programação de pagamento que funcione para você Existem cálculos disponíveis para cada etapa que você pode ajustar para atender às suas necessidades específicas. A análise detalhada do seu empréstimo passo a passo pode fazer com que o processo de reembolso pareça menos avassalador e gerenciável.
Entendendo sua hipoteca
Usando o Excel, você pode entender melhor sua hipoteca em três etapas simples. O primeiro passo determina o pagamento mensal. O segundo passo calcula a taxa de juros e o terceiro passo determina o cronograma do empréstimo.
Você pode criar uma tabela no Excel que informará a taxa de juros, o cálculo do empréstimo pela duração do empréstimo, a decomposição do empréstimo, a amortização e o pagamento mensal.
Calcular o pagamento mensal
Primeiro, veja como calcular o pagamento mensal de uma hipoteca. Usando a taxa de juros anual, o principal e a duração, podemos determinar o valor a ser pago mensalmente.
A fórmula, conforme mostrado na captura de tela acima, é escrita da seguinte maneira:
= -PMT (taxa; comprimento; valor_ presente;;)
O sinal de menos na frente do PMT é necessário, pois a fórmula retorna um número negativo. Os três primeiros argumentos são a taxa do empréstimo, a duração do empréstimo (número de períodos) e o principal emprestado. Os dois últimos argumentos são opcionais; o valor residual é padronizado como zero; pago antecipadamente (por um) ou no final (por zero), também é opcional.
A fórmula do Excel usada para calcular o pagamento mensal do empréstimo é:
= -PMT ((1 + B2) ^ (1/12) -1; B4 * 12; B3) = PMT ((1 + 3, 10%) ^ (1/12) -1; 10 * 12; 120000)
Explicação: Para a taxa, usamos a taxa mensal (período da taxa), calculamos o número de períodos (120 por 10 anos multiplicados por 12 meses) e, finalmente, indicamos o principal emprestado. Nosso pagamento mensal será de US $ 1.161, 88 em 10 anos.
Calcular a taxa de juros anual
Vimos como configurar o cálculo de um pagamento mensal por uma hipoteca. Mas podemos querer definir um pagamento mensal máximo que podemos pagar, que também exibe o número de anos durante os quais teríamos que pagar o empréstimo. Por esse motivo, gostaríamos de saber a taxa de juros anual correspondente.
Conforme mostrado na captura de tela acima, primeiro calculamos a taxa do período (mensalmente, no nosso caso) e depois a taxa anual. A fórmula usada será TAXA, conforme mostrado na captura de tela acima. Está escrito da seguinte maneira:
= RATE (Nper; pmt; valor_ presente;;)
Os três primeiros argumentos são a duração do empréstimo (número de períodos), o pagamento mensal para reembolsar o empréstimo e o principal emprestado. Os três últimos argumentos são opcionais e o valor residual é padronizado como zero; o termo argumento para gerenciar o vencimento antecipadamente (para um) ou no final (para zero) também é opcional. Finalmente, o argumento de estimativa é opcional, mas pode fornecer uma estimativa inicial da taxa.
A fórmula do Excel usada para calcular a taxa de empréstimo é:
= TAXA (12 * B4; -B2; B3) = TAXA (12 * 13; -960; 120000)
Nota: os dados correspondentes no pagamento mensal devem receber um sinal negativo. É por isso que há um sinal de menos antes da fórmula. O período da taxa é de 0, 294%.
Usamos a fórmula = (1 + B5) é 12-1 ^ = (1 + 0, 294%) ^ 12-1 para obter a taxa anual de nosso empréstimo, que é de 3, 58%. Em outras palavras, para emprestar US $ 120.000 ao longo de 13 anos para pagar US $ 960 mensais, devemos negociar um empréstimo a uma taxa máxima anual de 3, 58%.
Usar o Excel é uma ótima maneira de acompanhar o que você deve e elaborar uma programação de reembolso que minimize as taxas que você possa acabar pagando.
Determinando a duração de um empréstimo
Agora, veremos como determinar a duração de um empréstimo quando você souber a taxa anual, o principal emprestado e o pagamento mensal a ser reembolsado. Em outras palavras, quanto tempo precisaremos para pagar uma hipoteca de US $ 120.000 com uma taxa de 3, 10% e um pagamento mensal de US $ 1.100?
A fórmula que usaremos é NPER, conforme mostrado na captura de tela acima, e está escrita da seguinte maneira:
= NPER (taxa; pmt; valor_ presente;;)
Os três primeiros argumentos são a taxa anual do empréstimo, o pagamento mensal necessário para reembolsá-lo e o principal emprestado. Os dois últimos argumentos são opcionais, o valor residual é padronizado como zero. O termo argumento pago antecipadamente (para um) ou no final (para zero) também é opcional.
= NPER ((1 + B2) ^ (1/12) -1; -B4; B3) = NPER ((1 + 3, 10%) ^ (1/12) -1; -1100; 120000)
Nota: os dados correspondentes no pagamento mensal devem receber um sinal negativo. É por isso que temos um sinal de menos antes da fórmula. A duração do reembolso é de 127, 97 períodos (meses no nosso caso).
Usaremos a fórmula = B5 / 12 = 127.97 / 12 para o número de anos para concluir o pagamento do empréstimo. Em outras palavras, para emprestar US $ 120.000, com uma taxa anual de 3, 10% e para pagar US $ 1.100 mensais, devemos pagar os vencimentos por 128 meses ou 10 anos e 8 meses.
Decomposição do empréstimo
Um pagamento de empréstimo é composto por principal e juros. Os juros são calculados para cada período - por exemplo, os pagamentos mensais ao longo de 10 anos nos fornecerão 120 períodos.
A tabela acima mostra a composição de um empréstimo (um período total igual a 120) usando as fórmulas PPMT e IPMT. Os argumentos das duas fórmulas são os mesmos e são divididos da seguinte maneira:
= -PPMT (taxa; num_período; comprimento; principal;;)
Os argumentos são os mesmos da fórmula do PMT já vista, exceto "num_period", que é adicionado para mostrar o período durante o qual o empréstimo deve ser dividido, considerando o principal e os juros. Aqui está um exemplo:
= -PPMT ((1 + B2) ^ (1/12) -1; 1; B4 * 12; B3) = PPMT ((1 + 3, 10%) ^ (1/12) -1; 1; 10 * 12; 120000)
O resultado é mostrado na imagem acima "Decomposição do empréstimo" no período analisado, que é "um;" isto é, o primeiro período ou o primeiro mês. Pagamos US $ 1.161, 88, divididos em principal de US $ 856, 20 e juros de US $ 305, 68.
Computação de Empréstimos no Excel
Também é possível calcular o pagamento do principal e juros por vários períodos, como os primeiros 12 meses ou os primeiros 15 meses.
= -CUMPRINC (taxa; comprimento; principal; data_de_início; data_de_envio; tipo)
Encontramos os argumentos, taxa, duração, principal e prazo (que são obrigatórios) que já vimos na primeira parte com a fórmula PMT. Mas aqui também precisamos dos argumentos "start_date" e "end_date". O "start_date" indica o início do período a ser analisado, e o "end_date" indica o final do período a ser analisado.
Aqui está um exemplo:
= -CUMPRINC ((1 + B2) ^ (1/12) -1; B4 * 12; B3; 1; 12; 0)
O resultado é mostrado na captura de tela "1º ano do Cumul", portanto, os períodos analisados variam de um a 12 do primeiro período (primeiro mês) até o décimo segundo (12º mês). Em um ano, pagaríamos US $ 10.419, 55 em principal e US $ 3.522, 99 em juros.
Amortização do Empréstimo
As fórmulas anteriores nos permitem criar nosso cronograma, período por período, para saber quanto pagaremos mensalmente em principal e juros e para saber quanto resta a pagar.
Criando uma programação de empréstimo
Para criar uma programação de empréstimos, usaremos as diferentes fórmulas discutidas acima e as expandiremos ao longo do número de períodos.
Na coluna do primeiro período, digite "1" como o primeiro período e arraste a célula para baixo. No nosso caso, precisamos de 120 períodos, já que um pagamento de empréstimo de 10 anos multiplicado por 12 meses é igual a 120.
A segunda coluna é o valor mensal que precisamos pagar todos os meses - constante durante todo o cronograma de empréstimos. Para calcular a quantidade, insira a seguinte fórmula na célula do nosso primeiro período:
= -PMT (TP-1; B4 * 12; B3) = -PMT ((1 + 3, 10%) ^ (1/12) -1; 10 * 12; 120000)
A terceira coluna é a principal que será paga mensalmente. Por exemplo, para o 40º período, pagaremos US $ 945, 51 em principal em nosso valor total mensal de US $ 1.161, 88.
Para calcular o valor do principal resgatado, usamos a seguinte fórmula:
= -PPMT (TP; A18; $ B $ 4 * 12; $ B $ 3) = -PPMT ((1 + 3, 10%) ^ (1/12); 1; 10 * 12; 120000)
A quarta coluna é os juros, pelos quais usamos a fórmula para calcular o principal pago em nosso valor mensal para descobrir quanto de juros deve ser pago:
= -INTPER (TP; A18; $ B $ 4 * 12; $ B $ 3) = -INTPER ((1 + 3, 10%) ^ (1/12); 1; 10 * 12; 120000)
A quinta coluna contém o valor a ser pago. Por exemplo, após o 40º pagamento, teremos que pagar US $ 83.994, 69 em US $ 120.000.
A fórmula é a seguinte:
= $ B $ 3 + CUMPRINC (TP; $ B $ 4 * 12; $ B $ 3; 1; A18; 0)
A fórmula usa uma combinação de principal em um período antes da célula que contém o principal emprestado. Esse período começa a mudar quando copiamos e arrastamos a célula para baixo. A tabela abaixo mostra que, no final de 120 períodos, nosso empréstimo é reembolsado.
