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

Este site usa cookies e tecnologias afins que nos ajudam a oferecer uma melhor experiência. Ao clicar no botão "Aceitar" ou continuar sua navegação você concorda com o uso de cookies.

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

Treinos Avançados

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

Academia Excel
Escrito por Academia Excel em outubro 6, 2020
15 min de leitura
Junte-se a mais de X pessoas

Entre para nossa lista e receba conteúdos exclusivos e com prioridade

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 👇

Deixe uma resposta

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

28 Replies to “[Excel Financeiro] – Planilha de simulação de financiamento”

Ener

Excelente, e eu quero se possível!

Academia Excel

Olá Ener! Acabei de enviar no seu e-mail. Obrigado pela participação. Abraços

José Roberto de Oliveira

Excelente aula. Conteúdo muito útil.

Academia Excel

Obrigado José! Fico feliz com sua visita e seu comentário. Forte abraço!

Rosinez de Souza Camargo

Gostei muito. eu quero:)

Academia Excel

Olá Rosinez! Que ótimo, enviarei no seu e-mail, dá uma olhadinha se não vai para o spam 😉 abraços

Wesley

Planilha top demais. Gostaria de receber ela.

Academia Excel

Fala Wesley!
Obrigado pelo feedback =)
Planilha enviada, bom treino!

tiagomarques18

Adorei a aula, tudo muito bem explicado e ao pormenor. Também quero a planilha! Obrigado.

Academia Excel

Fala Tiago!

Planilha enviada, obrigado pelo feedback =)
abraços

marcos furtado

Pode me enviar por favor?

Gratidão!!!

Sucessooo!!!

Academia Excel

Fala Marcos!
Planilha enviada, abraços

Neto Bremar

Excelente aula!
Pode me enviar uma cópia da planilha?

Obrigado e sucesso!

Academia Excel

Fala Neto! E-mail enviado =) abraços

Henrique Santiago

Nossa que top, consegue enviar???

Academia Excel

Fala Henrique! Planilha enviada =)
abraços

Gabriel

Planilha incrível, adoraria receber ela.

Academia Excel

obrigado Gabriel! Planilha enviada, abraços =)

Vandeson Soares Lobo

Gostei muito da planilha, tem como nos enviar por favor..

Academia Excel

Fala Vanderson, planilha enviada =)
Abraçosss

Emerson Souza

Ótimo planilha, gostei demais! gentileza poderia me enviar

Academia Excel

Fala Emerson, planilha enviada! Abraços

Alice Pena Farias

Bom dia, gostaria de receber a tabela também. Excelente explicação

Academia Excel

Bom dia Alice, obrigado pelo feedback!
Planilha enviada, abraços

pigavjt

Pode me mandar essa planilha por favor

Academia Excel

Planilha enviada! abraços

Dario do Nascimento

parabéns pela planilha tem como me enviar a planilha, fazendo favor?

Academia Excel

Obrigado Dário pelo feedback!
Baixe a planilha através desse link:
https://academiaexcel.com/materiais/planilha-de-simulacao-de-financiamento/
Abraços =)

×
Escolha um atendente disponível 👇
×