Agendar pagamentos de empréstimos com fórmulas do Excel
O reembolso do empréstimo é o ato de devolver o dinheiro anteriormente emprestado de um credor, normalmente por meio de uma série de pagamentos periódicos que incluem o principal mais os juros. Você sabia que pode usar o programa de software Excel para calcular o pagamento do empréstimo?
Este artigo é um guia passo a passo para configurar cálculos de empréstimos.
Principais vantagens:
- Use o Excel para controlar sua hipoteca, determinando seu pagamento mensal, sua taxa de juros e seu cronograma de empréstimo.
- Você pode dar uma olhada mais aprofundada na composiçã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 tornar o processo de reembolso menos opressor e mais gerenciável.
Compreendendo sua hipoteca
Usando o Excel, você pode obter uma melhor compreensão de sua hipoteca em três etapas simples. A primeira etapa determina o pagamento mensal. A segunda etapa calcula a taxa de juros e a terceira etapa determina o cronograma do empréstimo.
Você pode construir uma tabela no Excel que informará a taxa de juros, o cálculo do empréstimo para a 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_atual; [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 é zero; pago antecipadamente (para um) ou no final (para 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), depois calculamos o número de períodos (120 para 10 anos multiplicado por 12 meses) e, por fim, indicamos o principal emprestado. Nosso pagamento mensal será de $ 1.161,88 em 10 anos.
Calcule a taxa de juros anual
Vimos como configurar o cálculo do pagamento mensal de uma hipoteca. Mas podemos definir um pagamento mensal máximo que podemos pagar, que também exiba o número de anos ao longo dos 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 (mensal, no nosso caso) e, em seguida, a taxa anual. A fórmula utilizada será TAXA, conforme mostrado na imagem acima. Está escrito da seguinte forma:
= TAXA (Nper; pmt; valor_atual; [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 últimos três argumentos são opcionais e o valor residual é zero; o termo argumento para administrar o vencimento antecipado (para um) ou no final (para zero) também é opcional. Finalmente, o argumento da 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 na mensalidade devem ter sinal negativo. É por isso que existe 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 do nosso empréstimo, que é 3,58%. Em outras palavras, para emprestar $ 120.000 ao longo de 13 anos para pagar $ 960 mensais, devemos negociar um empréstimo a uma taxa máxima anual de 3,58%.
Usar o Excel é uma ótima maneira de controlar o que você deve e de definir um cronograma de pagamento que minimize quaisquer taxas que você possa acabar devendo.
Determinando a Duração de um Empréstimo
Veremos agora como determinar a duração de um empréstimo quando você conhece a taxa anual, o principal emprestado e o pagamento mensal a ser reembolsado. Em outras palavras, quanto tempo precisaremos pagar uma hipoteca de $ 120.000 com uma taxa de 3,10% e um pagamento mensal de $ 1.100?
A fórmula que usaremos é NPER, conforme mostrado na imagem acima, e é escrita da seguinte maneira:
= NPER (taxa; pmt; valor_atual; [valor_futuro]; [tipo])
Os três primeiros argumentos são a taxa anual do empréstimo, o pagamento mensal necessário para pagar o empréstimo e o principal emprestado. Os dois últimos argumentos são opcionais, o valor residual padrão é zero. O termo argumento pagável 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)
Sinal de menos antes da fórmula
Os dados correspondentes na mensalidade 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 completar a amortização do empréstimo. Em outras palavras, para tomar emprestado $ 120.000, a uma taxa anual de 3,10% e pagar $ 1.100 mensalmente, devemos pagar vencimentos em 128 meses ou 10 anos e oito meses.
Decompondo o empréstimo
O pagamento do empréstimo é composto de principal e juros. Os juros são calculados para cada período – por exemplo, os pagamentos mensais ao longo de 10 anos nos darã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 iguais e estão divididos da seguinte forma:
= -PPMT (taxa; núm_período; comprimento; principal; [residual]; [prazo])
Os argumentos são os mesmos da fórmula PMT já vista, exceto por “núm_período”, que é adicionado para mostrar o período durante o qual dividir o empréstimo com base no principal e 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” ao longo do período analisado, que é “um;” ou seja, o primeiro período ou o primeiro mês. Pagamos $ 1.161,88 dividido em $ 856,20 do principal e $ 305,68 de juros.
Computação de Empréstimo em Excel
Também é possível calcular o reembolso do principal e juros para vários períodos, como os primeiros 12 meses ou os primeiros 15 meses.
= -CUMPRINC (taxa; comprimento; principal; data_início; data_final; tipo)
Encontramos os argumentos, taxa, comprimento, principal e termo (que são obrigatórios) que já vimos na primeira parte com a fórmula PMT. Mas aqui, precisamos dos argumentos “start_date” e “end_date” também. A “data_início” indica o início do período a ser analisado, e a “data_termino” 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 “Cumul 1º ano”, portanto os períodos analisados variam de um a 12 do primeiro período (primeiro mês) ao décimo segundo (12º mês). Ao longo de um ano, pagaríamos $ 10.419,55 de principal e $ 3.522,99 de juros.
Amortização do Empréstimo
As fórmulas anteriores nos permitem criar nossa programação período a período, saber quanto vamos pagar mensalmente de principal e juros, e saber quanto falta pagar.
Criação de um cronograma 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 primeira coluna do período, insira “1” como o primeiro período e arraste a célula para baixo. Em nosso caso, precisamos de 120 períodos, pois um pagamento de empréstimo de 10 anos multiplicado por 12 meses é igual a 120.
A segunda coluna é o valor mensal que precisamos pagar a cada mês – que é constante ao longo de todo o cronograma de empréstimo. Para calcular o valor, insira a seguinte fórmula na célula do nosso primeiro período:
= -PMT (TP; B4 * 12; B3) = -PMT ((1 + 3,10%) ^ (1/12) -1; 10 * 12; 120000)
A terceira coluna é o principal que será reembolsado mensalmente. Por exemplo, para o 40º período, reembolsaremos $ 945,51 em principal sobre nosso valor total mensal de $ 1.161,88.
Para calcular o valor 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 são os juros, para os quais usamos a fórmula para calcular o principal reembolsado 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 que falta pagar. Por exemplo, após o 40º pagamento, teremos que pagar $ 83.994,69 em $ 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. Este período começa a mudar quando copiamos e arrastamos a célula para baixo. A tabela a seguir mostra que ao final de 120 períodos, nosso empréstimo está quitado.