[Excel Financeiro] - Planilha de simulação de financiamento

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: