Como Criar uma Planilha de Amortização no Excel

A planilha de amortização revela a taxa de juros aplicada a um empréstimo de taxa fixa e como o valor principal é reduzido pelos pagamentos realizados. Ela ainda contém o cronograma detalhado de todos os pagamentos, permitindo a você ver quanto vai para o valor principal e quanto é direcionado aos juros da taxa adicional. Este artigo lhe ensinará a criar a sua própria planilha de amortização no Microsoft Excel.

Método 1

Método 1 de 2:

Criando uma planilha de amortização manualmente

Baixe em PDF

2
Crie rótulos na coluna A. Dê nome para os dados na primeira coluna a fim de mantê-los organizados. Escreva o seguinte em cada célula:
- A1: Quantia do Empréstimo;
- A2: Taxa de Juros;
- A3: Meses;
- A4: Pagamentos.
3
Insira as informações pertencentes ao empréstimo na coluna B. Preencha as células B1 a B3 com dados relativos ao empréstimo. Deixe a célula B4 (ao lado da célula Pagamentos) em branco.
- O valor da célula Meses deve ser o número total de meses no período do empréstimo. Se você estiver lidando com um empréstimo de dois anos, por exemplo, digite 24.
- O valor da célula Taxa de Juros deve ser em forma percentual (como 8,2%, por exemplo).
4
Calcule o pagamento na célula B4. Para isso, clique sobre a célula e digite a seguinte fórmula na barra de equações no topo da planilha, a seguir pressionando ↵ Enter ou ⏎ Return: =ARRED(PGTO($B$2/12,$B$3,-$B$1,0), 2).
- Os cifrões na fórmula são referências absolutas a fim de garantir que a equação colete sempre os valores das células específicas, mesmo que tenha sido copiada para outra parte da planilha.
- A taxa de juros do empréstimo deve ser dividida por 12, uma vez que a taxa anual é calculada mensalmente.
- Se o empréstimo for de R$ 150.000 a 6% durante 30 anos (360 meses), por exemplo, o pagamento resultante será equivalente a R$ 899,33.
5
Crie títulos na sétima linha. Você acrescentará alguns dados adicionais na planilha, o que requer uma segunda área. Insira os seguintes rótulos nas células:
- A7: Período;
- B7: Balanço Inicial;
- C7: Pagamento;
- D7: Valor Principal;
- E7: Taxa de Juros;
- F7: Valor Principal Cumulativo;
- G7: Taxa de Juros Cumulativa;
- H7: Balanço Final.
6
Complete a coluna Período. Ela conterá as datas dos pagamentos. Faça o seguinte:
- Digite o mês e o ano do primeiro pagamento do empréstimo na célula A8. Pode ser necessário formatá-la a fim de exibir corretamente o mês e o ano.
- Clique uma vez sobre a célula para selecioná-la.
- Arraste para baixo a partir do centro da célula selecionada a fim de cobrir todas as células até a A367. Se isso não fizer com que todas elas indiquem as datas de pagamento mensal corretamente, clique sobre o pequeno ícone com um relâmpago no canto direito da última célula e cuide para que a opção Preencher Meses esteja selecionada.
7
Preencha as demais entradas nas células B8 a H8.
- O balanço inicial do empréstimo na célula B8.
- Na célula C8, digite =$B$4 e pressione ↵ Enter ou ⏎ Return.
- Na célula E8, crie uma fórmula para calcular a taxa de juros do empréstimo no balanço inicial relativo ao período. Ela será similar a esta: =ARRED($B8*($B$2/12); 2). O cifrão único cria uma referência relativa. A fórmula procurará pela célula apropriada na coluna B.
- Na célula D8, subtraia a taxa de juros do empréstimo na célula E8 do pagamento total na célula C8. Use referências relativas para que a célula seja corretamente copiada. A fórmula será similar a esta: =$C8-$E8.
- Na célula H8, crie uma fórmula para subtrair a porção principal do pagamento do balanço inicial relativo ao período. A fórmula será similar a esta: =$B8-$D8.
8
Continue o cronograma criando as entradas de B9 a H9.
- A célula B9 deve incluir uma referência relativa ao balanço final do período anterior. Digite =$H8 na célula B9 e pressione ↵ Enter ou ⏎ Return.
- Copie as células C8, D8 e E8 e cole-as respectivamente em C9, D9 e E9.
- Copie H8 e cole-a em H9. É aqui onde a referência relativa demonstra sua utilidade.
- Na célula F9, crie uma fórmula para tabular o valor principal cumulativo. A fórmula será similar a esta: =$D9+$F8.
- Insira a taxa de juros cumulativa em G9 da seguinte maneira: =$E9+$G8.
Ao repousar o cursor do mouse sobre a porção direita inferior da área destacada, o cursor se transformará em uma cruz.
10
Arraste a cruz até a célula 367. Isto serve para ocupar todas essas células com os dados da amortização.
- Se isso parece um pouco estranho, clique no pequeno ícone de planilha na base direita da célula final e selecione Copiar Células.
Publicidade

Método 2

Método 2 de 2:

Usando um modelo do Excel

Baixe em PDF

Vá até https://templates.office.com/pt-br/agenda-de-amortização-do-empréstimo-tm03986974. Este é um modelo gratuito e baixável de agenda de amortização que facilita o cálculo de taxa de juros e do total de pagamentos. Ele ainda inclui a opção de se acrescentar pagamentos adicionais.^{[1]XFonte de pesquisa}
Isto salva o modelo no computador em formato de modelo do Excel (.XLTX).
3
Clique duas vezes sobre o arquivo baixado. Ele se chama tf03986974.xltx e estará geralmente na pasta de downloads do computador. Isso abre o modelo no Microsoft Excel.
- Os dados no modelo estão presentes apenas como exemplo — você pode inserir os próprios dados.
- Se necessário, clique em Ativar Edição a fim de realizar as alterações necessárias na pasta.
4
Digite a quantia do empréstimo na célula Quantia do Empréstimo. Ela se encontra na seção "Inserir os Valores" próximo ao canto esquerdo superior da planilha. Para prosseguir, clique sobre o valor existente (R$ 5.000,00) e digite-a.
- Ao pressionar ⏎ Return ou ↵ Enter (ou clicar em outra célula), as quantias no restante da planilha serão recalculadas. Isso acontecerá toda vez que você alterar um valor nessa seção.
Ela deve ser digitada na célula "Taxa de juros anual".
Ela deve ser digitada na célula "Período de empréstimo em anos".
Se são feitos uma vez ao mês, por exemplo, digite 12 na célula "Número de pagamentos por ano".
Ela deve ser digitada na célula "Data de início do empréstimo".
Ao pagar acima da quantia mínima devida ao empréstimo em cada período, insira nessa célula a quantia extra. De outro modo, altere o valor para 0 (zero).
Na célula em branco "Nome do credor", insira o nome de seu banco para facilitar a referência.
11
Salve a pasta como um novo arquivo do Excel. Veja como:
- Clique no menu Arquivo no canto esquerdo superior e selecione Salvar como....
- Escolha um local no computador ou na nuvem onde guardar a agenda.
- Insira um nome para o arquivo. Se o tipo de arquivo não estiver definido como "Pasta de trabalho do Excel (.xlsx)", selecione agora essa opção no menu suspenso (abaixo do nome do arquivo).
- Clique em Salvar.
Publicidade

Dicas

Caso não tenha notado um balanço final de R$ 0,00, confira se usou as referências absolutas e relativas como instruído e se as células foram copiadas da forma correta.
Você pode agora rolar para qualquer período durante o pagamento do empréstimo para ver quanto foi aplicado ao principal, quanto foi cobrado como taxa de juros e quanto do valor principal e da taxa de juros já foram pagos até o momento.

Avisos

Isto só funcionará para financiamentos imobiliários calculados mensalmente. No caso de um financiamento composto diariamente, o modelo dará apenas estimativas aproximadas relativas aos juros pagos.