Fala acadêmicos! Bora treinar?

Hoje iremos aprender como criar uma planilha de financiamento, do absoluto zero. Essa planilha calcula qualquer financiamento que deseja simular, basta digitar o valor, prestações e juros que saberá detalhadamente cada item. Esse tutorial ficou show, bora aprender *-*

 

 

CLIQUE AQUI PARA ADQUIRIR A PLANILHA

Sofrendo pra melhorar sua planilha buscando por vídeos soltos na internet? Que tal conhecer meu treinamento Desfio Excel 7D que te leva do zero ao Dashboard em 7 dias de estudos? Clique aqui para saber mais.

 

Nossa planilha possui os campos: Valor, prestações, juros, juros pagos.

 

Abaixo os campos: nº, amortização, juros, prestação e saldo.

Nosso objetivo é digitar o valor financiado, as prestações a serem pagas e a taxa de juros.

Preenchendo essas 3 informações, queremos que o Excel retorne abaixo todas as parcelas e valores a pagar.

Passo 1: Preencha a simulação dos valores, prestações e juros.

Passo 2: Nos campos n, amortização, juros e prestação digite o número 0.

Em saldo referencie a célula B5, onde está o valor de 1.000,00

Passo 3: Criar uma fórmula automática para os números.

Para que não seja necessária digitar manualmente os números referente as prestações, usaremos a função SE

Fórmula: =SE(B8<D5;B8+1;””)

Arrastando a fórmula para baixo, não funcionará, pois a célula D5 se move, é necessário travar essa célula.

Para não ter que usar o “travamento, iremos trabalhar com intervalos nomeados.

Passo 4: Nomeando os intervalos.

Clique na célula B5 onde está digitado o valor 1.000,00. Apague essa referência e digite a palavra VALOR.

Repita o processo para os demais: prestações, juros.

Passo 5: Substitua a célula D5 pelo intervalo nomeado prestações.

Ao começar a digitar, o intervalo PRESTACOES ficará visível, dê dois clique na palavra. Confirme pressionando a tecla enter.

Passo 6: Arraste a fórmula até a linha 124, será o limite estabelecido para 120 parcelas.

Passo 7: Calcular os juros pagos.

Faremos isso usando a função PGTO do Excel.

Função PGTO:

PGTO, uma das funções financeiras, calcula o pagamento de um empréstimo de acordo com pagamentos constantes e com uma taxa de juros constante.

Sintaxe: PGTO(taxa, nper, va, [vf], [tipo])

Fórmula: =PGTO(JUROS;PRESTACOES;-VALOR;;0)

A taxa é nosso juros pré-estabelecido. Nper: corresponde as prestações. VP: é o valor, 1.000,00 onde colocamos o sinal de menos a frente, pois é uma dívida. VF: por não ser argumento obrigatório e não sabermos ainda o valor, colocaremos o ponto e vírgula “;” na sintaxe. Tipo: 0 indica que o pagamento será feito no fim do período.

Passo 8: Calculando os juros

Digite o sinal de igualdade e multiplique o valor contido em F8, o seu saldo atual, 1.000,00 pelos juros (8,00%)

Fórmula: =F8*JUROS

Passo 9: Calculando a amortização

Amortização é o valor que paguei retirando os juros

Fórmula: =E9-D9

Passo 10: Calculando o saldo

O saldo é o valor que pegou “emprestado” subtraído pelo valor amortizado.

Fórmula: =F8-C9

Arraste a fórmula para baixo, selecione as quatro células, posicione o mouse até que ele fique essa cruz preta e dê um duplo clique.

Ao chegar na parcela 24, os outros valores não ficaram corretos.

Passo 11: Incluindo a função SE para corrigir

Se o número de parcelas for igual a vazio, retornar vazio, caso contrário realizar a fórmula anterior normalmente.

Fórmula: =SE(B9=””;””;E9-D9)

Faremos o mesmo para os cálculos de juros, prestação e saldo.

Para facilitar, vamos copiar a fórmula e colar antes das outras já feitas.

a) Selecione a fórmula na barra de fórmulas e pressione as teclas: Ctrl+C para copiar.

b) Clique na fórmula do juros e pressione as teclas: Ctrl+V para colar.

Lembre-se de fechar os parênteses no final.

c) Clique na fórmula da prestação e pressione as teclas: Ctrl+V para colar.

d) Clique na fórmula do Saldo e pressione as teclas: Ctrl+V para colar.

Selecione as 5 células e arraste até o final.

Agora sim o cálculo será realizado baseado na quantidade de prestações inseridas.

Para finalizar, vamos formatar para que preencha com cor as células em que se realizarem os cálculos.

Passo 12: Aplicando formatação condicional com fórmulas:

a) Selecione o intervalo de dados

b) Pressione as teclas de atalho: Ctrl+Shift+Seta para baixo

Esse atalho selecionará todo o intervalo.

d) Na página inicial, clique em formatação condicional, nova regra.

e) Escolha formatar apenas células que contenham

Clique na setinha e selecione a opção: Não vazias.

f) Após clique em formatar.

Na guia preenchimento, escolha a cor desejada. Vamos pegar o cinza bem clarinho, a primeira opção.

Essa planilha pode ser usada como calculadora inteligente.

Por exemplo, para calcular o financiamento de carro.

Simulamos o valor de 25.000,00 em 48 prestações com juros de 1,50%

Passo 13: Calculando os juros pagos

Somaremos todos os juros calculados.

a) Digite a fórmula =soma(

b) Clique na célula D9

c) Pressione as teclas: Ctrl+shift+seta para baixo (para marcar todo o intervalo)

d) Feche os parênteses e pressione a tecla enter.

Resultado final:

E aí, o que achou desse treino super avançado hoje? Comente abaixo:

28 respostas

Deixe uma resposta

Esse site utiliza o Akismet para reduzir spam. Aprenda como seus dados de comentários são processados.