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])
- Taxa Obrigatório. A taxa de juros para o empréstimo.
- Nper Obrigatório. O número total de pagamentos pelo empréstimo.
- Vp Obrigatório. O valor presente, ou a quantia total agora equivalente a uma série de pagamentos futuros; também conhecido como principal.
- Vf Opcional. O valor futuro, ou o saldo, que você deseja obter depois do último pagamento. Se vf for omitido, será considerado 0 (o valor futuro de determinado empréstimo, por exemplo, 0).
- Tipo Opcional. O número 0 (zero) ou 1 e indica o vencimento dos pagamentos.
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: