Tabela de amortização no Google Sheets (guia fácil)

Os usuários de planilhas conhecem o Google Sheets como a solução on-line gratuita para armazenar, estruturar e analisar dados. Ele também é cada vez mais usado por especialistas em finanças, pois tem funções financeiras integradas poderosas que permitem fazer desde cálculos simples até os mais complexos, seja para sua empresa ou para uso pessoal.

Por exemplo, você pode usar as funções PMT do Google Sheets, PPMT e IPMT para calcular quanto você vai pagar para quitar um empréstimo ou investimento, incluindo o principal e os juros. Além disso, você pode montar sua tabela de amortização do empréstimo para ver como os pagamentos mensais afetam o valor final.

Neste artigo, você vai aprender o que é uma tabela de amortização de empréstimos no Google Sheets e como criar uma tabela padrão e outra com pagamentos extras.

O que é uma tabela de amortização de empréstimos no Google Sheets?

A amortização de empréstimos é um processo em que primeiro se pagam os juros de um determinado período e o valor restante é usado para reduzir o principal. Como esses pagamentos são periódicos, você pode programá-los com antecedência para ver o principal e os juros que vai precisar pagar até quitar totalmente o empréstimo.

No Google Sheets, você pode usar as funções PMT (PGTO), PPMT (PPGTO) e IPMT (IPGTO) para criar sua própria tabela de amortização de empréstimos.

Funções PMT, PPMT e IPMT

Antes de ver os passos para criar sua tabela de amortização, vamos ver como essas funções são escritas como fórmula e o que cada argumento significa.

Sintaxe da PMT

=PMT(rate, number_of_periods, present_value, future_value, end_or_beginning)

taxa é a taxa de juros do período de pagamento.
número_de_períodos é o número de pagamentos que você precisa fazer para quitar o empréstimo.
valor_presente é o valor do principal.
valor futuro é um argumento opcional que representa o valor futuro que restará após o último pagamento.
fim_ou_começo é um argumento opcional que indica se os pagamentos vencem no fim (representado por "0") ou no início ("1") de cada período. Por padrão, esse valor é "0".

Sintaxe da PPMT

O PPMT tem uma sintaxe quase igual à da PMT, mas inclui o argumento período:

=PPMT(rate, period, number_of_periods, present_value, future_value, end_or_beginning)

período refere-se ao período de amortização. Por exemplo, se você tem 60 períodos, esse argumento deve ser qualquer número entre 1 e 60.

Sintaxe da IPMT

Embora a sintaxe seja a mesma, a PPMT é usada para calcular o valor do principal, enquanto a função IPMT calcula o valor dos juros. Assim, você pode determinar a proporção de cada pagamento.

=IPMT(rate, period, number_of_periods, present_value, future_value, end_or_beginning)

Como criar uma tabela de amortização de empréstimos no Google Sheets?

Agora que você conhece a sintaxe de cada função usada para criar sua tabela de amortização, vamos começar preparando o formato da tabela.

Como preparar o formato da tabela de amortização do empréstimo?

Antes de usar as funções, configure os dados e o formato da tabela de amortização. Veja como preparar a tabela de amortização do empréstimo.

1. Insira os valores da sua tabela de amortização, incluindo rate, number_of_periods e present_value.

Tabela de amortização no Google Sheets — taxa de juros anual de 6%, prazo de 5 anos e valor do empréstimo de $70,000.00 em uma página chamada Schedule

2. Crie uma tabela com 5 colunas, cada uma correspondente a um dos dados necessários para criar a tabela de amortização.

Tabela de amortização no Google Sheets — cabeçalhos de coluna Month, Monthly Payment, Interest Payment, Principal Payment e Loan Balance

Como preencher a tabela de amortização do empréstimo com as funções PMT, PPMT e IPMT?

Como você viu na seção anterior, vai precisar adaptar três fórmulas aos seus dados. Veja como preencher a tabela de amortização com as funções PMT, PPMT e IPMT.

1. Dependendo do number_of_periods, você pode usar esta fórmula para preencher rapidamente a primeira coluna, =ArrayFormula(row(cell range)), como mostrado abaixo.

Tabela de amortização no Google Sheets — ArrayFormula com ROW numerando a coluna Month a partir de 1

2. Para preencher os dados de "Monthly Payment" (pagamento mensal), use a fórmula PMT só com os 3 primeiros argumentos. Depois, pressione "Enter".

Tabela de amortização no Google Sheets — fórmula PMT digitada na primeira célula de Monthly Payment

OBSERVAÇÃO: Divida a taxa por 12 e multiplique o prazo do empréstimo (se estiver em anos) por 12, já que um ano tem 12 meses.

3. Para preencher os dados de toda a coluna, adicione a fórmula de matriz à fórmula PMT. Pressione "Enter" para aplicar a fórmula.

Tabela de amortização no Google Sheets — ArrayFormula envolvendo a PMT na primeira célula de Monthly Payment

4. Agora sua tabela deve mostrar o pagamento mensal (Monthly Payment) de cada mês.

Tabela de amortização no Google Sheets — coluna Monthly Payment preenchida com o mesmo pagamento em todos os meses

5. Para calcular "Interest Payment" (pagamento de juros), digite a fórmula de matriz e a fórmula IPMT, como mostrado abaixo.

Tabela de amortização no Google Sheets — ArrayFormula com IPMT preenchendo a coluna Interest Payment

6. Para calcular "Principal Payment" (pagamento do principal), digite a fórmula de matriz e a fórmula PPMT, como mostrado abaixo.

Tabela de amortização no Google Sheets — ArrayFormula com PPMT preenchendo a coluna Principal Payment

7. Para calcular "Loan Balance" (saldo do empréstimo), você precisa subtrair o primeiro "Principal Payment" do valor inicial do empréstimo. Se os valores forem negativos, use "+" em vez de "menos" para subtrair. Pressione "Enter" para obter o resultado.

Tabela de amortização no Google Sheets — primeira célula de Loan Balance depois de subtrair o primeiro pagamento do principal

8. Selecione a célula em que você incluiu a fórmula do saldo do empréstimo e arraste-a para baixo até o último período. Se tiver feito tudo certo, a última célula deve conter o valor "0", como mostrado abaixo.

Tabela de amortização no Google Sheets — coluna Loan Balance arrastada para baixo até $0.00 no mês 60

Como criar uma tabela de amortização de empréstimos com pagamentos extras no Google Sheets?

Como você viu, criar uma tabela de amortização de empréstimos no Google Sheets com as funções PMT, PPMT e IPMT é um processo simples. Mas, se precisar incluir pagamentos extras do principal, você terá que seguir outros passos.

1. Adicione outra linha com "Payment" (pagamento), abaixo dos três valores de entrada principais, e uma nova coluna para "Extra Payment" (pagamento extra). Você vai precisar deixar uma célula vazia abaixo do nome de cada coluna, exceto em "Loan Balance".

Tabela de amortização com pagamentos extras no Google Sheets — nova linha Payment e coluna Extra Payments adicionadas à tabela

2. Desta vez, você vai calcular o pagamento periódico na célula que corresponde a "Payment". Você pode usar a fórmula PMT com os três primeiros argumentos, como mostrado abaixo.

Tabela de amortização com pagamentos extras no Google Sheets — fórmula PMT na célula Payment abaixo do valor do empréstimo

3. Inclua o valor do empréstimo na primeira célula da coluna "Loan Balance" digitando o sinal "=" seguido da referência da célula. Pressione "Enter".

Tabela de amortização com pagamentos extras no Google Sheets — primeira célula de Loan Balance mostrando o valor do empréstimo de $70,000.00

4. Para obter o valor da PMT a partir do novo valor de "Payment", você vai precisar combinar a fórmula IF (SE) com a fórmula PMT, como mostrado abaixo. Depois de digitá-la, pressione "Enter" para aplicar.

Tabela de amortização com pagamentos extras no Google Sheets — fórmula com IF e PMT retornando $1,353.30 na primeira célula de Monthly Payment

OBSERVAÇÃO: Essa combinação de IF+PMT verifica a coluna de saldo na linha de cima. Por exemplo, a célula D6 vai verificar o valor da célula H5. Se o valor de "Payment" for menor que o de "Loan Balance", o valor da PMT continua o mesmo. Se a PMT for maior que o saldo, a fórmula IF vai executar a segunda parte da fórmula.

5. Para obter o pagamento de juros, combine a fórmula IF com a IPMT, como mostrado abaixo.

Tabela de amortização com pagamentos extras no Google Sheets — fórmula com IF e IPMT retornando $350.00 na primeira célula de Interest Payment

6. Para obter o pagamento do principal, você também vai precisar combinar a fórmula IF com a PPMT, como mostrado abaixo.

Tabela de amortização com pagamentos extras no Google Sheets — fórmula com IF e PPMT na primeira célula de Principal Payment

7. Insira o valor em "Extra Payments" e arraste-o para baixo para copiar esse mesmo valor.

Tabela de amortização com pagamentos extras no Google Sheets — $700.00 copiado para baixo na coluna Extra Payments

8. Use esta fórmula para calcular o saldo do empréstimo na célula correspondente, logo abaixo do valor inicial do empréstimo, como mostrado abaixo.

Tabela de amortização com pagamentos extras no Google Sheets — fórmula do saldo do empréstimo na célula abaixo do saldo inicial de $70,000.00

9. Arraste para baixo até chegar a "0". Como você pode ver, as demais células mudaram e agora você sabe quantos pagamentos vai precisar fazer, com os pagamentos extras, para quitar o empréstimo.

Tabela de amortização com pagamentos extras no Google Sheets — Loan Balance chega a 0 no mês 53, com zeros nos meses seguintes

Conclusão

Como você viu, a tabela de amortização do Google Sheets pode ser personalizada de acordo com o tipo de empréstimo, bastando alterar os valores. Ter uma tabela de amortização também ajuda a determinar os pagamentos de juros e do principal do seu empréstimo ao longo de um período específico. Assim, você consegue ver como os pagamentos de juros diminuem enquanto os do principal aumentam.

Agora você deve entender melhor o que é uma tabela de amortização de empréstimos no Google Sheets e como prepará-la com as funções PMT, PPMT e IPMT. E, se precisar incluir pagamentos extras, também já sabe como adaptar a tabela de amortização a eles.

Se você está pagando mais de um empréstimo, estas planilhas de redução de dívidas ajudam você a decidir qual saldo quitar primeiro.