Saberexcel - o site de quem precisa aprender Microsoft Excel VBA
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 tudo sobre planilhas do Aplicativo Microsoft Excel VBA(Visual Basic Application), sozinho, com baixo custo, praticando com os produtos didáticos Saberexcel
Faça o download do exemplo planilha com a dica acima
Excel financiamento calculos formulas (146.15 kB)
Publicidade
Compre com segurança, garantia e ótimos preços
