Detalhes de Excel financiamento calculos formulas

Dicas do Aplicativo Excel
Saberexcel – Informática
 

Como calcular juros no Excel

Download do exemplo no final da página

Vamos mostrar como criar uma aplicação para calcular e montar tabelas detalhadas para a amortização de empréstimos. Você, que vai construir o sisteminha, terá de encarar todas as fórmulas e truques necessários para fazê-lo funcionar. Mas o objetivo é esconder todas as complexidades, criando um resultado final que qualquer usuário possa utilizar. Na verdade, se você não está nem aí para fórmulas, a planilha esta disponível no site http://www.saberexcel.com para pessoas cadastradas no site.

Mãos à obra. Para começar, vamos definir o formato geral de nossa planilha. Ela deve conter três áreas. Primeiro, as células nas quais o usuário deve digitar os dados básicos do financiamento (valor, taxa de juros, prazo e data inicial). Depois, um painel-resumo, com o valor do pagamento mensal, número de pagamentos, total de juros e custo total do financiamento. O terceiro bloco apresenta uma tabela com a discriminação dos dados de cada pagamento previsto. Cada linha dessa tabela mostra a previsão de pagamento de um mês, evoluindo desde o valor financiado total até o saldo final zero. Na elaboração dessa planilha, tomamos como ponto de partida um modelo publicado no site da Microsoft, que foi redefinido para este tutorial. Para acompanhar adequadamente as tarefas, é aconselhável que você faça o download de nossa planilha no site de http://www.saberexcel.com

A principal técnica utilizada na elaboração do projeto é a definição de nomes para células, grupos de células e fórmulas. Assim, em vez de operar com endereços, trabalha-se com variáveis, o que facilita bastante a compreensão. É muito mais confortável, por exemplo, lidar com itens como Valor_Financiado e Taxa_Juros do que com E4, C7. Vamos, então, montar a área 1 da planilha, dedicada à entrada de dados. Em nosso exemplo, ela ocupa as células de E4 a E7. Vamos dar um nome a cada uma dessas células.

Coloque o cursor em E4 e acione o comando Inserir/Nome/Definir. Na caixa Definir Nome, digite, em cima, Valor_Financiado (o nome não pode ter espaços). Embaixo, o programa já inclui, automaticamente, o endereço de E4. Acione OK. Repita a operação para as células E5 a E7, nomeando-as, respectivamente, como Taxa_Juros, Prazo_Meses e Data_Inicio. Faça as adequadas formatações nas quatro células, ajustando cada uma conforme o conteúdo esperado: moeda, decimal, inteiro e data. Para orientar o usuário da calculadora de financiamentos, escreva, à esquerda de cada célula, as indicações dos dados a serem digitados.

Passemos à segunda área. Na mesma coluna, no intervalo E11:E14, vamos nomear quatro células: Pagamento_Mensal, Num_Pagamentos, Total_Juros e Custo_Total. A cada um desses nomes deve corresponder uma fórmula:

=-PGTO(Taxa_Juros;Num_Pagamentos;Valor_Financiado)

Acima, a fórmula para Pagamento_Mensal. Ela usa a função PGTO e baseia-se em outras variáveis nomeadas. Observe o sinal de menos: ele indica que cada pagamento será subtraído do valor financiado. A fórmula para Num_Pagamentos é a seguinte:

=SE(Tudo_Preenchido;Prazo_Meses;"")

A rigor, o número de pagamentos é igual ao valor digitado na célula Prazo_Meses. No entanto, ele só deve aparecer quando o usuário tiver preenchido as células da área 1 para o

Saberexcel – Curso Introdução a Informática Básica – Dicas do Excel 6

cálculo de novo financiamento. Por extensão, as células da área 2 e da área 3 só devem exibir alguma informação se as quatro células da área 1 estiverem preenchidas. Para garantir isso, vamos criar uma fórmula nomeada (sempre em Inserir/Nome/Definir), Tudo_Preenchido, que funciona como um teste lógico, do tipo sim/não:

=SE(Valor_Financiado*Taxa_Juros

*Prazo_Meses*Data_Inicio>0;1;0)

Aqui, o truque é o seguinte: multiplicam-se os quatro valores das células na área 1. Se todos estiverem preenchidos, o resultado será um número positivo; se pelo menos um estiver em branco, será zero. Assim, se a fórmula Tudo_Preenchido responde sim (valor 1), os cálculos devem ser executados para preencher as áreas 2 e 3. Se for não (valor 0), aquelas regiões são mostradas em branco.

As fórmulas para as células Total_Juros (E13) e Custo_Total (E14) são, respectivamente:

=-IPGTO(Taxa_Juros;Pagamento_Num;Num_Pagamentos;Valor_Financiado)

=SE(Tudo_Preenchido;ARRED(Pagamento_Mensal;2)*Num_Pagamentos;"")

Em Total_Juros, usa-se a função IPGTO, do Excel, que fornece os juros acumulados de um investimento. Também nesse caso, como se trata de débitos, o sinal é negativo. Na fórmula do Custo_Total, preste atenção para a função ARRED (arredondamento), aplicada apenas ao valor do pagamento mensal. Ela garante que a multiplicação dos valores mostrados para Pagamento_Mensal e Num_Pagamentos produz, exatamente, o número mostrado em Custo_Total. Sem essa função, apareceria um número aproximado.

A área 3 da planilha, que apresenta a discriminação de cada pagamento, é encimada por um cabeçalho com sete títulos de colunas: Número; Data do Pagamento; Balanço Inicial; Pagamento; Principal; Juros; e Balanço Final. Selecione essas sete células e nomeie-as como Linha_Cabeçalho, definida pela seguinte fórmula:

=LIN(´Tabela de Amortização´! $17:$17)

Ainda não havíamos dito que nossa folha de cálculo foi batizada como Tabela de Amortização. Nessa tabela, o cabeçalho corresponde à linha 17. Como a área 3 pode se estender por mais de uma página impressa, acione Arquivo/Configurar Página/orelha Planilha e, na caixa Linhas a Repetir na Parte Superior, digite Linha_Cabeçalho. Avancemos, agora, para o miolo da área 3. Vamos construir a primeira linha, logo abaixo do cabeçalho. Naturalmente, as sete células dessa linha têm fórmulas específicas. A célula Número apresenta apenas o valor Pagamento_Num, ou seja, a seqüência 1, 2, 3 etc. Sua fórmula é:

=SE(Nao_Pago*Tudo_Preenchido; Pagamento_Num;"""")

Aqui, entra em cena novo teste lógico, que é a variável Nao_ Pago, também definida por uma fórmula nomeada:

=SE(Pagamento_Num<=Num_Pagamentos;1;0)

Saberexcel – Curso Introdução a Informática Básica

Esse teste indica se a linha deve ou não ser preenchida. A resposta será sim enquanto o número do pagamento for menor ou igual ao total de pagamentos. A célula Data do Pagamento deve exibir a data inicial do financiamento acrescida de um mês:

=SE(Nao_Pago*Tudo_Preenchido; Data_Pagamento;"")

Ela se baseia na fórmula nomeada Data_Pagamento:

=DATA(ANO(Data_Inicio);MÊS(Data_Inicio)+Pagamento_ Num;DIA (Data_Inicio))

A célula Balanço Inicial também depende de outra fórmula nomeada, Balanço_Inicial, que, por sua vez, aplica a fórmula do valor futuro:

=-VF(Taxa_Juros;Pagamento_ Num-1;-Pagamento_Mensal;Valor _Financiado)

Balanço Final, a última célula, tem base idêntica:

=-VF(Taxa_Juros;Pagamento_Num;-Pagamento_Mensal;Valor_Financiado)

O valor a ser exibido na coluna Pagamento é sempre o mesmo e corresponde a Pagamento_Mensal. Como em todas as células da área 3, ele só deve ser escrito se os dados para o cálculo foram preenchidos e se a linha se refere a uma prestação não paga:

=SE(Nao_Pago*Tudo_Preenchido; Pagamento_Mensal;"")

Por fim, vêm as células para os valores Principal e Juros, que discriminam, no valor da prestação, quanto equivale à restituição do capital e quanto corresponde a juros. Principal e Juros são, também, duas fórmulas nomeadas. A primeira baseia-se na fórmula PPGTO, do Excel:

=-PPGTO(Taxa_Juros;Pagamento_Num;Num_Pagamentos;Valor _Financiado)

Juros, por sua vez, usa a fórmula interna IPGTO:

=-IPGTO(Taxa_Juros;Pagamento_ Num;Num_Pagamentos;Valor_Financiado)

Todas as células da primeira linha ativa da área 3 devem ser copiadas para as linhas seguintes. Na planilha-exemplo, essas cópias foram estendidas até a linha 377, que corresponde à prestação número 360 - ou seja, um financiamento de 30 anos, o prazo máximo aceito pela aplicação.

A essa altura, sua calculadora de financiamentos já está completa. Faltam apenas detalhes de acabamento. Para concluir o trabalho, selecione as células ativas da área 1 e dê o comando Formatar/Células. Na orelha Proteção, desligue a caixa Travada. Agora, acione Ferramentas/Proteger/Proteger Planilha (a definição de senha é opcional). Você acaba de criar uma espécie de formulário. As células para entrada de dados estão livres e todas as outras, protegidas. Assim, qualquer usuário poderá usar a planilha sem o risco de modificá-la.
Essa aplicação foi testada nas versões anteriores como 2003 funciona também nas versoes atuais

Aprenda Microsoft Excel VBA - Saberexcel - o site das macros

Download do exemplo planilha com a dica acima