Aprender Excel

APRENDER EXCEL

DICAS E NOVIDADES SOBRE EXCEL

Cálculo de juros simples e composto no Excel

por: Redação em Dicas, no dia 01/08 | 14:11

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. 

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?

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 se vai calculando, ao fim de cada período, sobre o principal acrescido do juro do período anterior, ou novo montante.

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.

Entretanto, recomendo a leitura da primeira página do artigo, pois assim ficará mais fácil compreender o funcionamento dos Juros Compostos, no qual o capital do mês seguinte passa a ser o montante do mês anterior.

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 Microsoft Office Excel 2003 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, geralmente, de um empréstimo ou compra, sempre brinco com meus alunos dizendo que 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 2003 e crie a tabela abaixo:

Em seguida, clique na célula B4 e vá ao Menu Inserir / 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.