A dívida ajuda a fazer as coisas acontecerem – um carro novo, uma faculdade ou até mesmo uma nova casa. No entanto, você deve pagá-lo. Você pode pedir um extrato de empréstimo, mas nem sempre é fácil de entender. No entanto, se você puder ver para onde vai o seu dinheiro, é mais fácil ver a luz no final do empréstimo.
É aí que entra o cronograma de amortização. Você visualizará melhor o empréstimo entendendo como funciona sua dívida e como seus pagamentos a afetam. Você pode até ver como um pequeno pagamento extra pode afetá-lo significativamente. Veja como você pode fazer um no Microsoft Excel.
O que é um cronograma de amortização?
De acordo com a Investopedia, “Um cronograma de amortização de empréstimos é uma tabela completa de pagamentos periódicos de empréstimos, mostrando o valor do principal e o valor dos juros que compõem cada pagamento até que o empréstimo seja pago no final de seu prazo”.
Assim, ao criar um cronograma, você verá quanto paga todos os meses e como é dividido entre o pagamento de juros e o valor principal. Com base em seus pagamentos mensais, você também verá quanto tempo leva para pagar o empréstimo. E, se você pagar mais, também verá como isso afeta o cronograma geral de pagamentos.
O cronograma de amortização ajuda você a ver quanto tempo leva para pagar sua dívida e como cada dólar que você investe para pagá-la a afeta. Você também pode usá-lo para planejar grandes compras, para ver quanto está pagando todos os meses e quanto economizará se pagar antecipadamente.
Como calcular seus detalhes de amortização no Excel
Para este exemplo, digamos que você esteja planejando comprar um Mustang Mach 1 Premium 2022. Seu preço líquido estimado é de US$ 68.529 e seu pagamento mensal é de US$ 1.164. Esse cálculo é baseado em um pagamento inicial de $ 6.853 por um prazo de 60 meses e uma taxa anual de 5%.
Insira os dados no Excel para confirmar se estão corretos. Você pode determinar seus pagamentos mensais, taxa de juros anual, meses para pagar o empréstimo ou valor total do empréstimo usando as seguintes fórmulas, respectivamente: PMT, RATE, NPER e NPV. Você também pode usar essas fórmulas se o banco ou revendedor não incluir um ponto de dados (como taxa de juros).
Essas fórmulas também incluem FV, TYPE e GUESS, mas você pode ignorá-las com segurança, pois são irrelevantes para sua aplicação.
Portanto, se você observar os dados fornecidos acima, PMT = 1.164, TAXA = 5%, NPER = 60 e PV = 61.676 (preço líquido estimado menos pagamento inicial). Mas se você tiver dados incompletos, é assim que você pode computá-los.
Pagamentos mensais
Para calcular os pagamentos mensais, use a função PMT:
=-PMT(RATE,NPER,PV,[FV],[TYPE])Lembre-se de dividir a TAXA por 12, pois estamos calculando os pagamentos mensais.
Taxa de juro
Se você precisar determinar a taxa de juros, use a função TAXA:
=RATE(NPER,-PMT,PV,[FV],[TYPE],[GUESS])Esta fórmula lhe dará a taxa mensal, então não se esqueça de multiplicar o resultado final por 12.
Período de pagamento
Quando você não tem certeza de quantos meses vai pagar um empréstimo, mas sabe quanto está emprestando e quanto pode pagar todo mês, pode usar a função NPER:
=NPER(RATE,-PMT,PV,[FV],[TYPE])Novamente, divida a TAXA por 12, já que você está calculando o número de meses em que pagará um empréstimo.
Valor Total do Empréstimo
Por fim, se você quiser saber quanto pode tomar emprestado dada uma taxa de juros específica, o valor que pode pagar mensalmente e o número de meses que está disposto a pagar o empréstimo, use a função PV:
=PV(RATE,NPER,-PMT,[FV],[TYPE]TAXA sempre deve ser dividida por 12 quando seu NPER for baseado em meses; caso contrário, você obterá um valor severamente deflacionado.
Agora que você sabe como encontrar qualquer valor ausente, é hora de criar a tabela de amortização.
Construindo uma Tabela de Amortização de Empréstimo com Taxa de Juros Fixa
Primeiro, você precisa adicionar os detalhes do seu empréstimo. Isso inclui o valor total, o adiantamento, o valor do empréstimo, a taxa de juros, o período do empréstimo e o pagamento mensal. Para garantir que você tenha os dados corretos, você pode usar a fórmula PMT acima, especialmente se quiser experimentar o período ou o valor do empréstimo.
Alternativamente, você pode usar as outras fórmulas mencionadas se quiser jogar com seus valores mensais de amortização, taxa de juros ou valor total do empréstimo.
Após adicionar seus dados, crie a tabela de amortização do empréstimo. Primeiro, adicione as seguintes colunas em seus dados iniciais: Período, Balanço inicial, Pagamento mensal, Pagamento principal, Pagamento de juros, Principal cumulativo, Juros cumulativos, Balanço final, Taxa de juro, e Pagamentos à vista.
A primeira linha de sua tabela de programação de amortização
Depois de criar as colunas, preencha a primeira linha (linha 9) em Período com Mês 1. Depois de fazer isso, use a funcionalidade de preenchimento automático do Excel para preencher rapidamente a coluna Período. Selecione a célula e coloque o cursor na pequena caixa verde no canto inferior direito da célula selecionada, onde o cursor deve se transformar em uma pequena cruz preta.
Quando o cursor se transforma, clique na pequena caixa verdee arraste o mouse para baixo. Um pequeno indicador aparecerá ao lado do mouse, indicando o número de meses que o Excel será aplicado à coluna. Pare e solte o botão do mouse quando chegar Mês 60.
Depois de preencher a coluna Período, é hora de preencher o restante da primeira linha. Debaixo Balanço inicialmodelo =$B$3 para se referir à célula onde você colocou o Montante do empréstimo. Não se esqueça de adicionar um sinal $ na frente da letra da coluna e do número da linha para garantir que ela não seja alterada ao preencher automaticamente o restante da tabela.
- Debaixo Pagamento mensalmodelo:
=$B$6
Isso copia a taxa de juros que você inseriu com seus dados iniciais. Não se esqueça de adicionar o sinal $, para que ele não mude quando você preencher automaticamente sua tabela.
- Debaixo Diretoruse a seguinte fórmula:
=C9-E9Isso subtrai o pagamento de juros do pagamento mensal.
- Debaixo Interessecalcule o valor com a seguinte fórmula:
=B9*(I9/12)Isso multiplica o pagamento mensal com a taxa de juros mensal.
- Por Principal cumulativomodelo:
=D9- Por Juros cumulativos, modelo:
=E9- Debaixo Taxa de juromodelo:
=B4Isso copia a taxa de juros que você calculou anteriormente. No entanto, se o seu banco usar taxas de juros variáveis, você poderá alterar isso mais adiante.
- Debaixo Balanço finaluse a fórmula:
=B9-D9Isso remove o valor principal que você pagou do seu saldo inicial.
- Manter Pagamentos à vista em branco, a menos que pretenda pagar uma quantia extra neste período.
Preenchimento da Tabela de Amortização de Empréstimos
Agora que você preencheu a primeira linha, é hora de adicionar o restante da tabela a partir da segunda linha (linha 10).
- Debaixo Balanço inicialdigite a seguinte fórmula:
=H9-I9Isso copia o saldo final que você pagou anteriormente, além de quaisquer pagamentos adiantados que você fez.
- Para o Pagamento mensal, Diretor, Interessee Balanço final colunas, copie a fórmula da linha acima.
- Debaixo Principal cumulativodigite o seguinte:
=F9+D10Isso adiciona o valor total do principal que você pagou no último período ao pagamento do principal que você fez nesse período.
- Debaixo Juros cumulativosdigite o seguinte:
=G9+E10Isso adiciona o valor total de juros que você pagou no último período ao pagamento de juros que você fez nesse período.
- Manter Pagamentos à vista em branco, a menos que pretenda pagar uma quantia extra neste período.
Depois de inserir as fórmulas acima, selecione os dados da linha 10, da coluna Saldo inicial até a coluna Pagamentos de quantia total. Após selecionar as células, pressione Ctrl + C para copiá-los.
Selecione os célula em Saldo Inicial para o Mês 3 (linha 11). Depois de fazer isso, role para baixo até a linha do Mês 60 (linha 68), pressione Mudança no teclado e, em seguida, selecione o célula sob o Coluna Pagamentos de quantia total (célula I68).
Ao concluir a seleção, pressione Ctrl + V para inserir as fórmulas que você criou; você verá toda a sua tabela de amortização preenchida com as informações necessárias.
Você pode enviar isso ao seu parceiro se estiver fazendo uma compra importante para ajudar a tomar uma decisão. Mesmo que estejam usando o Planilhas Google, eles podem importar o arquivo do Excel para o Planilhas para ver seus cálculos.
Experimente com seus dados
Você pode alterar seus dados para ver como isso afetará sua programação de pagamentos. Por exemplo, se você acha que pode fazer pagamentos adicionais esporádicos (como um pagamento de bônus anual da empresa), adicione seu valor extra estimado no período correto para pagamentos de quantia total.
Você também pode alterar o número de meses em que deseja fazer pagamentos. Por exemplo, se você quiser pagar seu empréstimo em 48 meses em vez de 60, basta alterar o número de meses em seus dados iniciais e sua tabela se ajustará para mostrar quanto você deve pagar todos os meses.
Quando seu saldo inicial cair abaixo de zero, isso significa que seu empréstimo está totalmente pago! Apenas observe que esta tabela só funciona para empréstimos com taxa fixa. Se o seu empréstimo tiver uma taxa variável, você terá que usar uma abordagem diferente.
