Fórmula para calcular um pagamento de anuidade no Excel

Os pagamentos de empréstimos são mais fáceis e rápidos de calcular com o Microsoft Office Excel. Muito mais tempo é gasto no cálculo manual. Este artigo se concentrará nos pagamentos de anuidades, nas características de seu cálculo, vantagens e desvantagens.

O que é um pagamento de anuidade

Um método de pagamento mensal de um empréstimo, no qual o valor depositado não muda durante todo o período do empréstimo. Aqueles. em determinadas datas de cada mês, uma pessoa deposita uma quantia específica de dinheiro até que o empréstimo seja totalmente reembolsado.

Além disso, os juros do empréstimo já estão incluídos no valor total pago ao banco.

Anuidade de classificação

Os pagamentos de anuidades podem ser divididos nos seguintes tipos:

1. Fixo. Os pagamentos que não mudam têm uma taxa fixa, independentemente das condições externas.
2. Moeda. A capacidade de alterar o valor do pagamento em caso de queda ou aumento da taxa de câmbio.
3. indexado. Pagamentos dependendo do nível, indicador de inflação. Durante o período do empréstimo, seu tamanho muda frequentemente.
4. Variáveis. Anuidade, que pode mudar dependendo do estado do sistema financeiro, instrumentos.

Preste atenção! Os pagamentos fixos são preferíveis para todos os mutuários, porque têm pouco risco.

Vantagens e desvantagens do pagamento de anuidade

Para entender melhor o tema, é necessário estudar as principais características desse tipo de pagamento de empréstimo. Tem as seguintes vantagens:

• Estabelecendo um valor específico de pagamento e a data de seu pagamento.
• Alta disponibilidade para os mutuários. Quase qualquer pessoa pode solicitar uma anuidade, independentemente de sua situação financeira.
• Possibilidade de baixar o valor da prestação mensal com aumento da inflação.

Não sem inconvenientes:

• Nota alta. O mutuário pagará em excesso uma quantidade maior de dinheiro em comparação com o pagamento diferencial.
• Problemas decorrentes do desejo de pagar a dívida antes do previsto.
• Não há recálculos para pagamentos antecipados.

Qual é o pagamento do empréstimo?

O pagamento da anuidade tem os seguintes componentes:

• Juros pagos por uma pessoa ao pagar um empréstimo.
• Parte do valor principal.

Como resultado, o valor total dos juros quase sempre excede o valor contribuído pelo mutuário para reduzir a dívida.

Fórmula básica de pagamento de anuidade em Excel

Como mencionado acima, no Microsoft Office Excel você pode trabalhar com vários tipos de pagamentos de empréstimos e adiantamentos. A anuidade não é exceção. Em geral, a fórmula com a qual você pode calcular rapidamente as contribuições de anuidades é a seguinte:  

Importante! É impossível abrir colchetes no denominador desta expressão para simplificá-la.

Os principais valores da fórmula são decifrados da seguinte forma:

• AP – pagamento de anuidade (o nome é abreviado).
• O – o tamanho da dívida principal do mutuário.
• PS – a taxa de juros apresentada mensalmente por um determinado banco.
• C é o número de meses que o empréstimo dura.

Para assimilar as informações, basta dar alguns exemplos do uso dessa fórmula. Eles serão discutidos mais adiante.

Exemplos de uso da função PMT no Excel

Damos uma condição simples do problema. É necessário calcular o pagamento mensal do empréstimo se o banco apresentar juros de 23% e o valor total for de 25000 rublos. O empréstimo terá a duração de 3 anos. O problema é resolvido de acordo com o algoritmo:

1. Faça uma planilha geral no Excel com base nos dados de origem.

2. Ative a função PMT e insira argumentos para ela na caixa apropriada.
3. No campo “Aposta”, digite a fórmula “B3/B5”. Esta será a taxa de juros do empréstimo.
4. Na linha “Nper” escreva o valor na forma “B4*B5”. Este será o número total de pagamentos para todo o prazo do empréstimo.
5. Preencha o campo “PS”. Aqui você precisa indicar o valor inicial retirado do banco, escrevendo o valor “B2”.

6. Certifique-se de que após clicar em “OK” na tabela de origem, o valor “Pagamento mensal” foi calculado.

Informação adicional! Um número negativo indica que o mutuário está gastando dinheiro.

Um exemplo de cálculo do valor do pagamento a maior de um empréstimo no Excel

Neste problema, você precisa calcular o valor que uma pessoa que tomou um empréstimo de 50000 rublos a uma taxa de juros de 27% por 5 anos pagará a mais. No total, o mutuário faz 12 pagamentos por ano. Solução:

1. Compile a tabela de dados original.

2. Do valor total dos pagamentos, subtraia o valor inicial de acordo com a fórmula «=ABS(ПЛТ(B3/B5;B4*B5;B2)*B4*B5)-B2». Deve ser inserido na barra de fórmulas na parte superior do menu principal do programa.
3. Como resultado, o valor dos pagamentos a maior aparecerá na última linha da placa criada. O mutuário pagará a mais de 41606 rublos no topo.

A fórmula para calcular o pagamento mensal ideal do empréstimo no Excel

Uma tarefa com a seguinte condição: o cliente registrou uma conta bancária de 200000 rublos com possibilidade de reabastecimento mensal. É necessário calcular o valor do pagamento que uma pessoa deve fazer todos os meses, para que, após 4 anos, ela tenha 2000000 rublos em sua conta. A taxa é de 11%. Solução:

1. Crie uma planilha com base nos dados originais.

2. Insira a fórmula na linha de entrada do Excel «=ПЛТ(B3/B5;B6*B5;-B2;B4)» e pressione “Enter” no teclado. As letras serão diferentes dependendo das células em que a tabela é colocada.
3. Verifique se o valor da contribuição é calculado automaticamente na última linha da tabela.

Preste atenção! Assim, para que o cliente acumule 4 rublos a uma taxa de 2000000% em 11 anos, ele precisa depositar 28188 rublos todos os meses. O menos no valor indica que o cliente incorre em perdas ao dar dinheiro ao banco.

Recursos do uso da função PMT no Excel

Em geral, esta fórmula é escrita da seguinte forma: =PMT(taxa; nper; ps; [bs]; [tipo]). A função tem as seguintes características:

1. No cálculo das contribuições mensais, considera-se apenas a taxa anual.
2. Ao especificar a taxa de juros, é importante recalcular com base no número de parcelas por ano.
3. Em vez do argumento “Nper” na fórmula, é indicado um número específico. Este é o período de pagamento.

Cálculo de pagamento

Em geral, o pagamento da anuidade é calculado em duas etapas. Para entender o tema, cada uma das etapas deve ser considerada separadamente. Isso será discutido mais adiante.

Etapa 1: cálculo da parcela mensal

Para calcular no Excel o valor que você precisa pagar mensalmente em um empréstimo com taxa fixa, você deve:

1. Compile a tabela de origem e selecione a célula na qual deseja exibir o resultado e clique no botão “Inserir função” na parte superior.

2. Na lista de funções, selecione “PLT” e clique em “OK”.

3. Na próxima janela, defina os argumentos para a função, indicando as linhas correspondentes na tabela compilada. Ao final de cada linha, você precisa clicar no ícone e, em seguida, selecionar a célula desejada na matriz.

4. Quando todos os argumentos estiverem preenchidos, a fórmula apropriada será escrita na linha de entrada de valores, e o resultado do cálculo com um sinal de menos aparecerá no campo da tabela “Pagamento mensal”.

Importante! Após o cálculo da parcela, será possível calcular o valor que o mutuário pagará a mais por todo o período do empréstimo.

Etapa 2: detalhes do pagamento

O valor do pagamento em excesso pode ser calculado mensalmente. Como resultado, uma pessoa entenderá quanto dinheiro gastará em um empréstimo todos os meses. O cálculo de detalhes é realizado da seguinte forma:

1. Crie uma planilha por 24 meses.

2. Coloque o cursor na primeira célula da tabela e insira a função “OSPLT”.

3. Preencha os argumentos da função da mesma maneira.

4. Ao preencher o campo “Período”, é necessário referir-se ao primeiro mês da tabela, indicando a célula 1.

5. Verifique se a primeira célula da coluna “Pagamento pelo corpo do empréstimo” está preenchida.
6. Para preencher todas as linhas da primeira coluna, você precisa esticar a célula até o final da tabela

7. Selecione a função “PRPLT” para preencher a segunda coluna da tabela.
8. Preencha todos os argumentos na janela aberta de acordo com a captura de tela abaixo.

9. Calcule o pagamento mensal total somando os valores nas duas colunas anteriores.

10. Para calcular o “Saldo a pagar”, você precisa somar a taxa de juros ao pagamento no corpo do empréstimo e esticá-la até o final da placa para preencher todos os meses do empréstimo.

Informação adicional! Ao calcular o restante, os cifrões devem ser pendurados na fórmula para que ela não se mova quando esticada.

Cálculo de pagamentos de anuidade em um empréstimo no Excel

A função PMT é responsável por calcular a anuidade no Excel. O princípio do cálculo em geral é realizar as seguintes etapas:

1. Compile a tabela de dados original.
2. Construa um cronograma de pagamento da dívida para cada mês.
3. Selecione a primeira célula na coluna “Pagamentos do empréstimo” e insira a fórmula de cálculo “PLT ($B3/12;$B$4;$B$2)”.
4. O valor resultante é esticado para todas as colunas da placa.

Cálculo em MS Excel reembolso do valor principal da dívida

Os pagamentos de anuidades devem ser feitos mensalmente em valores fixos. E a taxa de juros não muda.

Cálculo do saldo do valor principal (com BS=0, tipo=0)

Suponha que um empréstimo de 100000 rublos seja tomado por 10 anos a 9%. É necessário calcular o valor da dívida principal no 1º mês do 3º ano. Solução:

1. Compile uma folha de dados e calcule o pagamento mensal usando a fórmula PV acima.
2. Calcule a parcela do pagamento necessária para pagar parte da dívida usando a fórmula «=-PMT-(PS-PS1)*item=-PMT-(PS +PMT+PS*item)».
3. Calcule o valor da dívida principal por 120 períodos usando uma fórmula bem conhecida.
4. Usando o operador HPMT, encontre o valor dos juros pagos no 25º mês.
5. Confira resultado.

Calculando o valor do principal que foi pago entre dois períodos

Este cálculo é melhor feito de uma maneira simples. Você precisa usar as seguintes fórmulas para calcular o valor no intervalo para dois períodos:

• =«-BS(item; con_period; plt; [ps]; [tipo]) /(1+tipo *item)».
• = “+ BS(taxa; período_início-1; plt; [ps]; [tipo]) /IF(período_início =1; 1; 1+tipo *taxa)”.

Preste atenção! As letras entre parênteses são substituídas por valores específicos.

Reembolso antecipado com prazo reduzido ou pagamento

Se você precisar reduzir o prazo do empréstimo, terá que realizar cálculos adicionais usando o operador IF. Assim será possível controlar o saldo zero, que não deve ser alcançado antes do término do prazo de pagamento.

Para reduzir os pagamentos, você precisa recalcular a contribuição de cada mês anterior.

Calculadora de empréstimo com pagamentos irregulares

Existem várias opções de anuidade onde o mutuário pode depositar valores variáveis ​​em qualquer dia do mês. Em tal situação, o saldo da dívida e os juros são calculados para cada dia. Ao mesmo tempo, no Excel, você precisa:

1. Insira os dias do mês para os quais os pagamentos são feitos e indique seu número.
2. Verifique se há valores negativos e positivos. Os negativos são os preferidos.
3. Conte os dias entre duas datas em que o dinheiro foi depositado.

Cálculo do pagamento periódico em MS Excel. Depósito a prazo

No Excel, você pode calcular rapidamente o valor dos pagamentos regulares, desde que um valor fixo já tenha sido acumulado. Esta ação é executada usando a função PMT após a compilação da tabela inicial.

Conclusão

Assim, os pagamentos de anuidades são mais fáceis, rápidos e eficientes de calcular no Excel. O operador PMT é responsável pelo seu cálculo. Exemplos mais detalhados podem ser encontrados acima.