Aprenda a calcular amortização de empréstimos com gráficos no Excel usando fórmulas, tabelas Price e SAC. Tutorial completo com alguns exemplos de VBA.
Calcular a amortização de empréstimos no Excel é uma habilidade essencial para profissionais financeiros, empresários e qualquer pessoa que deseja controlar suas dívidas de forma eficiente. Com as ferramentas certas, você pode criar planilhas personalizadas que mostram exatamente quanto pagará de juros, parcelas e saldo devedor ao longo do tempo.
Neste guia completo, você aprenderá a construir sistemas de amortização utilizando os métodos mais populares no Brasil: Tabela Price (Sistema Francês) e SAC (Sistema de Amortização Constante). Além disso, mostraremos como criar gráficos dinâmicos e automatizar cálculos com macros VBA para deixar sua planilha profissional.
Índice
- O Que é Amortização de Empréstimos?
- Criando a Tabela de Amortização Básica no Excel
- Fórmulas para Sistema Price no Excel
- Fórmulas para Sistema SAC no Excel
- Código VBA para Automatizar Sistema Price
- Código VBA para Automatizar Sistema SAC
- Criando Gráficos Dinâmicos de Amortização
- Dicas Práticas para Análise de Empréstimos
- Benefícios de Calcular Amortização no Excel
- Conclusão
- Perguntas Frequentes
- 1. Qual a diferença prática entre Sistema Price e SAC para quem vai pegar empréstimo?
- 2. Como usar a função PGTO do Excel corretamente para calcular prestações?
- 3. É possível calcular quanto economizo fazendo amortização extraordinária no Excel?
- 4. Os códigos VBA funcionam em todas as versões do Excel?
- 5. Como adicionar IOF e outras taxas no cálculo de amortização?
- 6. Posso usar essas planilhas para financiamento imobiliário com carência?
O Que é Amortização de Empréstimos?
Amortização é o processo de pagamento gradual de uma dívida através de parcelas periódicas. Cada prestação contém duas partes: a amortização do principal (valor emprestado) e os juros calculados sobre o saldo devedor.
Existem dois sistemas principais de amortização utilizados no mercado brasileiro:
Sistema Price (Tabela Price): As parcelas são fixas durante todo o período. No início, você paga mais juros e menos amortização. Com o tempo, essa proporção se inverte.
Sistema SAC: A amortização é constante em todas as parcelas, mas os juros diminuem a cada mês. Isso resulta em prestações decrescentes ao longo do tempo.
Criando a Tabela de Amortização Básica no Excel
Antes de automatizar com VBA, vamos construir a estrutura básica da planilha. Você precisará de campos para os dados principais do empréstimo.
Dados Necessários
Configure sua planilha com os seguintes parâmetros iniciais:
- Valor do empréstimo (principal)
- Taxa de juros mensal (em porcentagem)
- Número de parcelas (prazo)
- Sistema de amortização escolhido
Estrutura da Tabela
Crie uma tabela com as seguintes colunas:
- Número da parcela
- Saldo inicial
- Amortização
- Juros
- Prestação total
- Saldo final
Fórmulas para Sistema Price no Excel
O Sistema Price mantém as prestações fixas durante todo o financiamento. Veja as fórmulas essenciais:
Cálculo da Prestação Fixa
=PGTO(taxa; nper; vp)
Onde:
- taxa = taxa de juros mensal
- nper = número total de parcelas
- vp = valor presente (empréstimo)
Cálculo dos Juros Mensais
=Saldo_Anterior * Taxa_Juros
Cálculo da Amortização
=Prestação - Juros
Exemplo Prático com Valores
Considere um empréstimo de R$ 50.000,00 a 1,5% ao mês em 24 parcelas:
| Parcela | Saldo Inicial | Amortização | Juros | Prestação | Saldo Final |
|---|---|---|---|---|---|
| 1 | R$ 50.000,00 | R$ 1.507,82 | R$ 750,00 | R$ 2.257,82 | R$ 48.492,18 |
| 2 | R$ 48.492,18 | R$ 1.530,44 | R$ 727,38 | R$ 2.257,82 | R$ 46.961,74 |
| 3 | R$ 46.961,74 | R$ 1.553,39 | R$ 704,43 | R$ 2.257,82 | R$ 45.408,35 |
Fórmulas para Sistema SAC no Excel
No Sistema SAC, a amortização permanece constante enquanto os juros e prestações diminuem.
Cálculo da Amortização Constante
=Valor_Emprestimo / Numero_Parcelas
Cálculo dos Juros Mensais
=Saldo_Devedor * Taxa_Juros
Cálculo da Prestação
=Amortização + Juros
Exemplo Comparativo SAC
Mesmo empréstimo de R$ 50.000,00 a 1,5% ao mês em 24 parcelas:
| Parcela | Saldo Inicial | Amortização | Juros | Prestação | Saldo Final |
|---|---|---|---|---|---|
| 1 | R$ 50.000,00 | R$ 2.083,33 | R$ 750,00 | R$ 2.833,33 | R$ 47.916,67 |
| 2 | R$ 47.916,67 | R$ 2.083,33 | R$ 718,75 | R$ 2.802,08 | R$ 45.833,34 |
| 3 | R$ 45.833,34 | R$ 2.083,33 | R$ 687,50 | R$ 2.770,83 | R$ 43.750,01 |
Código VBA para Automatizar Sistema Price
Agora vamos criar uma macro que calcula automaticamente a tabela Price. Cole este código no Editor VBA (Alt + F11):
Sub CalcularTabelaPrice()
Dim valorEmprestimo As Double
Dim taxaJuros As Double
Dim numeroParcelas As Integer
Dim prestacao As Double
Dim saldoDevedor As Double
Dim amortizacao As Double
Dim juros As Double
Dim i As Integer
' Limpar dados anteriores
Range("A10:F100").ClearContents
' Ler valores de entrada
valorEmprestimo = Range("B2").Value
taxaJuros = Range("B3").Value / 100
numeroParcelas = Range("B4").Value
' Calcular prestação fixa usando fórmula Price
prestacao = WorksheetFunction.Pmt(taxaJuros, numeroParcelas, -valorEmprestimo)
' Exibir prestação calculada
Range("B5").Value = prestacao
' Cabeçalhos da tabela
Range("A9").Value = "Parcela"
Range("B9").Value = "Saldo Inicial"
Range("C9").Value = "Juros"
Range("D9").Value = "Amortização"
Range("E9").Value = "Prestação"
Range("F9").Value = "Saldo Final"
' Inicializar saldo devedor
saldoDevedor = valorEmprestimo
' Loop para calcular cada parcela
For i = 1 To numeroParcelas
' Número da parcela
Cells(9 + i, 1).Value = i
' Saldo inicial
Cells(9 + i, 2).Value = saldoDevedor
Cells(9 + i, 2).NumberFormat = "R$ #,##0.00"
' Calcular juros
juros = saldoDevedor * taxaJuros
Cells(9 + i, 3).Value = juros
Cells(9 + i, 3).NumberFormat = "R$ #,##0.00"
' Calcular amortização
amortizacao = prestacao - juros
Cells(9 + i, 4).Value = amortizacao
Cells(9 + i, 4).NumberFormat = "R$ #,##0.00"
' Prestação
Cells(9 + i, 5).Value = prestacao
Cells(9 + i, 5).NumberFormat = "R$ #,##0.00"
' Atualizar saldo devedor
saldoDevedor = saldoDevedor - amortizacao
Cells(9 + i, 6).Value = saldoDevedor
Cells(9 + i, 6).NumberFormat = "R$ #,##0.00"
Next i
' Formatar tabela
Range("A9:F9").Font.Bold = True
Range("A9:F" & 9 + numeroParcelas).Borders.LineStyle = xlContinuous
MsgBox "Tabela Price calculada com sucesso!", vbInformation
End Sub
Código VBA para Automatizar Sistema SAC
Esta macro calcula a amortização pelo Sistema SAC com prestações decrescentes:
Sub CalcularTabelaSAC()
Dim valorEmprestimo As Double
Dim taxaJuros As Double
Dim numeroParcelas As Integer
Dim amortizacaoConstante As Double
Dim saldoDevedor As Double
Dim juros As Double
Dim prestacao As Double
Dim i As Integer
' Limpar dados anteriores
Range("A10:F100").ClearContents
' Ler valores de entrada
valorEmprestimo = Range("B2").Value
taxaJuros = Range("B3").Value / 100
numeroParcelas = Range("B4").Value
' Calcular amortização constante
amortizacaoConstante = valorEmprestimo / numeroParcelas
' Cabeçalhos da tabela
Range("A9").Value = "Parcela"
Range("B9").Value = "Saldo Inicial"
Range("C9").Value = "Juros"
Range("D9").Value = "Amortização"
Range("E9").Value = "Prestação"
Range("F9").Value = "Saldo Final"
' Inicializar saldo devedor
saldoDevedor = valorEmprestimo
' Loop para calcular cada parcela
For i = 1 To numeroParcelas
' Número da parcela
Cells(9 + i, 1).Value = i
' Saldo inicial
Cells(9 + i, 2).Value = saldoDevedor
Cells(9 + i, 2).NumberFormat = "R$ #,##0.00"
' Calcular juros sobre saldo devedor
juros = saldoDevedor * taxaJuros
Cells(9 + i, 3).Value = juros
Cells(9 + i, 3).NumberFormat = "R$ #,##0.00"
' Amortização constante
Cells(9 + i, 4).Value = amortizacaoConstante
Cells(9 + i, 4).NumberFormat = "R$ #,##0.00"
' Calcular prestação
prestacao = amortizacaoConstante + juros
Cells(9 + i, 5).Value = prestacao
Cells(9 + i, 5).NumberFormat = "R$ #,##0.00"
' Atualizar saldo devedor
saldoDevedor = saldoDevedor - amortizacaoConstante
Cells(9 + i, 6).Value = saldoDevedor
Cells(9 + i, 6).NumberFormat = "R$ #,##0.00"
Next i
' Formatar tabela
Range("A9:F9").Font.Bold = True
Range("A9:F" & 9 + numeroParcelas).Borders.LineStyle = xlContinuous
MsgBox "Tabela SAC calculada com sucesso!", vbInformation
End Sub
Criando Gráficos Dinâmicos de Amortização
Os gráficos tornam a visualização do empréstimo muito mais intuitiva. Veja como criar representações visuais eficientes.
Gráfico de Evolução do Saldo Devedor
- Selecione as colunas "Parcela" e "Saldo Final"
- Vá em Inserir > Gráficos > Gráfico de Linhas
- Personalize com título "Evolução do Saldo Devedor"
- Adicione rótulos aos eixos
Gráfico Comparativo Juros vs Amortização
Este gráfico mostra como a composição das parcelas muda ao longo do tempo:
- Selecione as colunas "Parcela", "Juros" e "Amortização"
- Insira um Gráfico de Colunas Empilhadas
- Use cores contrastantes para diferenciar juros e amortização
- Adicione legenda explicativa
Código VBA para Criar Gráfico Automaticamente
Sub CriarGraficoAmortizacao()
Dim ws As Worksheet
Dim chartObj As ChartObject
Dim ultimaLinha As Long
Set ws = ActiveSheet
' Encontrar última linha com dados
ultimaLinha = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
' Remover gráficos anteriores
For Each chartObj In ws.ChartObjects
chartObj.Delete
Next chartObj
' Criar gráfico de linhas - Saldo Devedor
Set chartObj = ws.ChartObjects.Add(Left:=500, Width:=450, Top:=50, Height:=250)
With chartObj.Chart
.SetSourceData Source:=ws.Range("A9:A" & ultimaLinha & ",F9:F" & ultimaLinha)
.ChartType = xlLine
.HasTitle = True
.ChartTitle.Text = "Evolução do Saldo Devedor"
.Axes(xlCategory).HasTitle = True
.Axes(xlCategory).AxisTitle.Text = "Parcelas"
.Axes(xlValue).HasTitle = True
.Axes(xlValue).AxisTitle.Text = "Valor (R$)"
End With
' Criar gráfico de colunas empilhadas - Juros vs Amortização
Set chartObj = ws.ChartObjects.Add(Left:=500, Width:=450, Top:=320, Height:=250)
With chartObj.Chart
.SetSourceData Source:=ws.Range("A9:A" & ultimaLinha & ",C9:D" & ultimaLinha)
.ChartType = xlColumnStacked
.HasTitle = True
.ChartTitle.Text = "Composição das Parcelas"
.Axes(xlCategory).HasTitle = True
.Axes(xlCategory).AxisTitle.Text = "Parcelas"
.Axes(xlValue).HasTitle = True
.Axes(xlValue).AxisTitle.Text = "Valor (R$)"
.HasLegend = True
.Legend.Position = xlLegendPositionBottom
End With
MsgBox "Gráficos criados com sucesso!", vbInformation
End Sub
Dicas Práticas para Análise de Empréstimos
Maximize o uso da sua planilha com estas recomendações profissionais:
Compare os sistemas: Crie abas separadas para Price e SAC no mesmo arquivo. Assim você visualiza rapidamente qual oferece melhor custo total.
Calcule o CET: Inclua o Custo Efetivo Total considerando tarifas, seguros e impostos além dos juros. Use a função TIR (Taxa Interna de Retorno) para isso.
Simule pagamentos extras: Adicione uma coluna para amortizações extraordinárias e veja o impacto na redução de juros totais.
Use validação de dados: Configure listas suspensas para escolher entre os sistemas de amortização, evitando erros de digitação.
Proteja as fórmulas: Bloqueie as células com cálculos para que apenas os campos de entrada possam ser modificados.
Crie cenários: Utilize a ferramenta Gerenciador de Cenários do Excel para salvar diferentes simulações de taxas e prazos.
Se ainda tem dúvidas, então baixe a planilha com exemplos práticos de amortização de empréstimos e modifique conforme suas necessidades.
Benefícios de Calcular Amortização no Excel
Dominar o cálculo de amortização de empréstimos com gráficos no Excel traz diversas vantagens práticas e profissionais.
Autonomia financeira: Você não depende de calculadoras online ou simuladores de bancos. Pode verificar se as prestações cobradas estão corretas e identificar inconsistências.
Personalização total: Adapte a planilha às suas necessidades específicas, incluindo seguros, taxas administrativas, IOF e outros custos que ferramentas genéricas não consideram.
Análise comparativa: Simule diferentes cenários alterando valores, taxas e prazos instantaneamente. Compare propostas de várias instituições financeiras lado a lado.
Visualização clara: Os gráficos transformam números complexos em informações visuais fáceis de entender, facilitando decisões e apresentações.
Economia de dinheiro: Identifique oportunidades de economia, como o momento ideal para fazer amortizações extraordinárias ou refinanciamento de dívidas.
Profissionalização: Para consultores financeiros, contadores e empresários, oferecer análises personalizadas aumenta o valor percebido dos seus serviços.
Controle de portfólio: Gerencie múltiplos empréstimos em uma única planilha, acompanhando o comprometimento total da sua renda.
Aprendizado prático: Compreender as fórmulas e a lógica por trás dos cálculos melhora sua educação financeira e capacidade de negociação.
Conclusão
Calcular a amortização de empréstimos com gráficos no Excel é uma competência valiosa que combina conhecimento financeiro com habilidades técnicas. Ao dominar as fórmulas dos sistemas Price e SAC, você ganha controle total sobre suas análises de crédito e financiamento.
As macros VBA apresentadas automatizam processos repetitivos, economizando tempo e eliminando erros manuais. Os gráficos dinâmicos transformam dados numéricos em visualizações intuitivas que facilitam a tomada de decisão.
Com a planilha que você criou seguindo este tutorial, é possível simular diferentes cenários, comparar propostas de instituições financeiras e entender exatamente para onde seu dinheiro está indo em cada parcela. Essa transparência é fundamental para negociar melhores condições e fazer escolhas financeiras mais conscientes.
Continue praticando e personalizando sua planilha conforme suas necessidades específicas. O Excel oferece recursos praticamente ilimitados para análise financeira, e este é apenas o começo da sua jornada.
Perguntas Frequentes
1. Qual a diferença prática entre Sistema Price e SAC para quem vai pegar empréstimo?
No Sistema Price você paga parcelas fixas durante todo o financiamento, o que facilita o planejamento mensal. Porém, nos primeiros meses você paga mais juros e menos amortização do principal. No Sistema SAC as parcelas começam mais altas mas diminuem progressivamente, pois a amortização é constante e os juros incidem sobre um saldo cada vez menor. No total, o SAC costuma ter menor custo de juros, mas exige maior capacidade de pagamento inicial.
2. Como usar a função PGTO do Excel corretamente para calcular prestações?
A sintaxe correta é PGTO(taxa; nper; vp; vf; tipo). Os argumentos obrigatórios são: taxa (juros por período em formato decimal, como 0,015 para 1,5%), nper (número total de parcelas) e vp (valor presente, que deve ser negativo se for empréstimo). Os opcionais são vf (valor futuro, geralmente 0) e tipo (0 para pagamento no fim do período, 1 para início). Exemplo: para R$ 100.000 em 36 meses a 1,2% ao mês, use =PGTO(0,012; 36; -100000).
3. É possível calcular quanto economizo fazendo amortização extraordinária no Excel?
Sim, basta criar duas tabelas paralelas: uma com o plano original e outra incluindo a amortização extra. Na segunda tabela, reduza o saldo devedor na parcela em que fará o pagamento adicional. Compare o total de juros pagos nas duas simulações para ver a economia exata. Você também pode usar o recurso de Teste de Hipótese para ver como diferentes valores de amortização impactam o custo final do empréstimo.
4. Os códigos VBA funcionam em todas as versões do Excel?
Os códigos apresentados são compatíveis com Excel 2010 ou superior, incluindo Microsoft 365. Para usar macros, você precisa habilitar o conteúdo ativo ao abrir a planilha e ter acesso ao Editor VBA (Alt + F11). No Excel Online a funcionalidade VBA é limitada, sendo necessário usar a versão desktop. No LibreOffice Calc o código pode precisar de pequenos ajustes, pois usa uma sintaxe ligeiramente diferente para macros.
5. Como adicionar IOF e outras taxas no cálculo de amortização?
O IOF e tarifas devem ser somados ao valor financiado antes de calcular as parcelas. Por exemplo: se você quer R$ 50.000 líquidos mas há 3% de IOF (R$ 1.500) e R$ 800 de tarifa de cadastro, o valor total financiado será R$ 52.300. Use este valor nas fórmulas de cálculo. Para encontrar o Custo Efetivo Total (CET), que inclui todos os encargos, use a função TIR do Excel considerando o valor líquido recebido e todas as parcelas pagas.
6. Posso usar essas planilhas para financiamento imobiliário com carência?
Sim, mas você precisa adaptar a lógica. Durante o período de carência há duas possibilidades: carência total (não paga nada, juros são capitalizados) ou carência parcial (paga só juros). Crie linhas específicas para o período de carência onde, na carência total, a amortização e prestação são zero mas o saldo cresce com juros capitalizados. Na carência parcial, a amortização é zero e a prestação igual aos juros. Após a carência, inicie a amortização normal usando o saldo devedor atualizado.
