KutoolsforOffice — Uma solução, cinco ferramentas poderosas.Fazer mais com menos esforço.

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

AutorXiaoyang Data de Modificação

Criar um plano de amortização de empréstimo no Excel é uma competência valiosa que lhe permite visualizar e gerir os seus reembolsos de forma clara e eficaz. Um plano de amortização é uma tabela que detalha cada pagamento periódico de um empréstimo amortizável — como uma hipoteca ou um empréstimo automóvel — decompondo-o em juros e capital e apresentando o saldo remanescente após cada pagamento. Vejamos, passo a passo, como criar este plano no Excel.

Criar um plano de amortização de empréstimo

O que é um plano de amortização?

Crie um plano de amortização no Excel

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

Crie um plano de amortização com pagamentos extra

Crie um plano de amortização (com pagamentos extra) utilizando um modelo do Excel

Transferir ficheiro de exemplo

Criar um plano de amortização no Excel

O que é um plano de amortização?

Um plano de amortização é uma tabela detalhada utilizada no cálculo de empréstimos que ilustra o processo de quitação de um empréstimo ao longo do tempo. Os planos de amortização são comumente usados para empréstimos a taxa fixa, como hipotecas, empréstimos automóveis e empréstimos pessoais, nos quais o valor da prestação permanece constante durante todo o prazo do empréstimo, mas a proporção destinada a juros e capital varia ao longo do tempo.

Para criar um plano de amortização de empréstimo no Excel, as funções incorporadas PMT, PPMT e IPMT são, de facto, essenciais. Vamos explorar o que cada uma delas faz:

  • Função PMT: Esta função permite calcular o pagamento total por período de um empréstimo com base em pagamentos constantes e numa taxa de juro fixa.
  • Função IPMT: Esta função calcula a parcela de juros de um pagamento relativa a um determinado período.
  • Função PPMT: Esta função permite calcular a parcela de capital de um pagamento num período específico.

Ao utilizar estas funções no Excel, pode criar um plano de amortização detalhado que apresente os componentes de juros e capital de cada pagamento, bem como o saldo remanescente do empréstimo após cada prestação.

Crie um plano de amortização no Excel

Nesta secção, apresentamos dois métodos distintos para criar um plano de amortização no Excel. Adaptados a diferentes preferências e níveis de competência, garantem que qualquer utilizador — independentemente do seu domínio do Excel — consiga construir com sucesso um plano de amortização detalhado e preciso para o seu empréstimo.

As fórmulas oferecem uma compreensão mais profunda dos cálculos subjacentes e permitem flexibilidade para adaptar o plano a requisitos específicos. Esta abordagem é ideal para quem deseja uma experiência prática e uma visão clara de como cada pagamento se divide entre capital e juros. Agora, vamos detalhar, passo a passo, o processo de criação de um plano de amortização no Excel:

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

  1. Introduza as informações relativas ao empréstimo, tais como a taxa de juro anual, o prazo do empréstimo em anos, o número de pagamentos por ano e o montante do empréstimo nas células, conforme mostrado na seguinte imagem:
    Introduza as informações relativas ao empréstimo
  2. De seguida, crie uma tabela de amortização no Excel com os rótulos especificados — Período, Pagamento, Juros, Capital e Saldo Remanescente — nas células A7:E7.
  3. Na coluna Período, insira os números correspondentes a cada período. Neste exemplo, como o total de pagamentos é de 24 meses (2 anos), introduza os números de 1 a 24 na coluna Período. Veja a imagem:
    introduza os números do período
  4. Depois de configurar a tabela com os rótulos e os números dos períodos, pode avançar para introduzir fórmulas e valores nas colunas Pagamento, Juros, Capital e Saldo, de acordo com as características específicas do seu empréstimo.

⭐️ Passo 2: Calcular o montante total do pagamento utilizando a função PMT

A sintaxe da função PMT é:

= -PGTO()taxa de juro por período,número total de pagamentos, valor do empréstimo)
  • taxa de juro por período: Se a taxa de juro do seu empréstimo for anual, divida-a pelo número de pagamentos por ano. Por exemplo, se a taxa anual for 5 % e os pagamentos forem mensais, a taxa por período será 5 %/12. Neste exemplo, a taxa será apresentada como B1/B3.
  • número total de pagamentos: Multiplique o prazo do empréstimo em anos pelo número de pagamentos por ano. Neste exemplo, é apresentado como B2*B3.
  • Montante do empréstimo: Este é o valor principal que pediu emprestado. Neste exemplo, corresponde à célula B4.
  • sinal negativo (-): A função PGTO devolve um valor negativo porque representa um pagamento efetuado. Para apresentar esse valor como positivo, basta adicionar um sinal negativo antes da função PGTO.

Introduza a seguinte fórmula na célula B7 e, de seguida, arraste a alça de preenchimento para baixo para aplicar a fórmula às restantes células — obterá um valor de pagamento constante em todos os períodos. Veja a imagem:

= -PMT($B$1/$B$3, $B$2*$B$3, $B$4)
Nota: Aqui, utilize referências absolutasna fórmula para que, ao copiá-la para as células abaixo, permaneça inalterada.

Calcular o valor total do pagamento utilizando a função PMT

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

Neste passo, irá calcular os juros de cada período de pagamento utilizando a função IPMT do Excel.

= -PGTOJUR()taxa de juro por período,período específico,número total de pagamentos, valor do empréstimo)
  • taxa de juro por período: Se a taxa de juro do seu empréstimo for anual, divida-a pelo número de pagamentos por ano. Por exemplo, se a taxa anual for 5 % e os pagamentos forem mensais, a taxa por período será 5 %/12. Neste exemplo, a taxa será apresentada como B1/B3.
  • período específico: o período específico para o qual pretende calcular os juros. Normalmente, começa em 1 na primeira linha do seu plano e aumenta uma unidade em cada linha seguinte. Neste exemplo, o período inicia-se na célula A7.
  • número total de pagamentos: Multiplique o prazo do empréstimo, em anos, pelo número de pagamentos por ano. Neste exemplo, é apresentado como B2*B3.
  • Montante do empréstimo: Este é o valor principal que pediu emprestado. Neste exemplo, corresponde à célula B4.
  • sinal negativo (-): A função PGTO devolve um valor negativo, pois representa um pagamento efetuado. Para apresentar esse valor como positivo, basta adicionar um sinal negativo antes da função PGTO.

Introduza a seguinte fórmula na célula C7 e, de seguida, arraste a alça de preenchimento para baixo ao longo da coluna para replicar a fórmula e obter os juros de cada período.

=-IPMT($B$1/$B$3, A7, $B$2*$B$3, $B$4)
Nota: Na fórmula acima, A7 está definido como uma referência relativa, garantindo que se adapta dinamicamente à linha específica à qual a fórmula é estendida.

Calcular os juros utilizando a função IPMT

⭐️ Passo 4: Calcular o capital utilizando a função PPMT

Após calcular os juros de cada período, o próximo passo na criação de um plano de amortização consiste em determinar a parcela de capital de cada pagamento. Para tal, utiliza-se a função PPMT, concebida especificamente para calcular a parte do capital num pagamento referente a um determinado período, com base em pagamentos constantes e numa taxa de juro fixa.

A sintaxe da função IPMT é:

= -PGTOCAP()taxa de juro por período,período específico,número total de pagamentos, valor do empréstimo)

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

Introduza a seguinte fórmula na célula D7 e, de seguida, arraste a alça de preenchimento ao longo da coluna para replicar o capital em cada período. Consulte a imagem:

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

Calcular o capital utilizando a função PPMT

⭐️ Passo 5: Calcular o saldo remanescente

Após calcular os juros e o capital de cada pagamento, o próximo passo no seu plano de amortização é determinar o saldo remanescente do empréstimo após cada prestação. Esta etapa é essencial, pois revela claramente como o saldo do empréstimo diminui ao longo do tempo.

  1. Na primeira célula da sua coluna de saldo – E7, introduza a seguinte fórmula, que significa que o saldo remanescente será o montante inicial do empréstimo menos a parte do capital do primeiro pagamento:
    =B4-D7
    Calcular o saldo remanescente
  2. A partir do segundo período e em todos os seguintes, calcule o saldo remanescente subtraindo o pagamento de capital do período atual ao saldo do período anterior. Aplique a seguinte fórmula na célula E8:
    =E7-D8
    Nota: A referência à célula do saldo deve ser relativa, para que se atualize automaticamente ao arrastar a fórmula para baixo.
    Calcular o saldo remanescente
  3. Em seguida, arraste a alça de preenchimento para baixo na coluna. Como pode verificar, cada célula ajusta-se automaticamente para calcular o saldo remanescente com base nos pagamentos de capital atualizados.
    arraste a alça de preenchimento para baixo na coluna

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

Após configurar o seu plano de amortização detalhado, criar um resumo do empréstimo oferece uma visão geral rápida dos aspetos essenciais do seu financiamento, incluindo normalmente o custo total do empréstimo e o montante total de juros pagos.

● Para calcular o total de pagamentos:

=SUM(B7:B30)

● Para calcular o total de juros:

=SUM(C7:C30)

Criar um resumo do empréstimo

⭐️ Resultado:

Agora, foi criado com sucesso um plano de amortização de empréstimo simples, mas abrangente. Veja a imagem:

foi criado um plano simples de amortização de empréstimo

uma captura de ecrã de kutools for excel ai

Desbloqueie a Magia do Excel com KUTOOLS AI

  • Execução Inteligente: Realize operações em células, analise dados e crie gráficos — tudo impulsionado por comandos simples.
  • fórmulas personalizadas: Crie fórmulas personalizadas para otimizar os seus fluxos de trabalho.
  • Programação VBA: Escreva e implemente código VBA com facilidade.
  • Interpretação de Fórmulas: Compreenda facilmente fórmulas complexas.
  • Tradução de Texto: Elimine barreiras linguísticas nas suas folhas de cálculo.
Potencie as suas capacidades no Excel com ferramentas impulsionadas por IA.Descarregar Agorae experimente uma eficiência sem precedentes!

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

No exemplo anterior, criámos um plano de reembolso de empréstimo com um número fixo de prestações. Esta abordagem é ideal para gerir um empréstimo ou hipoteca específicos cujos termos permanecem inalterados.

Contudo, se pretender criar um plano de amortização flexível, reutilizável para empréstimos com períodos variáveis e que lhe permita ajustar o número de pagamentos conforme necessário a diferentes cenários de empréstimo, terá de seguir um método mais detalhado.

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

  1. Introduza as informações relativas ao empréstimo, tais como a taxa de juro anual, o prazo do empréstimo em anos, o número de pagamentos por ano e o montante do empréstimo nas células, conforme mostrado na seguinte imagem:
    Introduza as informações relativas ao empréstimo
  2. De seguida, crie uma tabela de amortização no Excel com os rótulos especificados — Período, Pagamento, Juros, Capital e Saldo Remanescente — nas células A7:E7.
  3. Na coluna Período, introduza o número máximo de pagamentos que pretende considerar para qualquer empréstimo — por exemplo, insira os valores de 1 a 360. Isto cobre um empréstimo padrão de 30 anos com pagamentos mensais.
    introduza o número máximo de pagamentos

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

Introduza as seguintes fórmulas nas células correspondentes e, de seguida, arraste a alça de preenchimento para as estender até ao número máximo de períodos de pagamento definido.

● Fórmula do pagamento:

Normalmente, utiliza-se a função PGTO para calcular o pagamento. Para incorporar uma instrução SE, a sintaxe da fórmula é:

=SE()período atual<=períodos totais, -PGTO()taxa de juro por período,períodos totais, valor do empréstimo), «» )

Assim, a fórmula é esta:

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

● Fórmula dos juros:

A fórmula da sintaxe é:

=SE()período atual<=períodos totais, -PGTOJUR()taxa de juro por período,período atual,períodos totais, valor do empréstimo), «» )

Assim, a fórmula é esta:

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

● Fórmula do capital:

A fórmula da sintaxe é:

=SE()período atual<=períodos totais, -PGTOCAP()taxa de juro por período,período atual,períodos totais, valor do empréstimo), «» )

Assim, a fórmula é esta:

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

Modifique as fórmulas de pagamento, juros e capital com a função SE

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

Para o saldo remanescente, subtraia normalmente o capital ao saldo anterior. Com uma instrução SE, modifique-a da seguinte forma:

● Célula do primeiro saldo: (E7)

=B4-D7

● Célula do segundo saldo: (E8)

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

Ajustar o saldo remanescente

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

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

● Para calcular o total de pagamentos:

=SUM(B7:B366)

● Para calcular o total de juros:

=SUM(C7:C366)

Criar um resumo do empréstimo

⭐️ Resultado:

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

Crie um plano de amortização com pagamentos extra

Ao efetuar pagamentos adicionais para além dos programados, é possível liquidar um empréstimo mais rapidamente. Um plano de amortização com pagamentos extra, quando criado no Excel, mostra claramente como esses montantes adicionais aceleram a quitação do empréstimo e reduzem o valor total de juros pagos. Eis como o pode configurar:

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

  1. Introduza as informações relativas ao empréstimo, tais como a taxa de juro anual, o prazo do empréstimo em anos, o número de pagamentos por ano, o montante do empréstimo e o pagamento extra nas células, conforme mostrado na seguinte imagem:
    Introduza as informações relativas ao empréstimo
  2. De seguida, calcule o pagamento programado.
    Além das células de introdução, é necessária outra célula predefinida para os nossos cálculos seguintes: o montante do pagamento programado. Trata-se do valor regular do pagamento de um empréstimo, assumindo que não são efetuados pagamentos adicionais. Aplique a seguinte fórmula na célula B6:
    =IFERROR(-PMT($B$1/$B$3, $B$2*$B$3, $B$4),"")

    calcular o pagamento programado
  3. De seguida, crie uma tabela de amortização no Excel:
    • Defina as etiquetas especificadas, tais como Período, Pagamento Programado, Pagamento Extra, Pagamento Total, Juros, Capital e Saldo Remanescente nas células A8:G8;
    • Na coluna Período, introduza o número máximo de pagamentos que pretende considerar para qualquer empréstimo — por exemplo, valores entre 0 e 360, o que corresponde a um empréstimo padrão de 30 anos com pagamentos mensais.
    • Para o Período 0 (linha 9 no nosso caso), obtenha o valor do Saldo com a fórmula =B4, que corresponde ao montante inicial do empréstimo. Todas as outras células nesta linha devem permanecer em branco.
    • criar uma tabela de amortização

⭐️ Passo 2: Criar as fórmulas para o plano de amortização com pagamentos extra

Introduza as seguintes fórmulas nas respetivas células, uma de cada vez. Para reforçar a gestão de erros, envolvemos esta e todas as fórmulas subsequentes na função SEERRO — uma abordagem que previne eficazmente múltiplos erros potenciais, caso alguma das células de entrada esteja em branco ou contenha valores inválidos.

● Calcular o pagamento programado:

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

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

Calcular o pagamento programado

● Calcular o pagamento extra:

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

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

Calcular o pagamento extra

● Calcular o pagamento total:

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

=IFERROR(B10+C10, "")

Calcular o pagamento total

● Calcular o capital:

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

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

Calcular o capital

● Calcular os juros:

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

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

Calcular os juros

● Calcular o saldo remanescente

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

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

Calcular o saldo remanescente

Após concluir cada uma das fórmulas, selecione o intervalo de células B10:G10 e utilize a alça de preenchimento para arrastar e replicar essas fórmulas por todos os períodos de pagamento. Nos períodos não utilizados, as células exibirão automaticamente o valor 0. Veja a captura de ecrã:
utilize a alça de preenchimento para arrastar e estender estas fórmulas para baixo

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

● Obter o número programado de pagamentos:

=B2:B3

● Obter o número real de pagamentos:

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

● Obter o total de pagamentos extra:

=SUM(C10:C369)

● Obter o total de juros:

=SUM(F10:F369)

Criar um resumo do empréstimo

⭐️ Resultado:

Ao seguir estes passos, cria um plano de amortização dinâmico no Excel que incorpora pagamentos extra.

Criar um plano de amortização utilizando um modelo do Excel

Criar um plano de amortização no Excel com base num modelo é uma forma simples e eficaz de poupar tempo. O Excel oferece modelos integrados que calculam automaticamente os juros, o capital e o saldo em dívida de cada prestação. Veja como criar um plano de amortização utilizando um modelo do Excel:

  1. Clique em Ficheiro > Novo, digite plano de amortização na caixa de pesquisa e prima a tecla Enter. Em seguida, selecione o modelo que melhor se adapta às suas necessidades com um simples clique. Por exemplo, aqui escolhemos o modelo Calculadora simples de empréstimos. Veja a imagem:
    Criar um plano de amortização com base num modelo
  2. Depois de selecionar um modelo, clique no botão Criar para abri-lo como um novo livro.
  3. De seguida, introduza os dados do seu empréstimo; o modelo calculará e preencherá automaticamente o plano com base nas suas entradas.
  4. Por fim, guarde a sua nova folha de cálculo do plano de amortização.