Cálculo de juros simples e composto no Excel

Este artigo inicialmente era composto de 4 partes, fizemos uma junção dos 4 artigos em um único, assim o conteúdo se manteve o mesmo, porém você terá apenas um local para pesquisar sobre Juros compostos e simples.

Precisando de uma planilha que faça o cálculo de juros AUTOMATICAMENTE para você, com número de parcelas, projeção dos juros, valor do financiamento, valor real, etc??? Então clique aqui e baixe-a gratuitamente ;)

Neste artigo vamos demonstrar o que é Juro, como funciona o cálculo, como são feitas as fórmulas dentro do excel, tudo exemplificado e ilustrado para que você tenha uma ótima compreensão do conteúdo e possa sempre usar este artigo como guia para o cálculo de juros simples e compostos usando o Excel.

O que é Juro?

Primeiro precisamos ter as ideias bem claras em nossa cabeça do que estamos falando:

Juro é o preço pago pelo uso de fundos tomados por empréstimo: pode-se referir também a um valor adicional, incidente sobre as parcelas de pagamento ou sobre o montante total; expressado geralmente por uma percentagem anual, semestral, trimestral ou mensal, seu cálculo considera três variáveis: o valor do capital inicial (ou do bem adquirido), o prazo para que seja saldado e a taxa de mercado; quando calculado sobre o principal, denomina-se juro simples, enquanto que o juro composto é o que vai sendo acrescido ao final de cada período, somando os juros do período anterior, ou novo montante. O juros composto é a famosa (e temida) "bola de neve".

Juros Simples

A fórmula para o cálculo de Juros Simples é a seguinte:

Juros = C * i * n

Onde:

C: corresponde ao capital, valor que você vai pegar emprestado.
I: corresponde à taxa de juros, que pode ser a.a (ao ano) ou a.m (ao mês). Antes de iniciar o cálculo você deve dividir a taxa de juros por 100, a fim de que obtenha o valor da taxa no sistema decimal.
N: é o prazo, período ou se você quiser dizer de outra forma: as suaves prestações das Casas Bahia. Note que o prazo deve estar de acordo com a taxa, ou seja, se ela estiver em ano, o prazo também deverá ser expresso em ano, agora se a taxa estiver em mês o prazo também deve ser convertido para mês.

Supomos que a taxa seja de 1,65 a.a e o prazo seja de 36 meses:
Logo, basta que você divida o prazo pelo período de 1 ano, nesse caso teríamos: = 36 / 12: resultado 3 anos

Supomos que a taxa seja de 1,65 a.m e o prazo seja de 3 anos:
Logo, basta que você multiplique o prazo pelo período de 1 ano, nesse caso teríamos: = 3 * 12: resultado 36 meses

Observe os dois exemplos abaixo: no primeiro exemplo o cálculo está sendo realizado fora das células em que se encontram as variáveis citadas, a fim de que seja possível realizar um novo cálculo a qualquer momento. Desse modo, faz-se necessário proteger as células que modificam a célula.

Exemplo 1:

Exemplo 2:

É por isso que temos de planejar bem antes de sair implementando uma planillha, já que a manutenção do cálculo deve fazer parte do seu projeto.

Parte 2

Nesta segunda parte do artigo, veremos como realizar o cálculo de Juros Simples e Compostos com o auxílio da Função =PGTO(taxa; nper; vp; vf; tipo), que retorna o pagamento periódico de uma anuidade de acordo com pagamentos constantes e com uma taxa de juros constante.

Onde:

Taxa é a taxa de juros por período.
Nper é o número total de pagamentos pelo empréstimo.
Vp é o valor presente - o valor total presente de uma série de pagamentos futuros.
Vf é o valor futuro, ou o saldo, que você deseja obter depois do último pagamento. Se vf for omitido, será considerado 0.
Tipo é o número 0 ou 1 e indica as datas de vencimento.

Primeiramente, crie uma planilha com os dados do Capital, Prazo (em meses), Taxa de Juros (em porcentagem), Prestação Mensal e Valor Total, conforme a figura abaixo:

Onde:

Valor Financiado (VP): corresponde ao valor presente da fórmula, ou seja, o capital, valor financiado ou empréstimo.
Prazo (Nper): corresponde ao Nper da fórmula, ou seja, o número total de pagamentos, o período, as suaves prestações.
Taxa de Juros (Taxa): corresponde à taxa de juros aplicada ao período.
Valor Mensal: corresponde ao valor mensal a ser pago, já acrescido de juros.
Valor Total: corresponde ao valor total a ser pago, já crescido de juros.

Clique na célula D2 e logo após vá em no Menu Inserir / Função. Na caixa de diálogo "Inserir Função" escolha a categoria "Financeira", e logo depois, clique na função =PGTO(), conforme na figura abaixo:

Na janela Argumentos da Função, defina a célula C2 para a taxa, B2 para Nper, e A2 para Valor Presente, conforme a figura abaixo:

O resultado deve ser idêntico à figura abaixo:

Note que o Excel traz o resultado em negativo, para resolver esse problema, tecle F2 e coloque um sinal de menos (-) na frente da fórmula PGTO, que deverá ficar dessa forma: -PGTO(C2;B2;A2), conforme a figura abaixo:

O resultado deverá ser idêntico à figura abaixo:

Só para ter clareza do que estamos fazendo, que tal tirar a prova real do cálculo, conforme figura abaixo:

O nosso exemplo está pronto, basta multiplicar o VALOR MENSAL pelo NÚMERO DE PARCELAS (=D2*E2) para obter o VALOR TOTAL. Após obter o resultado, utilize a alça de preenchimento do Excel para copiar a fórmula para as células abaixo.

Parte 3

Na terceira parte do artigo, vamos criar uma planilha para o Cálculo de Juros Compostos sem a necessidade do uso de uma função específica, a fim de que possamos exercitar nossa a criatividade e capacidade para resolver problemas.

No regime de juros compostos, os juros de cada período são somados ao capital para o cálculo de novos juros nos períodos seguintes. Nesse caso, o valor da dívida é sempre corrigida e a taxa de juros é calculada sobre esse valor. O atual sistema financeiro utiliza o regime de juros compostos, pois ele oferece uma maior rentabilidade se comparado ao regime de juros simples, onde o valor dos rendimentos se torna fixo, e no caso do composto o juro incide mês a mês de acordo com o somatório acumulativo do capital com o rendimento mensal, isto é, prática do juro sobre juro. As modalidades de investimentos e financiamentos são calculadas de acordo com esse modelo de investimento, pois ele oferece um maior rendimento, originando mais lucro.

Fórmula de juros composto:

Montante = Capital * (1 + taxa)^prazo

Onde

Montante é igual ao capital * (1 + a taxa de juros) elevado na potência do número de meses do prazo de pagamento.

Abra o Excel e crie a planilha com o valor do Capital na célula B1, a taxa de juros em B2 e os meses devidamente distribuídos, conforme a figura abaixo:

Logo em seguida, clique na célula B4 e coloque a fórmula: =B1*(1+(B2/100))^A4, conforme a figura abaixo:

Explicando: na fórmula acima, o capital é multiplicado pelo valor de (1+ taxa dividida por cem) elevado ao prazo (do primeiro mês), conforme código abaixo:

Montante = B1*(1 + (B2/100))^A4
Montante = Capital * (1 + (taxa/100))^prazo

Logo após, clique na célula B5 para calcular o juro embutido no segundo mês, e coloque a seguinte fórmula: =B4*(1+($B$2/100))^A5, conforme a figura abaixo:

Explicando: para calcular o juro embutido nos próximos meses, temos que substituir o capital pelo montante (resultado) do mês anterior e multiplicá-lo pelo valor de (1+ taxa dividida por cem) elevado ao prazo (do segundo mês), e assim por diante, conforme a figura abaixo:

= B4*(1 + ($B2/100))^A5
= Montante do mês anterior * (1 + (taxa/100))^prazo

Em seguida, vamos calcular a projeção dos juros em moeda e em porcentagem, assim, ficará mais fácil saber quanto estamos pagando a mais (mensalmente), tanto em moeda quanto em porcentagem. Clique na célula C4 e digite a seguinte fórmula =B4-$B$1, conforme a figura abaixo:

Explicando: para saber a projeção de juros em Moeda, basta subtrair o capital pelo montante do mês desejado. Para compor todos os resultados, copie a fórmula (arrastando) para as células seguintes, conforme a figura abaixo:

Agora, clique na célula D4 e digite a seguinte fórmula =B4-$B$1, conforme a figura abaixo:

Explicando: para saber a projeção de juros em Porcentagem, primeiramente temos que dividir o correspondente ao primeiro mês pelo valor do capital. Note que o resultado é idêntico à taxa de juros definida. Finalmente, vamos calcular a projeção desse percentual mês a mês, ou seja, quantos de juros vai sendo acrescentado em relação ao juros embutido no primeiro mês. Para tanto, clique na célula D5 e digite a seguinte fórmula: =(C5/$B$1)-D4.

Explicando: para saber realmente quantos juros serão cobrados nos meses posteriores, dividimos o valor do juro correspondente ao segundo mês pelo capital (parte dividida pelo todo) e subtraímos o resultado dessa divisão pelo percentual de juros do mês anterior, a fim de que apareça, conforme a figura abaixo:

Dessa forma, obtemos somente a diferença do percentual de juros, conforme a figura abaixo:

Arraste a fórmula para baixo a fim de que você obtenha o resultado final:

É isso aí, com essa planilha poderíamos calcular os juros embutidos em diversos meses. Faça um teste arrastando a última linha de resultados, conforme a figura acima.

Parte 4

Estamos aqui na última parte do artigo da série que se propôs a ensinar como trabalhar com Juros Simples e Compostos no Excel. Dessa vez, falaremos um pouco sobre Valor Presente e Valor Futuro, presentes no cálculo de Juros Compostos.

Montante = Capital * (1 + taxa) ^ prazo

Onde:

Valor Futuro = Valor Presente * (1 + taxa) ^ prazo

Chamamos de VP (present value) o valor presente, que representa o valor que eu tenho na data 0 (zero) ou 1 (um). Se esse parâmetro for omitido em sua fórmula, o Excel irá considerar o zero para efetuar o cálculo. E chamamos de VF (Valor Futuro ou future value), o valor que será obtido ao final do período, considerando as entradas, saídas e os juros. Também conhecemos VF como montante, na medida em que ele retorna o valor futuro de um investimento com base nas aplicações constantes reguladas pela taxa de juros.

Quando se trata de um empréstimo ou compra o montante é aquilo que vai doer no seu bolso, que vai cheirar mal à beça. Em outras palavras, o valor futuro corresponde à rentabilidade do capital que você investiu em um determinado produto, serviço, etc.

Dadas às explicações, vamos á pratica:

Problema: Vamos aplicar 100 reais todo mês em uma poupança, com uma taxa de 2% ao mês, para saber quanto vamos obter após um determinado período.

Logo, a fórmula será a seguinte:

VF( taxa;número_de_períodos,pagamento;valor_presente;tipo)

Abra o Microsoft Excel e crie a tabela abaixo:

Em seguida, clique na célula B4 e vá ao Menu" Inserir" e depois em "Função". Clique na categoria "Financeira" e escolha a fórmula "VF", conforme a figura abaixo:

Na Caixa de Diálogo Argumentos da função, configure:

Taxa: digite $B$2 - 0,02
Nper: clique em A4 - que corresponde ao primeiro período / mês
Pgto: digite $B$1 - que corresponde ao valor aplicado regularmente
VP: não informaremos, pois os valores serão aplicados regularmente. Se desejar, digite zero (0) apenas para não deixar esse campo em branco.
Tipo: digite 1 para informar que os pagamentos ocorrerão ao final de cada período.

Caso tenha alguma dúvida, observe a figura abaixo:

Você verá que o valor aparecerá em vermelho, sendo necessário colocar um sinal de menos (-) antes de VF para corrigir esse problema: =-VF($B$2;A4;$B$1;;1), conforme a figura abaixo:

Após ter feito isso, você obterá o seguinte resultado:

Arraste o resultado para baixo a fim de obter o resultado dos próximos períodos / meses, conforme a figura abaixo:

Para saber a projeção do acúmulo de juros em moeda, clique na célula C4 e digite a fórmula =B4-($B$1*A4).

Explicando: o valor futuro obtido no mês está sendo subtraído do pagamento multiplicado pelo período atual, a fim de que saibamos qual é a projeção real daquele determinado período, conforme a figura abaixo:

Enfim, para sabermos a projeção do acúmulo de juros em porcentagem basta digitar a fórmula =C4/B4 na célula D4 e, logo em seguida, arrastar a fórmula para baixo para obter o seguinte resultado:

Explicando: apenas dividimos a parte (juros acumulado) pelo todo (valor futuro) como em qualquer cálculo simples de porcentagem. Prova real: a confirmação do resultado pode ser obtida, por exemplo, dessa maneira:

Valor Presente: Também podemos obter somente o Valor Presente isolando PV na fórmula mostrada no início do artigo, fazendo desta maneira:

Valor Presente = Valor Futuro / (1 + taxa) ^ prazo

Notem que o Valor Presente é determinado pela divisão (valor futuro / taxa elevada ao prazo). No exemplo abaixo, obtemos o valor presente do último período / mês, conforme a figura abaixo:

Também é possível obter o valor presente com o auxílio da Função PV, que retorna o valor presente de um investimento. Para tanto, clique em "Inserir", "Função", aponte para a categoria "Financeira" e escolha "VP", configurando-a conforme a figura abaixo:

Onde:

Taxa: digite $B$2 - 0,02
Nper: clique em A15 - que corresponde ao último período / mês
Pgto: digite $B$1 - que corresponde ao valor aplicado regularmente
VF: não informaremos. Se desejar, digite zero (0) apenas para não deixar esse campo em branco.
Tipo: digite 1 para informar que os pagamentos ocorrerão ao final de cada período.

A fórmula =VP(B2;A15;B1;;1) retornará o resultado em negativo, para corrigir o problema basta colocar um sinal de menos (-) antes de VP, ficando desta maneira: =-VP(B2;A15;B1;;1).

Pronto, terminamos a nossa série de quatro artigos que abordou o cálculo de Juros Simples e Compostos no Excel.

Aproveitem esta dica pessoal, essa é uma das funções mais uteis do Excel e pode lhe poupar muita dor de cabeça.