Aprenda a criar uma planilha de investimentos no Excel do zero! Isso mesmo, veja como criar uma planilha para controlar os seus investimentos, passo a passo.
Caso prefira esse conteúdo no formato de vídeo-aula, assista ao vídeo abaixo ou acesse o nosso canal do YouTube!
Na aula de hoje, eu quero te ensinar como construir uma Planilha de Investimentos no Excel do zero! Isso mesmo, vamos criar juntos uma planilha para que você possa controlar seus investimentos, sejam eles quais forem.
Com a tabela pronta e as informações preenchidas, podemos criar um painel de resumo com todos os cálculos e gráficos para que você consiga fazer uma análise rápida e eficiente dos seus investimentos.
Além disso, essa é uma ótima maneira de você exercitar conceitos como formatação de tabela, validação de dados, fórmula SE, fórmula SOMASES e muito mais!
Então, vem comigo aprender o passo a passo para construir uma planilha de investimentos completa no Excel, com impostos, taxas e todos os cálculos feitos corretamente!
Para começar nossa planilha de investimentos, vamos abrir um arquivo em branco no Excel e definir as principais informações que essa tabela precisa ter, como data da compra, ativo, preço, rendimentos e impostos.
Dentro da Categoria, teremos as opções de renda Fixa ou Variável. E em Tipo, teremos os tipos de ativos conforme a categoria deles.
Então, vamos selecionar a célula logo abaixo de Categoria e criar uma lista suspensa com validação de dados para podermos escolher entre fixa ou variável.
Para isso, na guia Dados, vamos clicar em Validação de Dados.
Na janela que será aberta, vamos escolher o tipo Lista e definir a fonte como Renda Fixa ou Renda Variável.
Vamos fazer o mesmo procedimento para a coluna Tipo, mas para ela vamos atribuir os valores: Poupança, CDB, Tesouro Direto, Ações e FII (Fundo de Investimento Imobiliário).
Para o Status, também criaremos uma validação de dados com os valores Em carteira ou Vendida.
Com isso, temos a base da nossa planilha de investimentos criada. Agora podemos começar a preenchê-la e a definir os cálculos e fórmulas que serão utilizados em algumas colunas.
A coluna Preço de Venda só será preenchida quando o Status do ativo for Vendida. Enquanto estiver como Em carteira, essa coluna ficará em branco.
A partir das colunas Preço de Compra e Preço de Venda, podemos definir a lógica e o cálculo da coluna Lucro Unitário antes IT (impostos e taxas).
Para calcularmos o lucro unitário, é preciso primeiro que o ativo tenha sido vendido, ou seja, que exista um valor preenchido na coluna Preço de Venda. Para realizar essa verificação, utilizaremos a fórmula SE.
Dentro da função SE,vamos verificar se o preço de venda é diferente (<>) de 0. Se a condição for verdadeira, então o lucro será o preço de venda menos o preço de compra. Caso contrário, o lucro unitário ficará em branco.
=SE(H2<>0;H2-F2;"")
Caso prefira, você pode definir, ao invés de deixar em branco, uma mensagem como “Em carteira” para a coluna do Lucro Unitário nos casos em que a venda ainda não foi feita.
Agora podemos selecionar toda as nossas células e, na guia Inserir, selecionar a opção Tabela.
A formatação como tabela, além de deixar sua planilha visualmente mais atrativa, também traz algumas facilidades, permitindo replicar os cálculos e configuração ao adicionarmos novas linhas.
Perceba que ao adicionar uma nova linha na tabela, todos os cálculos e validações de dados aplicados na primeira linha são replicados para a tabela toda.
Além dos preços e compras, uma parte essencial quando estamos construindo uma planilha de investimentos é calcular os Impostos e Taxas aplicados, como o Imposto de Renda e taxas administrativas que podem ser cobradas.
Eu optei por deixar uma única coluna para essas informações, mas caso você sinta necessidade pode estar dividindo em mais colunas.
O ponto importante aqui é que, com a coluna de Impostos e Taxas definida, podemos calcular a coluna de Lucro da Operação.
Para evitar erros na planilha, vamos utilizar novamente a fórmula SE para verificar se o valor de venda é diferente de 0. Garantindo assim que o Lucro da Operação só será calculado quando houver uma venda feita.
Se a condição for verdadeira, o lucro da operação será obtido multiplicando o lucro unitário pela quantidade e, em seguida, subtraindo os impostos e taxas. Caso contrário, ficará em branco.
=SE([@[Preço de Venda]]<>0;[@[Lucro Unitário antes IT]]*[@Quantidade]-[@[Impostos e Taxas]];"")
Com isso, temos toda a estrutura inicial da nossa planilha de investimentos no Excel pronta!
Podemos renomeá-la para Registro de Operações.
Para termos mais informações para continuar trabalhando e utilizando de exemplo, vou preencher mais algumas linhas de exemplo dentro dela.
Com nossa planilha de Registro de Operações pronta, podemos criar uma nova planilha chamada Painel, dentro do mesmo arquivo. Nesta planilha, vamos primeiro calcular o Total em Carteira, Lucro Total e o Total de Taxas e Impostos.
Porém, antes de realizarmos esses cálculos, precisaremos fazer alguns ajustes na nossa planilha de Registro de Operações.
Isso porque, para calcularmos o Total em Carteira, não podemos considerar apenas o preço do momento em que compramos o ativo, mas sim o preço atual desse ativo, e não temos essa informação no nosso Registro de Operações.
O que vamos fazer é, alterar a coluna Preço da Venda para Preço Atual e, além disso, redefinir a lógica aplicada nas colunas de lucro unitário e lucro da operação.
As lógicas das colunas Lucro Unitário e Lucro da Operação não serão mais baseadas em verificar se o Preço de Venda é diferente de 0, mas sim se o Status é igual a “Vendida”.
Nova fórmula coluna Lucro Unitário antes IT:
=SE([@Status]="Vendida";H2-F2;"")Nova fórmula coluna Lucro Operação após IT:
=SE([@Status]="Vendida";[@[Lucro Unitário antes IT]]*[@Quantidade]-[@[Impostos e Taxas]];"")Com isso, podemos definir os valores atuais dos ativos no mercado e criar uma nova coluna calculando o valor total atual (Valor Atual). O valor atual será calculado multiplicando o preço atual pela quantidade.
=[@[Preço Atual]]*[@Quantidade]
Com a planilha de registros ajustada, podemos voltar à planilha Painel e calcular, na seção Total em Carteira, o valor atualmente presente na carteira de investimentos.
Para isso, utilizaremos a função SOMASES, passando a coluna Valor Atual como intervalo a ser somado, a coluna Status como intervalo de critério e o valor “Em carteira” como critério de avaliação.
=SOMASES('Registro de Operações'!I:I;'Registro de Operações'!G:G;"Em carteira")
Para a coluna Lucro Total,podemos utilizar a função SOMA passando o intervalo da coluna Lucro da Operação após IT.
=SOMA('Registro de Operações'!L:L)E para a coluna Total Taxas e Impostos, podemos fazer o mesmo, mas definindo o intervalo como a coluna Impostos e Taxas.
=SOMA('Registro de Operações'!J:J)
Com isso, temos três importantes informações sendo exibidas no nosso Painel. Você pode formatar o visual dessa tabela conforme achar necessário.
Para aprofundar ainda mais nossas análises, podemos voltar para a planilha de Registro de Operações, ir em Inserir e selecionar a opção de Tabela Dinâmica.
Isso criará uma nova planilha onde poderemos criar nossas tabelas dinâmicas para analisarmos mais profundamente os dados da tabela de registros.
Podemos, por exemplo, criar uma tabela dinâmica considerando o Valor Atual por Categoria.
Assim como podemos criar análises para o Valor Atual por Descrição, que trará o valor para cada produto.
E Valor Atual por Tipo.
Lembrando que nessa parte você pode construir diversas análises, considerando os impostos, os lucros por operação, e muito mais. Esses são apenas alguns exemplos rápidos para que você tenha algumas ideias.
A partir das tabelas dinâmicas geradas, podemos criar gráficos para cada uma delas, como gráficos de rosca, coluna e pizza.
Podemos copiar esses três gráficos para a planilha Painel; assim, além das principais informações, também teremos a análise dessas métricas a partir dos gráficos.
Na aula de hoje, eu te mostrei como construir uma planilha de investimentos no Excel a partir do zero! Com essa planilha, você será capaz de organizar e analisar seus investimentos de forma eficiente e completa.
Você ainda pode complementar essa tabela adicionando novas informações, tabelas dinâmicas e gráficos, de acordo com suas necessidades e análises.
Além disso, esse é um ótimo exercício para você praticar conceitos importantes do Excel, como formatação de tabela, validação de dados, fórmula SE, fórmula SOMASES e tabelas dinâmicas.
E caso queira uma planilha de investimentos completa e pronta para utilizar, confira também o nosso modelo pronto aqui.
Essa é uma apostila básica de Excel para que você saia do zero de forma 100% gratuita!
Para acessar outras publicações de Excel Intermediário, clique aqui!
Expert em conteúdos da Hashtag Treinamentos. Auxilia na criação de conteúdos de variados temas voltados para aqueles que acompanham nossos canais.
Quer sair do zero no Excel e virar uma referência na sua empresa?
Inscreva-se agora no Excel Impressionador!