Como criar sua própria calculadora de amortização de empréstimos |

A maioria das pessoas acha que existe algum tipo de mágica de vodu na criação de um empréstimo cronograma de amortização . Mas estou feliz em dizer que não há - e até melhor - até mesmo um iniciante pode criar um facilmente usando o Microsoft Excel.

Uma tabela de amortização de empréstimos fornece muitas informações boas: o valor total a pagar ao credor, a parte que é o pagamento de juros, a parte que é o pagamento do principal e o saldo do empréstimo em aberto.

Quando um empréstimo é amortizado, significa simplesmente que um pagamento de juros e um pagamento do principal são combinados em um pagamento periódico e os valores de pagamento são iguais para cada período de pagamento. Mas uma das características mais interessantes de um empréstimo amortizado é que, a cada pagamento, o montante de juros e principal sendo pago é alterado.

O Excel é uma ferramenta extremamente útil para calcular sua própria tabela de amortização. Você pode até experimentar os diferentes insumos para ver como diferentes taxas de juros, prazos de pagamento e valores de empréstimos principais afetam os pagamentos de empréstimos que você fará.

Vejamos um exemplo para ilustrar o processo

Suponhamos que você acabou de comprar um carro com um empréstimo de US $ 20.000 que deve ser totalmente pago em 4 anos. A taxa de juros anual do empréstimo é de 3%. Como o empréstimo é amortizado, os pagamentos mensais serão todos iguais.

O primeiro passo é calcular o valor do pagamento mensal. Isso pode ser feito usando a função PMT (que significa "pagamento") no Excel.

Comece criando uma nova planilha e inserindo os seguintes dados necessários para o cálculo do valor de pagamento.

A coluna A conterá os seguintes rótulos para nossos dados:

A1 = o principal original do empréstimo (seu valor do empréstimo)

A2 = o prazo do empréstimo em anos

A3 = a taxa de juros anual

A4 = o número de pagamentos por ano

A5 = o valor do pagamento (o que estamos calculando)

Coluna B contém os valores correspondentes para cada rótulo na coluna A:

B1 = $ 20.000

B2 = 4

B3 = 3%

B4 = 12

B5 = o valor que procuramos

A função PMT do Excel pode ser encontrada no menu de funções financeiras. Primeiro selecione a célula B5, depois clique no menu de funções financeiras na aba fórmulas e selecione a função PMT na lista. Uma caixa será exibida onde você deve inserir os dados da planilha, assim como está nesta imagem:

Há algumas coisas importantes a serem observadas aqui. Primeiro, precisamos fazer dois ajustes importantes, porque estamos calculando pagamentos mensais. Precisamos dividir nossa taxa anual por 12 para obter a taxa de juros mensal (taxa), e precisamos multiplicar 4 por 12 para obter o número total de meses em nosso período de reembolso (Nper).

Em segundo lugar, o valor presente (PV) é inserido como um valor negativo porque esse montante é devido ao credor.

Quando as caixas estiverem preenchidas, pressione "OK".

Agora, sua planilha deve ficar assim:

Agora, começaremos a criar a tabela de amortização.

Ignorar uma linha abaixo da planilha feita na parte 1 e, começando na linha 7, fazer o seguinte rótulos a partir da coluna A e terminando na coluna E:

A7 = Mês

B7 = Pagamento

C7 = Juros

D7 = Principal

E7 = Saldo

Estes são os cinco títulos para a tabela de amortização

Agora é hora de começar a preencher a tabela. O primeiro passo é inserir o número de meses na vida do empréstimo. Sob o cabeçalho "mês", digite "0" na célula A8 (a hora em que o empréstimo foi emitido). Preencha as células subseqüentes em ordem seqüencial até o número 48 (que o colocará na célula A56). Um truque: depois de inserir 0, verifique se a célula está selecionada (A8) e, em seguida, arraste o canto inferior direito da célula para a linha 56. Em seguida, selecione "preencher série" na caixa pequena que aparece à direita de a última célula. Isso deve preencher automaticamente a série numérica para os 48 meses.

Em seguida, todos os pagamentos podem ser preenchidos porque o valor de cada pagamento mensal já foi determinado. A partir do mês 1 (não há pagamento no mês 0 porque os pagamentos ocorrem no final de cada mês) preencha o valor $ 442.69 para cada mês 1-48. Depois disso, os valores de juros e principal podem ser calculados usando suas respectivas funções do Excel

O cálculo dos juros e componentes principais dos pagamentos usando a função Excel é simples e pode ser usado para preencher toda a tabela de amortização. Para calcular o pagamento de juros, selecione a célula C9 e, em seguida, clique no menu de funções financeiras na guia fórmulas. Selecione a função IPMT (para "pagamento de juros") na lista. Uma caixa será exibida onde você deve inserir os dados da planilha, assim como está nesta imagem:

Nota: Os sinais "$" entre as letras e números da célula são necessários para quando copiarmos as fórmulas até as células restantes depois. O "$" bloqueia a célula referida, de modo que as fórmulas permanecem intactas quando as copiamos para as células restantes. Você pode bloquear as células (e ver o "$") pressionando F4.

Em seguida, podemos calcular o valor do principal no pagamento. Semelhante ao cálculo do IPMT, selecione a célula D9 e, em seguida, clique no menu de funções financeiras na guia de fórmulas. Selecione a função PPMT (para "pagamento do principal) da lista. Uma caixa será exibida onde você deve inserir os dados da planilha, assim como está nesta imagem:

Nota: O principal poderia ser calculado usando esta função ou simplesmente subtrair o valor dos juros do valor total do pagamento.Também, os juros poderiam ser calculados se calculássemos o principal primeiro usando a função PPMT e então subtraíssemos esse valor do total do pagamento.No entanto, é bom para saber como usar as duas fórmulas, que também permite que o restante da tabela seja preenchido com mais eficiência, como você verá abaixo.

A coluna final (Coluna E) é a coluna de saldo restante. Isso pode ser facilmente calculado para a cada período, agora que determinamos os valores de juros e principal Para o mês 0, nenhum pagamento foi feito ainda, então o saldo é de US $ 20.000. No entanto, queremos que o Excel calcule automaticamente o saldo em cada período. manu Se você inserir “US $ 20.000”, insira “= B1”, consultando a tabela acima para o cálculo do pagamento. Para o mês 1, como o saldo é calculado subtraindo o saldo do mês anterior pelo pagamento do principal do mês atual, insira “= E8-D9” na célula E9. O saldo será calculado automaticamente. Sua tabela de amortização agora está completa para o mês 1 e deve ser exatamente assim:

Todos os dados para os meses restantes podem ser preenchidos simplesmente selecionando as células B9-E9, copiando a seleção e depois colando a seleção nas células B10-E10 Como inserimos as fórmulas para cada célula e bloqueamos nossas células que serão usadas em cada cálculo, os valores de juros, principal e saldo são calculados automaticamente. Vendo esse trabalho para o mês 2, a tabela inteira pode ser preenchida simplesmente selecionando as colunas B-E até o mês 48 e colando as informações já copiadas. O Excel calculou agora todos os valores para a tabela de amortização usando as fórmulas que inserimos. Sua tabela preenchida deve ter a seguinte aparência:

Primeiro Ano do Empréstimo:

Último Ano do Empréstimo:

Nota: Essas tabelas contêm apenas o primeiro e o último anos do tabela de amortização. Os anos 2 e 3 estariam entre essas duas tabelas formando uma tabela de amortização contínua para todos os 48 meses.

Se você estiver interessado em calcular pagamentos de hipoteca para uma hipoteca de taxa fixa, pode fazer uma tabela de amortização usando essas etapas, ou você pode usar nossa calculadora financeira para fazer isso por você. Nossa calculadora de hipoteca mostrará seu pagamento mensal, bem como um cronograma de amortização total.