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:
Opa,
o que você achou dessa aula? Conta pra mim 👇
Excelente, e eu quero se possível!
Olá Ener! Acabei de enviar no seu e-mail. Obrigado pela participação. Abraços
Excelente aula. Conteúdo muito útil.
Obrigado José! Fico feliz com sua visita e seu comentário. Forte abraço!
Gostei muito. eu quero:)
Olá Rosinez! Que ótimo, enviarei no seu e-mail, dá uma olhadinha se não vai para o spam 😉 abraços
Planilha top demais. Gostaria de receber ela.
Fala Wesley!
Obrigado pelo feedback =)
Planilha enviada, bom treino!
Adorei a aula, tudo muito bem explicado e ao pormenor. Também quero a planilha! Obrigado.
Fala Tiago!
Planilha enviada, obrigado pelo feedback =)
abraços
Pode me enviar por favor?
Gratidão!!!
Sucessooo!!!
Fala Marcos!
Planilha enviada, abraços
Excelente aula!
Pode me enviar uma cópia da planilha?
Obrigado e sucesso!
Fala Neto! E-mail enviado =) abraços
Nossa que top, consegue enviar???
Fala Henrique! Planilha enviada =)
abraços
Planilha incrível, adoraria receber ela.
obrigado Gabriel! Planilha enviada, abraços =)
Gostei muito da planilha, tem como nos enviar por favor..
Fala Vanderson, planilha enviada =)
Abraçosss
Ótimo planilha, gostei demais! gentileza poderia me enviar
Fala Emerson, planilha enviada! Abraços
Bom dia, gostaria de receber a tabela também. Excelente explicação
Bom dia Alice, obrigado pelo feedback!
Planilha enviada, abraços
Pode me mandar essa planilha por favor
Planilha enviada! abraços
parabéns pela planilha tem como me enviar a planilha, fazendo favor?
Obrigado Dário pelo feedback!
Baixe a planilha através desse link:
https://academiaexcel.com/materiais/planilha-de-simulacao-de-financiamento/
Abraços =)