Criar uma planilha de amortização de empréstimo no Excel – Um tutorial passo a passo

Criar uma planilha de amortização de empréstimo no Excel pode ser uma habilidade valiosa, permitindo que você visualize e gerencie eficazmente os pagamentos do seu empréstimo. Uma planilha de amortização é uma tabela que detalha cada pagamento periódico em um empréstimo amortizado (normalmente uma hipoteca ou empréstimo automotivo). Ela divide cada pagamento em componentes de juros e principal, mostrando o saldo restante após cada pagamento. Vamos mergulhar em um guia passo a passo para criar tal planilha no Excel.

O que é uma planilha de amortização?

Criar uma planilha de amortização no Excel

Criar uma planilha de amortização para um número variável de períodos

Criar uma planilha de amortização com pagamentos extras

Criar uma planilha de amortização (com pagamentos extras) usando um modelo do Excel

O que é uma planilha de amortização?

Uma planilha de amortização é uma tabela detalhada usada nos cálculos de empréstimos que exibe o processo de quitar um empréstimo ao longo do tempo. As planilhas de amortização são comumente usadas para empréstimos de taxa fixa, como hipotecas, empréstimos automotivos e empréstimos pessoais, onde o valor da prestação permanece constante durante todo o prazo do empréstimo, mas a proporção do pagamento destinado aos juros versus o principal muda ao longo do tempo.

Para criar uma planilha de amortização de empréstimo no Excel, as funções embutidas PMT, PPMT e IPMT são realmente 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 parte dos juros de um pagamento para um determinado período.
• Função PPMT: Esta função é usada para calcular a parte do principal de um pagamento para um período específico.

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

Criar uma planilha de amortização no Excel

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

As fórmulas oferecem uma compreensão mais profunda dos cálculos subjacentes e proporcionam flexibilidade para personalizar a planilha conforme requisitos específicos. Essa abordagem é ideal para aqueles que desejam 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 o processo de criação de uma planilha de amortização no Excel passo a passo:

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

1. Insira as informações relevantes 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 na captura de tela 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 Restante nas células A7:E7.
3. Na coluna Período, insira os números dos períodos. Para este exemplo, o número total de pagamentos é de 24 meses (2 anos), então você inserirá os números de 1 a 24 na coluna Período. Veja a captura de tela:
   
4. Uma vez configurada a tabela com os rótulos e números dos períodos, você pode prosseguir para inserir fórmulas e valores para as colunas Pagamento, Juros, Principal e Saldo com base nas especificações do seu empréstimo.

⭐️ Passo 2: Calcular o valor total do pagamento usando a função PMT

A sintaxe da função 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)

⭐️ Passo 3: Calcular os juros usando a função IPMT

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

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

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

⭐️ Passo 4: Calcular o principal usando a função PPMT

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

A sintaxe da função 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, em seguida, arraste a alça de preenchimento para baixo na coluna para preencher o principal para cada período. Veja a captura de tela:

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

⭐️ Passo 5: Calcular o saldo restante

Após calcular tanto os juros quanto o principal de cada pagamento, o próximo passo na sua planilha de amortização é calcular o saldo restante do empréstimo após cada pagamento. Esta é uma parte crucial da planilha, pois mostra como o saldo do empréstimo diminui ao longo do tempo.

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

   =B4-D7

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

   =E7-D8

    Nota: A referência à célula de saldo deve ser relativa, para que ela seja atualizada conforme você arrasta a fórmula para baixo.
   
3. E então arraste a alça de preenchimento para baixo na coluna. Como você pode ver, cada célula ajustará automaticamente para calcular o saldo restante com base nos pagamentos principais atualizados.
   

⭐️ Passo 6: Fazer um resumo do empréstimo

Após configurar sua planilha de amortização detalhada, criar um resumo do empréstimo pode fornecer uma visão rápida dos aspectos-chave do seu empréstimo. Esse resumo normalmente incluirá o custo total do empréstimo, os juros totais pagos.

● Para calcular os pagamentos totais:

=SUM(B7:B30)

● Para calcular os juros totais:

=SUM(C7:C30)

⭐️ Resultado:

Agora, uma planilha de amortização de empréstimo simples, mas abrangente, foi criada com sucesso. Veja a captura de tela:

Desbloqueie a Magia do Excel com o Kutools AI

• Execução Inteligente: Realize operações de células, analise dados e crie gráficos — tudo impulsionado por comandos simples.
• Fórmulas Personalizadas: Gere fórmulas sob medida para otimizar seus fluxos de trabalho.
• Codificação VBA: Escreva e implemente código VBA sem esforço.
• Interpretação de Fórmulas: Compreenda fórmulas complexas com facilidade.
• Tradução de Texto: Supere barreiras linguísticas dentro de suas planilhas.

Potencialize suas capacidades no Excel com ferramentas alimentadas por IA. Baixe Agora e experimente uma eficiência como nunca antes!

Criar uma planilha de amortização para um número variável de períodos

No exemplo anterior, criamos uma programação 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 uma planilha de amortização flexível que possa ser usada repetidamente para empréstimos com períodos variáveis, permitindo que você modifique o número de pagamentos conforme necessário para diferentes cenários de empréstimo, precisará seguir um método mais detalhado.

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

1. Insira as informações relevantes 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 na captura de tela 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 Restante 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 variando de 1 a 360. Isso pode cobrir um empréstimo padrão de 30 anos se você estiver fazendo pagamentos mensais.
   

⭐️ Passo 2: Modificar as fórmulas de pagamento, juros e principal com a função SE

Insira as seguintes fórmulas nas células correspondentes e, em seguida, 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 SE, a fórmula de sintaxe é:

Então as fórmulas são estas:

=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 as fórmulas são estas:

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

● Fórmula de principal:

A fórmula de sintaxe é:

Então as fórmulas são estas:

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

⭐️ Passo 3: Ajustar a fórmula do saldo restante

Para o saldo restante, você geralmente subtrai o principal do saldo anterior. Com uma instrução SE, modifique-a assim:

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

=B4-D7

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

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

⭐️ Passo 4: Fazer um resumo do empréstimo

Após configurar a planilha de amortização com as fórmulas modificadas, o próximo passo é criar um resumo do empréstimo.

● Para calcular os pagamentos totais:

=SUM(B7:B366)

● Para calcular os juros totais:

=SUM(C7:C366)

⭐️ Resultado:

Agora, você tem uma planilha de amortização abrangente e dinâmica no Excel, completa com um resumo detalhado do empréstimo. Sempre que você ajustar o prazo do período de pagamento, toda a planilha de amortização será atualizada automaticamente para refletir essas mudanças. Veja a demonstração abaixo:

Criar uma planilha de amortização com pagamentos extras

Ao fazer pagamentos adicionais além dos agendados, um empréstimo pode ser quitado mais rapidamente. Uma planilha de amortização que incorpora pagamentos extras, quando criada no Excel, demonstra como esses pagamentos adicionais podem acelerar a quitação do empréstimo e diminuir o total de juros pagos. Aqui está como configurá-la:

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

1. Insira as informações relevantes 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 na captura de tela a seguir:
   
2. Em seguida, calcule o pagamento agendado.
   Além das células de entrada, outra célula pré-definida é necessária para nossos cálculos subsequentes - o valor do pagamento agendado. Este é o valor regular de pagamento de um empréstimo, assumindo que nenhum pagamento adicional seja feito. Por favor, 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, Pagamento Agendado, Pagamento Extra, Pagamento Total, Juros, Principal, Saldo Restante 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 variando de 0 a 360. Isso pode cobrir um empréstimo padrão de 30 anos se você estiver fazendo pagamentos mensais;
   • Para o Período 0 (linha 9 no 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 nesta linha devem ser deixadas em branco.
   • 

⭐️ Passo 2: Criar as fórmulas para a planilha de amortização com pagamentos extras

Insira as seguintes fórmulas nas células correspondentes uma por uma. Para melhorar o tratamento de erros, encapsulamos esta e todas as fórmulas futuras dentro da função SEERRO. Essa abordagem ajuda a evitar múltiplos erros potenciais que poderiam surgir se alguma das células de entrada for deixada em branco ou contiver valores incorretos.

● Calcular o pagamento agendado:

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

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

● Calcular o pagamento extra:

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

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

● Calcular o pagamento total:

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

=IFERROR(B10+C10, "")

● Calcular o principal:

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

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

● Calcular os juros:

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

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

● Calcular o saldo restante

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

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

Uma vez que você tenha concluído 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 para baixo através de 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:

⭐️ Passo 3: Fazer um resumo do empréstimo

● Obter número de pagamentos agendados:

=B2:B3

● Obter número real de pagamentos:

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

● Obter total de pagamentos extras:

=SUM(C10:C369)

● Obter total de juros:

=SUM(F10:F369)

⭐️ Resultado:

Ao seguir esses passos, você cria uma planilha de amortização dinâmica no Excel que leva em conta os pagamentos extras.

Criar uma planilha de amortização usando um modelo do Excel

Criar uma planilha de amortização no Excel usando um modelo é um método direto e eficiente em termos de tempo. O Excel fornece modelos embutidos que calculam automaticamente os juros, o principal e o saldo restante de cada pagamento. Aqui está como criar uma planilha de amortização usando um modelo do Excel:

1. Clique Arquivo > Novo, na caixa de pesquisa, digite planilha de amortização, e pressione Enter . Em seguida, selecione o modelo que melhor atende às suas necessidades clicando nele. Por exemplo, aqui, selecionarei o modelo Calculadora de Empréstimo Simples. Veja a captura de tela:
   
2. Uma vez que você tenha selecionado um modelo, clique no botão Criar para abri-lo como uma nova pasta de trabalho.
3. Em seguida, insira seus próprios detalhes do empréstimo, e o modelo deve calcular automaticamente e preencher a planilha com base nas suas entradas.
4. Por fim, salve sua nova pasta de trabalho da planilha de amortização.