Como criar um cronograma de amortização no Excel: As etapas explicadas

Um cronograma de amortização fornece um roteiro financeiro claro, mostrando como cada pagamento do empréstimo se divide entre juros e principal até que sua dívida seja totalmente quitada.

Com as funções integradas do Excel, você pode criar cronogramas de amortização detalhados para visualizar o ciclo de vida do seu empréstimo, tomar decisões financeiras informadas e até mesmo explorar opções de pagamento antecipado.

Neste artigo, você aprenderá a criar um cronograma de amortização abrangente no Excel a partir do zero. Abordaremos funções essenciais como PMT(), IPMT() e PPMT(), guiaremos você pelo processo passo a passo e exploraremos cenários práticos para aprimorar seu kit de ferramentas de planejamento financeiro.

O que exatamente é um cronograma de amortização?

Uma tabela de amortização é uma tabela completa de pagamentos periódicos de empréstimos, mostrando o valor do principal e dos juros que compõem cada pagamento até que o empréstimo seja quitado no final de seu prazo.

Quando você faz um empréstimo, vários elementos-chave determinam como funcionam seus pagamentos:

• Principal: O valor original do empréstimo que você deve pagar
• Interesse: O custo do empréstimo do dinheiro, calculado como uma porcentagem do principal
• Períodode pagamento: Com que frequência você faz pagamentos (mensal, quinzenal, etc.)
• Prazodo empréstimo: O período total de tempo durante o qual você pagará o empréstimo

Para a maioria dos empréstimos padrão, como hipotecas, empréstimos para automóveis e empréstimos pessoais, os pagamentos permanecem consistentes durante todo o prazo. Entretanto, o que muda a cada pagamento é a proporção que vai para os juros em relação ao principal. Nos estágios iniciais, uma porcentagem maior de cada pagamento cobre os juros. À medida que você avança no prazo do empréstimo, mais de cada pagamento reduz o saldo principal.

O objetivo principal da criação de um cronograma de amortização é proporcionar transparência em seu empréstimo. Ele permite que você veja exatamente o valor dos juros que pagará ao longo do tempo, a rapidez com que está acumulando patrimônio e como os pagamentos adicionais podem afetar o cronograma do empréstimo. Para as empresas, essas programações também servem para fins de planejamento contábil e tributário.

As funções e fórmulas do Excel que você precisa conhecer

O Excel não tem uma única função de "amortização" integrada. Em vez disso, você normalmente constrói um cronograma de amortização com funções financeiras específicas que trabalham juntas para calcular diferentes aspectos dos pagamentos do empréstimo. Aqui estão as principais funções de que você precisará:

Função PMT()

A função PMT() calcula o pagamento de um empréstimo com base em pagamentos constantes e em uma taxa de juros constante:

=PMT(rate, nper, pv, [fv], [type])

• rate: A taxa de juros por período (taxa anual dividida pela frequência de pagamento)

• nper: O número total de períodos de pagamento

• pv: O valor atual (valor do empréstimo)

• [fv]: Valor futuro opcional (normalmente 0 para empréstimos pagos integralmente)

• [type]: Opcional; 0 para pagamentos de fim de período, 1 para início de período

Para calcular o pagamento mensal de uma hipoteca de US$ 250.000 com juros de 4,5% ao ano por 30 anos, você deve calcular o valor da hipoteca: 

=PMT(4.5%/12, 30*12, 250000)

Isso resulta em - US$ 1.266,71, representando a saída de caixa para o mutuário.

Para obter uma explicação detalhada da função PMT(), incluindo erros comuns e exemplos de uso avançado, você pode consultar o nosso abrangente guia PMT() Excel.

Função IPMT()

A função IPMT() calcula a parte dos juros de um pagamento específico:

=IPMT(rate, per, nper, pv, [fv], [type])

• per: O período de pagamento específico que você está calculando

O parâmetro per é essencial, pois determina o valor dos juros do período de pagamento que você está calculando. Diferentemente da função PMT(), que fornece o mesmo valor de pagamento para todos os períodos, os resultados de IPMT() variam significativamente, dependendo do período que você especificar, pois as parcelas de juros diminuem ao longo do prazo do empréstimo.

Isso mostra exatamente quanto de um determinado pagamento vai para os juros.

Função PPMT()

A função PPMT() calcula a parte principal de um pagamento específico:

=PPMT(rate, per, nper, pv, [fv], [type])

Semelhante ao site IPMT(), o valor do principal varia de acordo com o período, sendo que os primeiros pagamentos têm parcelas menores do principal e os últimos têm parcelas maiores. Isso mostra quanto de um determinado pagamento reduz o saldo do empréstimo.

Fórmulas personalizadas

Para cada período de pagamento, você deverá programar:

Saldo remanescente: O valor pendente do empréstimo após cada pagamento

Remaining balance = Previous balance - Current principal payment

Principal acumulado: Total do principal pago até a data

Cumulative principal = Previous cumulative principal + current principal payment

Juros acumulados: Total de juros pagos até a data

Formula: Previous cumulative interest + current interest payment

Função Round()

Mesmo pequenos erros de arredondamento podem se acumular ao longo do tempo em um cronograma de amortização:

=ROUND(value, 2)

Isso garante que todos os valores monetários sejam arredondados para o centavo mais próximo.

Referências absolutas vs. relativas

Ao criar sua tabela de amortização, você precisará:

• Use referências absolutas ($) para valores fixos, como taxa de juros e valor do empréstimo

• Use referências relativas para valores que mudam a cada período

A fórmula abaixo, por exemplo, usa referências absolutas para a taxa de juros, o prazo do empréstimo e o valor, mas uma referência relativa para o período de pagamento:

=PPMT($B$2/12, A10, $B$3*12, $B$1)

Guia passo a passo para criar o cronograma de amortização

Agora que você entende as principais funções, vamos criar um cronograma de amortização do zero. Com esse passo a passo, você criará uma tabela completa de pagamento de empréstimo que programa cada pagamento durante a vida do empréstimo.

1. Entrada de dados do empréstimo

Digite os detalhes do seu empréstimo nas células de entrada dedicadas, conforme mostrado no exemplo abaixo:

• Valor do empréstimo: US$ 250.000 (célula B1)
• Taxa de juros anual: 4,5% (célula B2)
• Prazo do empréstimo: 30 anos (célula B3)
• Frequência de pagamento: Mensalmente (célula B4)

Configuração dos parâmetros de entrada do empréstimo. Imagem do autor. 

2. Cálculo do pagamento mensal

Na célula B5, calcule o pagamento mensal usando a função PMT: =PMT(B2/12, B3*12, B1)

Essa fórmula retorna -$1.266,71, que representa seu pagamento mensal. O sinal negativo indica que se trata de um pagamento (saída de caixa).

Configurar os cabeçalhos das colunas e as fórmulas. Imagem do autor.

3. Criando a primeira linha do cronograma

Em seguida, podemos criar os seguintes cabeçalhos e trabalhar com a primeira linha de dados (linhas 7 e 8):  

• Período: Digite 1 na célula A8

• Valor do pagamento: =$B$5 na célula B8 (referenciando o pagamento mensal calculado)

• Pagamento de juros: =IPMT($B$2/12, A8, $B$3*12, $B$1) na célula C8

• Pagamento do principal: =PPMT($B$2/12, A8, $B$3*12, $B$1) na célula D8

• Saldo restante: =$B$1+D8 na célula E8 (empréstimo original mais pagamento do principal, o que funciona porque D8 retorna um valor negativo, subtraindo efetivamente o valor do principal do empréstimo)

• Principal acumulado: =D8 na célula F8 (o mesmo que o pagamento do principal da primeira linha)

• Juros acumulados: =C8 na célula G8 (o mesmo que o pagamento de juros da primeira linha)

Mostrando o detalhamento do primeiro pagamento. Imagem do autor.

4. Conclusão da programação

Para a segunda linha (linha 9):

• Período: =A8+1 na célula A9 (incrementando em 1)

• Valor do pagamento: Copie a fórmula de B8 para B9

• Pagamento de juros: =IPMT($B$2/12, A9, $B$3*12, $B$1) na célula C9

• Pagamento do principal: =PPMT($B$2/12, A9, $B$3*12, $B$1) na célula D9

• Saldo restante: =E8+D9 na célula E9 (saldo anterior mais principal atual)

• Principal acumulado: =F8+D9 na célula F9 (total anterior mais principal atual)

• Juros acumulados: =G8+C9 na célula G9 (total anterior mais juros atuais)

Observe que nas funções IPMT() e PPMT(), A9 é usado como referência relativa (sem sinais de dólar), o que significa que quando você copiar a fórmula, ela será atualizada automaticamente para A10, A11 etc., calculando o período correto a cada vez.

Configurando fórmulas que podem ser copiadas. Imagem do autor.

Os primeiros vários pagamentos no cronograma de amortização. Imagem do autor.

Selecione todas as células na linha 9 e arraste para baixo até a linha 367 (para um empréstimo mensal de 30 anos com 360 pagamentos).

À medida que você avança no prazo do empréstimo, mais de cada pagamento vai para o principal e não para os juros. Imagem do autor.

5. Verificação do pagamento final

Em um cronograma de amortização adequadamente construído, o pagamento final deve levar o saldo restante a exatamente zero, e o principal acumulado deve ser igual ao valor original do empréstimo.

O pagamento final leva o saldo restante a exatamente zero. Imagem do autor.

Como você pode ver na última linha, o saldo restante é de US$ 0,00 (destacado) e o principal acumulado é igual a US$ 250.000, o que corresponde ao valor original do empréstimo. Isso confirma que nosso cronograma de amortização é preciso.

Ao verificar o saldo final, pode ser útil aplicar a função ROUND() aos principais cálculos. Pequenas discrepâncias decimais devido à precisão de cálculo do Excel podem fazer com que o saldo final mostre um pequeno valor residual em vez de exatamente zero. O uso do site =ROUND(value, 2) garante que todos os valores monetários permaneçam consistentes com os padrões típicos de relatórios financeiros.

Coisas adicionais que você pode querer considerar

Um cronograma padrão é o ponto de partida. Considere estes aspectos para tornar seu modelo mais flexível:

Gerenciar variações de pagamento

Os cronogramas de amortização tornam-se ainda mais valiosos quando personalizados para explorar diferentes cenários de pagamento. Para modelar pagamentos extras, crie uma célula de entrada adicional para pagamentos suplementares e modifique sua fórmula de pagamento principal para incluir esse valor. Essa abordagem ajuda a visualizar como os pagamentos adicionais reduzem o prazo do empréstimo e o total de juros pagos. Para empréstimos com pagamentos em balão ou taxas de juros variáveis, considere a criação de seções separadas em seu cronograma que reflitam as mudanças de taxas em pontos específicos. Você pode usar o Gerenciador de cenários do Excel para comparar diferentes ambientes de taxas e seu impacto nos custos totais.

Aprimorando a legibilidade e a análise

Com a formatação adequada, você transforma sua tabela de amortização de uma tabela básica em uma ferramenta financeira inteligente. Aplique a formatação de moeda com casas decimais consistentes a todos os valores monetários e use a formatação condicional para destacar os principais marcos, como quando você pagou metade do principal ou atingiu limites específicos de patrimônio líquido. Também é importante entender como os diferentes métodos de acumulação de juros afetam seus pagamentos. A acumulação diária (comum em empréstimos estudantis) calcula os juros com base em um ano de 365 dias, enquanto muitas hipotecas usam um método de acumulação mensal. Documentar o método que sua programação usa garante a precisão na comparação com os extratos reais do empréstimo.

Personalização para tipos de empréstimos específicos

Empréstimos diferentes têm características únicas que podem exigir ajustes em seu modelo básico de amortização. Para hipotecas com contas de garantia, adicione colunas para rastrear os componentes de seguro e impostos juntamente com o principal e os juros. Para empréstimos com amortização negativa (em que os pagamentos não cobrem os juros acumulados), modifique o cálculo do saldo remanescente para adicionar os juros não pagos ao principal. A opção de tempo de pagamento nas funções financeiras do Excel (parâmetrotype ) permite que você modele os pagamentos no início ou no final dos períodos, o que é particularmente útil para arrendamentos e alguns empréstimos comerciais em que as convenções de tempo diferem dos empréstimos ao consumidor padrão.

Conclusão

A criação de um cronograma de amortização no Excel oferece a você uma visão valiosa de seus empréstimos, além do que a maioria dos credores fornece. Ao criar seu próprio cronograma, você pode ver exatamente como cada pagamento afeta seu saldo, entender o custo real do empréstimo ao longo do tempo e tomar decisões informadas sobre as opções de pré-pagamento. As habilidades desenvolvidas por meio desse processo - uso das funções financeiras do Excel, criação de fórmulas dinâmicas e elaboração de tabelas informativas - vão muito além da análise de empréstimos e podem ser aplicadas a muitos aspectos do planejamento financeiro pessoal e empresarial.

Se você quiser ir além da amortização e criar modelos financeiros mais completos - incluindo previsão de renda, avaliação de investimentos e execução de cenários hipotéticos -, confira nosso curso Modelagem financeira no Excel. É uma ótima próxima etapa para fortalecer suas habilidades em planilhas para análise financeira. E lembre-se: quanto mais confortável você se sentir com esses cálculos financeiros, mais bem posicionado estará para assumir o controle de seu futuro financeiro, seja gerenciando dívidas pessoais, planejando investimentos comerciais ou aconselhando clientes sobre suas opções financeiras.