o tutorial mostra como construir um cronograma de amortização no Excel para detalhar pagamentos periódicos sobre um empréstimo ou hipoteca.um empréstimo amortizante é apenas uma forma elegante de definir um empréstimo que é pago em prestações ao longo de todo o período do empréstimo.basicamente, todos os empréstimos são amortizados de uma forma ou de outra. Por exemplo, um empréstimo totalmente amortizing por 24 meses terá 24 pagamentos mensais iguais., Cada pagamento aplica um determinado montante ao capital e outro aos juros. Para detalhar cada pagamento de um empréstimo, você pode construir um calendário de amortização de empréstimos.
um calendário de amortização é uma tabela que lista pagamentos periódicos sobre um empréstimo ou hipoteca ao longo do tempo, divide cada pagamento em capital e juros, e mostra o saldo remanescente após cada pagamento.,
- Como criar uma agenda de amortização de empréstimo no Excel
- plano de Amortização para um número variável de pontos
- agenda de amortização de Empréstimo com pagamentos adicionais
- Excel amortização modelo
Como criar uma agenda de amortização de empréstimo no Excel
Para criar um empréstimo ou hipoteca cronograma de amortização no Excel, vamos precisar de utilizar as funções seguintes:
- a função PGTO calcula a quantidade total de um pagamento periódico. Este montante permanece constante durante toda a duração do empréstimo.,função PPMT-recebe a parte principal de cada pagamento que vai para o capital do empréstimo, ou seja, o montante que você pediu emprestado. Este montante aumenta para os pagamentos posteriores.função IPMT-Encontra a parte de juros de cada pagamento que vai para os juros. Este montante diminui a cada pagamento.
Agora, vamos passar pelo processo passo a passo.,
Configurar a tabela de amortização
Para começar, defina a entrada de células onde você irá inserir o conhecido componentes de um empréstimo:
- C2 – taxa de juros anual
- C3 – prazo do empréstimo em anos
- C4 – número de pagamentos por ano
- C5 – montante do empréstimo
A próxima coisa a fazer é criar uma tabela de amortização, com as etiquetas (Período de Pagamento, Juros, capital, Equilíbrio) em A7:E7., No Período de coluna, digite uma série de números iguais para que o número total de pagamentos (1 – 24 neste exemplo):
Com os componentes no lugar, vamos para a parte mais interessante – amortização de empréstimo fórmulas.
Calcule total payment amount(PMT formula)
the payment amount is calculated with the PMT (rate, nper, pv,) function.(por exemplo, semanal, mensal, trimestral, etc.).,), você deve ser consistente com os valores fornecidos para a taxa e argumentos nper:
- taxa – dividir a taxa de juro anual pelo número de períodos de pagamento por ano ($C$2/$C$4).
- Nper – multiplique o número de anos pelo número de períodos de pagamento por ano ($C$3*$C$4).
- para o argumento fotovoltaico, indique o montante do empréstimo ($C$5).
- Os argumentos fv e tipo podem ser omitidos, uma vez que os seus valores por defeito funcionam muito bem para nós (o saldo após o último pagamento é suposto ser 0; Os pagamentos são feitos no final de cada período).,
Colocar os argumentos acima conjunto, temos a seguinte fórmula:
=PMT($C$2/$C$4, $C$3*$C$4, $C$5)
por Favor, preste atenção, que podemos utilizar referências de célula absolutas porque esta fórmula deve copiar para o abaixo células sem quaisquer alterações.,
Insira o PGTO fórmula em B8, arraste para baixo a coluna, e você verá uma constante de valor de pagamento para todos os períodos:
Calcular juros (IPMT fórmula)
Para encontrar a parte do juro de cada pagamento periódico, use a função IPGTO(taxa de, por, nper, pv, , ) função:
=IPMT($C$2/$C$4, A8, $C$3*$C$4, $C$5)
Todos os argumentos são os mesmos, como no PMT fórmula, exceto por argumento de que especifica o período de pagamento., Este argumento é fornecido como uma referência de célula relativa (A8) porque é suposto mudar com base na posição relativa de uma linha para a qual a fórmula é copiada.,
Esta fórmula vai para C8 e, em seguida, copie-o para baixo como muitos células como necessário:
Encontrar principal (PPMT fórmula)
Para calcular a parte principal de cada pagamento periódico, use este PPGTO fórmula:
=PPMT($C$2/$C$4, A8, $C$3*$C$4, $C$5)
A sintaxe e os argumentos são exatamente os mesmos do IPMT fórmula acima discutidos:
Esta fórmula vai para a coluna D, com início em D8:
obter o saldo restante
para calcular o saldo restante para cada período, estaremos usando duas fórmulas diferentes.
Para encontrar o equilíbrio após o primeiro pagamento em E8, adicionar o valor do empréstimo (C5) e o principal do primeiro período (D8):
=C5+D8
Devido o montante do empréstimo é um número positivo e a principal é um número negativo, o último, na verdade, é subtraído o antigo.,
para o segundo e todos os períodos subsequentes, adicione o saldo anterior e o principal deste período:
=E8+D9
a fórmula acima vai para E9, E então você copia para baixo na coluna. Devido ao uso de referências de células relativas, a fórmula ajusta corretamente para cada linha.é isso! Nosso cronograma mensal de amortização de empréstimos é feito:
Tip: Return payments as positive numbers
Because a loan is paid out of your bank account, Excel functions return the payment, interest and principal as negative numbers., Por padrão, estes valores são realçados em vermelho e fechados entre parêntesis, como você pode ver na imagem acima.
Se preferir ter todos os resultados como números positivos, coloque um sinal menos antes das funções PMT, IPMT e PPMT.
Para o Equilíbrio fórmulas, use a subtração, em vez de adição, como mostrado na imagem abaixo:
plano de Amortização para um número variável de pontos
No exemplo acima, criamos uma agenda de amortização de empréstimo para o número predefinido de períodos de pagamento., Esta solução rápida de uma só vez funciona bem para um empréstimo específico ou hipoteca.
Se você está olhando para criar um cronograma de amortização reutilizável com um número variável de períodos, você terá que tomar uma abordagem mais abrangente descrita abaixo.
introduza o número máximo de períodos
na coluna do período, insira o número máximo de pagamentos que irá permitir para qualquer empréstimo, digamos, de 1 a 360. Você pode alavancar a funcionalidade de preenchimento automático do Excel para introduzir uma série de números mais rápido.,
Use se as declarações em fórmulas de amortização
porque você agora tem muitos números de período excessivo, você tem que de alguma forma limitar os cálculos para o número real de pagamentos para um determinado empréstimo. Isto pode ser feito embrulhando cada fórmula em uma declaração IF. O teste lógico da declaração de FI verifica se o número do período na linha actual é inferior ou igual ao número total de pagamentos. Se o teste lógico for verdadeiro, a função correspondente é calculada; se falso, uma cadeia vazia é retornada.,
assumindo que o período 1 está na linha 8, indique as seguintes fórmulas nas células correspondentes, e depois copie-as por toda a tabela.,
de Pagamento (B8):
=IF(A8<=$C$3*$C$4, PMT($C$2/$C$4, $C$3*$C$4, $C$5), "")
de Juros (C8):
=IF(A8<=$C$3*$C$4, IPMT($C$2/$C$4, A8, $C$3*$C$4, $C$5), "")
Principal (D8):
=IF(A8<=$C$3*$C$4,PPMT($C$2/$C$4, A8, $C$3*$C$4, $C$5), "")
Equilíbrio:
Para o Período de 1 (E8), a fórmula é a mesma, como no exemplo anterior:
=C5+D8
Para o Período de 2 (E9) e todos os períodos subsequentes, a fórmula tem esta forma:
=IF(A9<=$C$3*$C$4, E8+D9, "")
Como resultado, você tem um correctamente calculado cronograma de amortização e de um monte de linhas em branco com os números de período após o empréstimo é liquidado.,
Ocultar períodos extra números
Se você pode viver com um monte de supérfluo período de números que aparecem após o último pagamento, você pode considerar o trabalho feito e ignorar este passo. Se você se esforçar para a perfeição, em seguida, esconder todos os períodos não utilizados, fazendo uma regra de formatação condicional que define a cor da fonte para branco para qualquer linha após o último pagamento é feito.,
Para isso, selecione todas as linhas de dados se a sua amortização (tabela A8:E367 no nosso caso) e clique em Casa separador > formatação Condicional > Nova Regra… > Usar uma fórmula para determinar quais células devem ser formatadas.
Na caixa correspondente, digite a fórmula abaixo que verifica se o número de período na coluna A é maior do que o número total de pagamentos:
=$A8>$C$3*$C$4
Depois disso, carregue no botão Formato… e escolha a cor branca da fonte. Feito!,
faça um resumo do empréstimo
para ver as informações resumidas sobre o seu empréstimo num relance, adicione mais algumas fórmulas no topo do seu calendário de amortização.
Total de pagamentos (F2):
=-SUM(B8:B367)
Total de juros (F3):
=-SUM(C8:C367)
Se você tiver pagamentos como números positivos, remova o sinal de subtração das fórmulas acima.é isso! O nosso calendário de amortização dos empréstimos está completo e pronto!,
Download loan amortization schedule for Excel
How to make a loan amortization schedule with extra payments in Excel
The amortization schedules discussed in the previous examples are easy to create and follow (esperançosamente :). No entanto, eles deixam de fora uma característica útil que muitos pagadores de empréstimos estão interessados em-pagamentos adicionais para pagar um empréstimo mais rápido. Neste exemplo, vamos olhar para como criar um calendário de amortização de empréstimos com pagamentos extras.
defina as células de entrada
como de costume, comece por configurar as células de entrada., Neste caso, vamos nome destas células, como escrito abaixo para fazer nossas fórmulas mais fácil de ler:
- InterestRate – C2 (taxa de juro anual)
- LoanTerm – C3 (prazo do empréstimo em anos)
- PaymentsPerYear – C4 (número de pagamentos por ano)
- LoanAmount – C5 (total montante do empréstimo)
- ExtraPayment – C6 (pagamento extra por período)
Calcular um agendamento de pagamento
Além das células de entrada, mais uma célula predefinida é necessária para os nossos cálculos mais – agendada valor do pagamento, i.é., o montante a pagar sobre um empréstimo se não forem feitos pagamentos adicionais. Este montante é calculado com a seguinte fórmula:
=IFERROR(-PMT(InterestRate/PaymentsPerYear, LoanTerm*PaymentsPerYear, LoanAmount), "")
por favor, preste atenção que colocamos um sinal menos antes da função PMT para ter o resultado como um número positivo. Para evitar erros no caso de algumas das células de entrada estarem vazias, anexamos a fórmula PMT dentro da função IFERROR.
introduza esta fórmula em alguma célula (G2 no nosso caso) e nomeie essa célula como pagamento escalonado.,
configure a tabela de amortização
crie uma tabela de amortização de empréstimos com os cabeçalhos mostrados na imagem abaixo. Na coluna do período, indique uma série de números que comecem por zero (poderá esconder a linha do período 0 mais tarde, se necessário).
Se pretender criar um esquema de amortização reutilizável, indique o número máximo possível de períodos de pagamento (0 a 360 neste exemplo).
para o período 0 (linha 9 no nosso caso), puxe o valor do saldo, que é igual ao montante do empréstimo original., Todas as outras células nessa linha permanecerá vazio:
Fórmula em G9:
=LoanAmount
4. Compilar fórmulas para o calendário de amortização com pagamentos extras
esta é uma parte chave do nosso trabalho. Como as funções incorporadas do Excel não fornecem pagamentos adicionais, teremos que fazer todas as contas por conta própria.Nota: Neste exemplo, o período 0 está na linha 9 e o período 1 na linha 10. Se a sua tabela de amortização começar numa linha diferente, não se esqueça de ajustar as referências das células em conformidade.,
introduza as seguintes fórmulas na linha 10 (período 1), e depois copie-as para todos os períodos restantes.
pagamento agendado (B10):
Se o montante do pagamento agendado (chamada célula G2) for menor ou igual ao saldo restante (G9), use o pagamento agendado. Caso contrário, adicione o saldo restante e os juros do mês anterior.
=IFERROR(IF(ScheduledPayment<=G9, ScheduledPayment, G9+G9*InterestRate/PaymentsPerYear), "")
Como uma precaução extra, nós embrulhamos isto e todas as fórmulas subsequentes na função IFERROR., Isto irá evitar vários erros se algumas das células de entrada estiverem vazias ou contiverem valores inválidos.
pagamento Extra (C10):
Use uma fórmula IF com a seguinte lógica:
Se o montante do pagamento extrapatrimonial (chamada célula C6) for inferior à diferença entre o saldo remanescente e o capital deste período (G9-E10), return ExtraPayment; caso contrário use a diferença.
=IFERROR(IF(ExtraPayment<G9-E10, ExtraPayment, G9-E10), "")
Total de Pagamento (D10)
Simplesmente, adicione o pagamento periódico (B10) e o pagamento extra (C10) para o período corrente:
=IFERROR(B10+C10, "")
Principal (E10)
Se o calendário de pagamento para um determinado período for maior que zero, um retorno menor dos dois valores: pagamento periódico menos juros (B10-F10) ou o saldo remanescente (G9); caso contrário, retornam zero.
=IFERROR(IF(B10>0, MIN(B10-F10, G9), 0), "")
Please note that the principal only includes the part of the scheduled payment (not the extra payment!) que vai para o empréstimo principal.,juros (F10) se o pagamento do calendário para um determinado período for superior a zero, dividir a taxa de juro anual (célula C2) pelo número de pagamentos por ano (célula C4) e multiplicar o resultado pelo saldo remanescente após o período anterior; caso contrário, devolver 0.
=IFERROR(IF(B10>0, InterestRate/PaymentsPerYear*G9, 0), "")
Saldo (G10)
Se o saldo remanescente (G9) é maior que zero, subtrai-se a principal parte do pagamento (E10) e o pagamento extra (C10) do saldo remanescente após o período anterior (G9); caso contrário, retorna 0.,
Se tudo for feito correctamente, o seu calendário de amortização do empréstimo neste ponto deverá ser parecido com isto:
Esconder períodos extras
Configure uma regra de formatação condicional para esconder os valores em períodos não utilizados, como explicado nesta dica.,pagamentos por ano:
=LoanTerm*PaymentsPerYear
número Real de pagamentos:
a Contagem de células no Pagamento Total da coluna que são maiores do que zero, começando com o Período de 1:
=COUNTIF(D10:D369,">"&0)
Total de pagamentos extra:
Adicionar células no Pagamento Extra de coluna, começando com o Período de 1:
=SUM(C10:C369)
Total de juros:
Adicionar células no Interesse da coluna, começando com o Período de 1:
=SUM(F10:F369)
Opcionalmente, ocultar o Período de 0 linha, e sua agenda de amortização de empréstimo com pagamentos adicionais, que é feito!, A imagem abaixo mostra o resultado final:
Download loan amortization schedule with extra payments
Amortization schedule Excel
para fazer um esquema de amortização de empréstimo de topo em nenhum momento, faça uso dos modelos inbuilt do Excel. Vá em Arquivo > Novo tipo de “agenda de amortização” na caixa de pesquisa e escolher o modelo que você gosta, por exemplo, esta com pagamentos extra:
isso é como você criar um empréstimo ou hipoteca cronograma de amortização no Excel., Agradeço-lhe por ler e espero vê-lo em nosso blog na próxima semana!
downloads Disponíveis
a Agenda de Amortização de pasta de trabalho de Exemplo
- Como calcular juros compostos no Excel
- Como encontrar CAGR (taxa de crescimento anual composta) no Excel
- Cálculo de porcentagem no Excel com exemplos de fórmulas