Programar reembolsos de empréstimos com fórmulas do Excel

O reembolso do empréstimo é o ato de devolver o dinheiro emprestado anteriormente de um credor, normalmente através 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 analisar mais detalhadamente a repartição de um empréstimo com o Excel e criar um cronograma 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.
• Dividir e examinar seu empréstimo passo a passo pode fazer com que o processo de reembolso pareça menos esmagador e mais 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, como mostra a captura de tela acima, é escrita da seguinte maneira:

= -PMT (taxa; comprimento; valor_ presente; [valor_ futuro]; [tipo])

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 possamos pagar, que também exiba 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; [valor_ futuro]; [tipo])

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 antecipadamente o vencimento (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%.

Utilizamos 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, por quanto tempo precisaremos 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; [valor_ futuro]; [tipo])

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; [residual]; [termo])

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 captura de tela 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_inicial; data_inferior; tipo)

Encontramos os argumentos, taxa, comprimento, 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, insira "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.