Você sabia que você pode usar o Excel para calcular seus reembolsos de empréstimos? Este artigo irá acompanhá-lo através de todas as etapas necessárias para fazê-lo. (Veja também: Calculadoras de hipoteca: como funcionam .)

Usando o Excel, você pode obter uma melhor compreensão da sua hipoteca em três etapas simples. O primeiro passo é determinar o pagamento mensal. O segundo é descobrir a taxa de juros, e o terceiro é encontrar o cronograma do empréstimo. Para fazer isso, você pode construir uma tabela no Excel que irá dizer-lhe: as taxas iniciais; o cálculo do empréstimo para a duração; decomposição de um empréstimo, bem como amortização e cálculo de aluguel mensal.

Cálculo do empréstimo para aluguel mensal

Primeiro, vejamos como implementar o cálculo de um pagamento mensal de uma hipoteca. Em outras palavras, usando a taxa anual Iinterest, o principal e a duração, podemos determinar o valor a ser reembolsado mensalmente.

A fórmula, como mostrado na captura de tela acima, é escrita da seguinte forma:

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

O sinal 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 do valor residual para 0, pagável antecipadamente (para 1) ou no final (para 0), 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 o período de taxa, que é a taxa mensal, então calculamos o número de períodos (meses aqui 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.

Cálculo de hipoteca para taxas de juros

Vimos como configurar o cálculo de um pagamento mensal de uma hipoteca. Mas podemos querer definir um pagamento mensal máximo que podemos pagar, que também mostra o número de anos sobre os quais teríamos que reembolsá-lo. Por essa razão, gostaríamos de saber a taxa de juros anual correspondente.

Informando a Taxa de Interesse para um Empréstimo

Conforme mostrado na captura de tela acima, primeiro calculamos a taxa do período (mensalmente no nosso caso) e, em seguida, a taxa anual. A fórmula utilizada será RATE, como mostrado na captura de tela acima, está escrito da seguinte forma:

= RATE (Nper; pmt; present_value; [future_value]; [type])

Os três primeiros argumentos são o comprimento de o empréstimo (número de períodos) eo pagamento mensal para reembolsar o principal emprestado. Os últimos três argumentos são opcionais, e o valor residual padrão para 0, o termo argumento para gerenciar a maturidade com antecedência (para 1) ou no final (para 0) também é opcional e, finalmente, o argumento de estimativa é opcional, mas pode Dê uma estimativa inicial da taxa.

A fórmula do Excel usada para calcular a taxa de empréstimo é:

= RATE (12 * B4; -B2; B3) = RATE (12 * 13; -960; 120000)

Nota: os dados correspondentes em o pagamento mensal deve receber um sinal negativo. É por isso que um sinal de menos antes da fórmula. Nosso período de taxas é 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 para ser 3. 58 %. Em outras palavras, para emprestar 120 000 $ ao longo de 13 anos para pagar mensalmente 960 $, devemos negociar um empréstimo a uma taxa anual de 3. 58% máxima.

Cálculo de hipoteca para o comprimento de um empréstimo

Agora veremos como obter o prazo de um empréstimo quando você conhece a taxa anual, o principal emprestado e o pagamento mensal a ser reembolsado. Em outras palavras, por quanto tempo precisamos reembolsar uma hipoteca de US $ 120 000 com uma taxa de 3. 10% e um pagamento mensal de $ 1, 100?

Número de reembolsos para um empréstimo

A fórmula que usamos é NPER, conforme mostrado na captura de tela acima, e está escrito da seguinte forma:

= NPER (rate; pmt; present_value; [future_value]; [tipo])

Os três primeiros argumentos são a taxa anual do empréstimo, o pagamento mensal necessário para reembolsar o empréstimo e o principal emprestado. Os dois últimos argumentos são opcionais, o valor residual padrão para 0, o termo argumento pagável antecipadamente (para 1) ou no final (para 0) 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. O prazo de 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 o reembolso do empréstimo. Em outras palavras, para emprestar US $ 120.000, com uma taxa anual de 3. 10% e pagar US $ 1, 100 mensalmente, devemos reembolsar prazos por 128 meses ou 10 anos e 8 meses.

Descompactando o empréstimo

Um pagamento de empréstimo consiste em duas coisas, o principal e os juros. O interesse é calculado para cada período, por exemplo, os reembolsos mensais ao longo de 10 anos, nos dará 120 períodos.

A captura de tela acima mostra a repartiçã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 forma:

= - PPMT (taxa; num_period; comprimento; principal; [residual]; [terme])

= - INTPER (taxa; num_period; comprimento; principal; [residual]; [terme])

Os argumentos são os mesmos que para a fórmula PMT vista na primeira parte, com exceção de num_period que é adicionado para mostrar o período sobre o qual dividir o empréstimo, dando o principal e o interesse para isso. Vamos dar um exemplo:

= - PPMT ((1 + B2) ^ (1/12) -1; 1; B4 * 12; B3) = PPMT ((1 + 3, 10%) ^ (1/12 ) -1; 1; 10 * 12; 120000)

= - INTPER ((1 + B2) ^ (1/12) -1; 1; B4 * 12; B3) = INTPER ((1 + 3, 10 %) ^ (1/12) -1; 1; 10 * 12; 120000)

O resultado é o mostrado na captura de tela "Decomposição do empréstimo", no período analisado, que é "1", então o primeiro período , ou o primeiro mês.Para este, pagamos US $ 1161. 88, dividido em US $ 856, 20 principais e US $ 305. 68 interesse.

Excel Loan Computation

Agora também é possível calcular o principal e o reembolso de juros por vários períodos, como os primeiros 12 meses ou os primeiros 15 meses.

= - CUMPRINC (taxa, comprimento, principal, início_data, end_date, tipo)

= - CUMIPMT (taxa; comprimento; principal; start_date; end_date; tipo)

Encontramos os argumentos, a taxa, o comprimento, principal e termo (que são obrigatórios) que já vimos na primeira parte com a fórmula PMT. Mas aqui, precisamos também dos argumentos start_date e end_date. O primeiro indica o início do período a ser analisado e o segundo o fim. Vamos dar um exemplo:

= - CUMPRINC ((1 + B2) ^ (1/12) -1; B4 * 12; B3; 1; 12; 0)

= - CUMPRINC ((1 + 3, 10%) ^ (1/12) -1; 10 * 12; 120000; 1; 12; 0)

= - CUMIPMT ((1 + B2) ^ (1/12) -1; B4 * 12; B3 ; 1; 12; 0)

= - CUMIPMT ((1 + 3, 10%) ^ (1/12) -1; 10 * 12; 120000; 1; 12; 0)

O resultado é o um mostrado na captura de tela "Cumul 1º ano", de modo que os períodos analisados ​​variam de 1 a 12, do primeiro período (primeiro mês) ao duodécimo (12º mês). Ao longo de um ano, pagaríamos US $ 10 419, 55 Principais e US $ 3 522. 99 Juros.

Amortização do Empréstimo

As fórmulas anteriores nos permitem criar nosso período de cronograma por período, quanto pagaremos mensalmente no capital e interesse, e quanto é necessário pagar.

Criar uma programação de empréstimo no Excel

Para criar um cronograma de empréstimo, usaremos diferentes fórmulas discutidas acima e expandi-las sobre o número de períodos.

Na primeira coluna do período, basta digitar "1" como o primeiro período, depois arraste a célula para baixo. No nosso caso, precisamos de 120 períodos, uma vez que um pagamento de empréstimo de 10 anos multiplicado por 12 meses = 120.

A segunda coluna é o montante mensal que precisamos pagar cada mês, o que é constante durante todo o cronograma do empréstimo. Para calculá-lo, 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 é o principal que será reembolsado mensalmente. Por exemplo, para o 40º período, reembolsaremos $ 945. 51 em principal em nosso valor total mensal de US $ 1, 161. 88. Para calcular o valor do principal resgatado, estamos usando 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 é o interesse, pelo qual calculamos o principal reembolsado em nosso montante mensal para descobrir como é necessário pagar muito interesse, usando a fórmula:

= - INTPER (TP; A18; $ B $ 4 * 12; $ B $ 3) = -INTPER ((1 + 3, 10%) ^ (1/12) ; 1; 10 * 12; 120000)

A quinta coluna contém a quantidade restante a pagar. 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)

= 120000 + CUMPRINC ((1 + 3, 10%) ^ (1/12); 10 * 12; 120000; 1; 1; 0)

A fórmula usa uma combinação de principal sob um período anterior com a célula contendo o principal emprestado. Esse período começa a mudar quando copiamos e arrastamos a célula para baixo.A captura de tela abaixo mostra que no final de 120 períodos nosso empréstimo é reembolsado.