Criar um cronograma de amortização de empréstimos no Excel pode ser uma habilidade valiosa, permitindo visualizar e gerenciar os pagamentos de seus empréstimos de maneira eficaz. Um cronograma de amortização é uma tabela que detalha cada pagamento periódico de um empréstimo com amortização (normalmente uma hipoteca ou um empréstimo para um carro). Ele divide cada pagamento em juros e componentes principais, mostrando o saldo remanescente após cada pagamento. Vamos mergulhar em um guia passo a passo para criar essa programação no Excel.

O que é um cronograma de amortização?

Crie um cronograma de amortização no Excel

Crie um cronograma de amortização para um número variável de períodos

Crie um cronograma de amortização com pagamentos extras

Crie um cronograma de amortização (com pagamentos extras) usando modelo Excel

O que é um cronograma de amortização?

Um cronograma de amortização é uma tabela detalhada usada em cálculos de empréstimos que exibe o processo de pagamento de um empréstimo ao longo do tempo. Os cronogramas de amortização são comumente usados ​​para empréstimos de taxa fixa, como hipotecas, empréstimos para automóveis e empréstimos pessoais, onde o valor do pagamento permanece constante ao longo do prazo do empréstimo, mas a proporção do pagamento de juros versus principal muda ao longo do tempo.

Na verdade, para criar um cronograma de amortização de empréstimos no Excel, as funções integradas PMT, PPMT e IPMT são cruciais. Vamos entender o que cada função faz:

• Função PMT: Esta função é usada para calcular o pagamento total por período de um empréstimo com base em pagamentos constantes e uma taxa de juros constante.
• Função IPMT: Esta função calcula a parcela de juros de um pagamento para um determinado período.
• Função PPMT: Esta função é usada para calcular a parcela principal de um pagamento para um período específico.

Ao usar essas funções no Excel, você pode criar um cronograma de amortização detalhado que mostra os juros e os componentes principais de cada pagamento, junto com o saldo restante do empréstimo após cada pagamento.

Crie um cronograma de amortização no Excel

Nesta seção, apresentaremos dois métodos distintos para criar um cronograma de amortização no Excel. Esses métodos atendem a diferentes preferências e níveis de habilidade do usuário, garantindo que qualquer pessoa, independentemente de sua proficiência com Excel, possa construir com sucesso um cronograma de amortização detalhado e preciso para seu empréstimo.

As fórmulas oferecem uma compreensão mais profunda dos cálculos subjacentes e oferecem flexibilidade para personalizar o cronograma de acordo com requisitos específicos. Esta abordagem é ideal para quem deseja ter uma experiência prática e uma visão clara de como cada pagamento é dividido em componentes de principal e juros. Agora, vamos detalhar passo a passo o processo de criação de um cronograma de amortização no Excel:

⭐️ Etapa 1: configurar as informações do empréstimo e a tabela de amortização

1. Insira as informações relativas do empréstimo, como taxa de juros anual, prazo do empréstimo em anos, número de pagamentos por ano e valor do empréstimo nas células, conforme mostrado a seguir:
   
2. Em seguida, crie uma tabela de amortização no Excel com os rótulos especificados, como Período, Pagamento, Juros, Principal, Saldo Remanescente nas células A7:E7.
3. Na coluna Período, insira os números dos períodos. Neste exemplo, o número total de pagamentos é de 24 meses (2 anos), portanto, você inserirá os números de 1 a 24 na coluna Período. Veja a captura de tela:
   
4. Depois de configurar a tabela com os rótulos e números dos períodos, você poderá inserir fórmulas e valores para as colunas Pagamento, Juros, Principal e Saldo com base nas especificações do seu empréstimo.

⭐️ Etapa 2: Calcule o valor total do pagamento usando a função PMT

A sintaxe do PMT é:

Insira a seguinte fórmula na célula B7 e, em seguida, arraste a alça de preenchimento para baixo para preencher esta fórmula em outras células e você verá um valor de pagamento constante para todos os períodos. Veja a captura de tela:

= -PMT($B$1/$B$3, $B$2*$B$3, $B$4)

⭐️ Etapa 3: Calcule os juros usando a função IPMT

Nesta etapa, você calculará os juros de cada período de pagamento usando a função IPMT do Excel.

Insira a seguinte fórmula na célula C7 e, a seguir, arraste a alça de preenchimento para baixo na coluna para preencher esta fórmula e obter os juros de cada período.

=-IPMT($B$1/$B$3, A7, $B$2*$B$3, $B$4)

⭐️ Etapa 4: Calcule o principal usando a função PPMT

Após calcular os juros de cada período, o próximo passo na criação de um cronograma de amortização é calcular a parcela do principal de cada pagamento. Isso é feito por meio da função PPMT, que tem como objetivo determinar a parte principal de um pagamento para um período específico, com base em pagamentos constantes e uma taxa de juros constante.

A sintaxe do IPMT é:

A sintaxe e os parâmetros da fórmula PPMT são idênticos aos usados ​​na fórmula IPMT discutida anteriormente.

Insira a seguinte fórmula na célula D7 e arraste a alça de preenchimento para baixo na coluna para preencher o principal de cada período. Veja a captura de tela:

=-PPMT($B$1/$B$3, A7, $B$2*$B$3, $B$4)

⭐️ Etapa 5: Calcule o saldo restante

Depois de calcular os juros e o principal de cada pagamento, a próxima etapa do cronograma de amortização é calcular o saldo restante do empréstimo após cada pagamento. Esta é uma parte crucial do cronograma, pois mostra como o saldo do empréstimo diminui ao longo do tempo.

1. Na primeira célula da coluna de saldo – E7, insira a seguinte fórmula, o que significa que o saldo restante será o valor original do empréstimo menos a parcela principal do primeiro pagamento:

   =B4-D7

   
2. Para o segundo e todos os períodos subsequentes, calcule o saldo remanescente subtraindo o pagamento do principal do período atual do saldo do período anterior. Aplique a seguinte fórmula na célula E8:

   =E7-D8

    Note: a referência à célula de saldo deve ser relativa, por isso é atualizada conforme você arrasta a fórmula para baixo.
   
3. E então arraste a alça de preenchimento para baixo até a coluna. Como você pode ver, cada célula será ajustada automaticamente para calcular o saldo restante com base nos pagamentos do principal atualizados.
   

⭐️ Etapa 6: Faça um resumo do empréstimo

Depois de configurar seu cronograma de amortização detalhado, a criação de um resumo do empréstimo pode fornecer uma visão geral rápida dos principais aspectos do seu empréstimo. Este resumo normalmente incluirá o custo total do empréstimo e o total de juros pagos.

● Para calcular o total de pagamentos:

=SUM(B7:B30)

● Para calcular os juros totais:

=SUM(C7:C30)

⭐️ Resultado:

Agora, um cronograma de amortização de empréstimos simples, mas abrangente, foi criado com sucesso. veja a captura de tela:

Crie um cronograma de amortização para um número variável de períodos

No exemplo anterior, criamos um cronograma de pagamento de empréstimo para um número fixo de pagamentos. Essa abordagem é perfeita para lidar com um empréstimo ou hipoteca específico onde os termos não mudam.

Mas, se você deseja criar um cronograma de amortização flexível que possa ser usado repetidamente para empréstimos com períodos variados, permitindo modificar o número de pagamentos conforme necessário para diferentes cenários de empréstimo, você precisará seguir um método mais detalhado.

⭐️ Etapa 1: configurar as informações do empréstimo e a tabela de amortização

1. Insira as informações relativas do empréstimo, como taxa de juros anual, prazo do empréstimo em anos, número de pagamentos por ano e valor do empréstimo nas células, conforme mostrado a seguir:
   
2. Em seguida, crie uma tabela de amortização no Excel com os rótulos especificados, como Período, Pagamento, Juros, Principal, Saldo Remanescente nas células A7:E7.
3. Na coluna Período, insira o maior número de pagamentos que você pode considerar para qualquer empréstimo, por exemplo, preencha os números que variam de 1 a 360. Isso pode cobrir um empréstimo padrão de 30 anos se você estiver fazendo pagamentos mensais.
   

⭐️ Etapa 2: Modifique as fórmulas de pagamento, juros e princípio com a função IF

Insira as seguintes fórmulas nas células correspondentes e arraste a alça de preenchimento para estender essas fórmulas até o número máximo de períodos de pagamento que você definiu.

● Fórmula de pagamento:

Normalmente, você usa a função PMT para calcular o pagamento. Para incorporar uma instrução IF, a fórmula de sintaxe é:

Então a fórmula é esta:

=IF(A7<=$B$2*$B$3, -PMT($B$1/$B$3, $B$2*$B$3, $B$4), "")

● Fórmula de juros:

A fórmula de sintaxe é:

Então a fórmula é esta:

=IF(A7<=$B$2*$B$3,-IPMT($B$1/$B$3, A7, $B$2*$B$3, $B$4), "")

● Fórmula principal:

A fórmula de sintaxe é:

Então a fórmula é esta:

=IF(A7<=$B$2*$B$3,-PPMT($B$1/$B$3, A7, $B$2*$B$3, $B$4), "")

⭐️ Etapa 3: ajuste a fórmula do saldo restante

Para o saldo restante, normalmente você pode subtrair o principal do saldo anterior. Com uma instrução IF, modifique-a como:

● A primeira célula do saldo:(E7)

=B4-D7

● A segunda célula do saldo:(E8)

=IF(A8<=$B$2*$B$3, E7-D8, "") 

⭐️ Etapa 4: Faça um resumo do empréstimo

Depois de configurar o cronograma de amortização com as fórmulas modificadas, a próxima etapa é criar um resumo do empréstimo.

● Para calcular o total de pagamentos:

=SUM(B7:B366)

● Para calcular os juros totais:

=SUM(C7:C366)

⭐️ Resultado:

Agora você tem um cronograma de amortização abrangente e dinâmico no Excel, completo com um resumo detalhado do empréstimo. Sempre que você ajustar o prazo do período de pagamento, todo o cronograma de amortização será atualizado automaticamente para refletir essas alterações. Veja a demonstração abaixo:

Crie um cronograma de amortização com pagamentos extras

Ao fazer pagamentos adicionais além dos programados, um empréstimo pode ser liquidado mais rapidamente. Um cronograma de amortização que incorpora pagamentos extras, quando criado em Excel, demonstra como esses pagamentos adicionais podem acelerar o pagamento do empréstimo e diminuir o total de juros pagos. Veja como você pode configurá-lo:

⭐️ Etapa 1: configurar as informações do empréstimo e a tabela de amortização

1. Insira as informações relativas do empréstimo, como taxa de juros anual, prazo do empréstimo em anos, número de pagamentos por ano, valor do empréstimo e pagamento extra nas células, conforme mostrado a seguir:
   
2. Em seguida, calcule o pagamento programado.
   Além das células de entrada, outra célula predefinida é necessária para nossos cálculos subsequentes – o valor do pagamento programado. Este é o valor do pagamento regular de um empréstimo, presumindo que nenhum pagamento adicional seja feito. Aplique a seguinte fórmula na célula B6:

   =IFERROR(-PMT($B$1/$B$3, $B$2*$B$3, $B$4),"")

   
   
3. Em seguida, crie uma tabela de amortização no Excel:
   • Defina os rótulos especificados, como Período, Programar Pagamento, Pagamento Extra, Pagamento Total, Juros, Principal, Saldo Remanescente nas células A8:G8;
   • Na coluna Período, insira o maior número de pagamentos que você pode considerar para qualquer empréstimo. Por exemplo, preencha os números que variam de 0 a 360. Isso pode cobrir um empréstimo padrão de 30 anos se você fizer pagamentos mensais;
   • Para o Período 0 (linha 9 em nosso caso), recupere o valor do Saldo com esta fórmula = B4, que corresponde ao valor inicial do empréstimo. Todas as outras células desta linha devem ser deixadas em branco.
   • 

⭐️ Passo 2: Crie as fórmulas para cronograma de amortização com pagamentos extras

Insira as seguintes fórmulas nas células correspondentes, uma por uma. Para melhorar o tratamento de erros, incluímos esta e todas as fórmulas futuras na função IFERROR. Essa abordagem ajuda a evitar vários erros potenciais que podem surgir se alguma das células de entrada for deixada em branco ou contiver valores incorretos.

● Calcule o pagamento programado:

Insira a seguinte fórmula na célula B10:

=IFERROR(IF($B$6<=G9, $B$6, G9+G9*$B$1/$B$3), "")

● Calcule o pagamento extra:

Insira a seguinte fórmula na célula C10:

=IFERROR(IF($B$5<G9-E10,$B$5, G9-E10), "")

● Calcule o pagamento total:

Insira a seguinte fórmula na célula D10:

=IFERROR(B10+C10, "")

● Calcule o principal:

Insira a seguinte fórmula na célula E10:

=IFERROR(IF(B10>0, MIN(B10-F10, G9), 0), "")

● Calcule os juros:

Insira a seguinte fórmula na célula F10:

=IFERROR(IF(B10>0, $B$1/$B$3*G9, 0), "")

● Calcule o saldo restante

Insira a seguinte fórmula na célula G10:

=IFERROR(IF(G9 >0, G9-E10-C10, 0), "")

Depois de concluir cada uma das fórmulas, selecione o intervalo de células B10:G10 e use a alça de preenchimento para arrastar e estender essas fórmulas por todo o conjunto de períodos de pagamento. Para quaisquer períodos não utilizados, as células mostrarão 0s. Veja a captura de tela:

⭐️ Etapa 3: Faça um resumo do empréstimo

● Obtenha o número programado de pagamentos:

=B2:B3

● Obtenha o número real de pagamentos:

=COUNTIF(D10:D369,">"&0)

● Receba o total de pagamentos extras:

=SUM(C10:C369)

● Obtenha interesse total:

=SUM(F10:F369)

⭐️ Resultado:

Seguindo essas etapas, você cria um cronograma de amortização dinâmico no Excel que contabiliza pagamentos extras.

Crie um cronograma de amortização usando o modelo Excel

Criar um cronograma de amortização no Excel usando um modelo é um método simples e eficiente em termos de tempo. O Excel fornece modelos integrados que calculam automaticamente os juros, o principal e o saldo restante de cada pagamento. Veja como criar um cronograma de amortização usando um modelo Excel:

1. Clique Envie o > Novo, na caixa de pesquisa, digite cronograma de amortizaçãoe pressione Entrar chave. Em seguida, selecione o modelo que melhor atende às suas necessidades clicando nele. Por exemplo, aqui selecionarei o modelo de calculadora de empréstimo simples. Veja a captura de tela:
   
2. Depois de selecionar um modelo, clique no botão Crie botão para abri-lo como uma nova pasta de trabalho.
3. Em seguida, insira os detalhes do seu próprio empréstimo. O modelo deve calcular e preencher automaticamente o cronograma com base em suas entradas.
4. Por fim, salve sua nova pasta de trabalho do cronograma de amortização.