💵 Aprenda a Criar uma Planilha de Investimentos no Excel
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.
Uma planilha de investimentos no Excel reúne, em uma aba de registro, dados como ativo, categoria, preço de compra, preço atual, quantidade, impostos e status. Com validação de dados, a função SE e a SOMASES, você calcula lucro e total em carteira; tabelas dinâmicas e gráficos montam um painel para acompanhar tudo.
O que você vai aprender?
💵 Aprenda a Criar uma Planilha de Investimentos no Excel
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!
Inserindo as Informações Básicas
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.
Preço de Venda e Lucro Unitário antes dos Impostos e Taxas
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.
Formatando como Tabela
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.
Impostos e Taxas e Lucro da Operação
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.
Nova Planilha – Painel
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.
Excel ImpressionadorTudo que você precisa saber, do Básico ao Avançado, pra dominar a ferramenta mais importante do Mercado de Trabalho. Aprenda as principais ferramentas, funções e recursos do Excel para deixar qualquer planilha mais chamativa e eficiente.
Começar agora
Ajustes da Planilha 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]
Painel – Cálculos dos Investimentos
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.
Tabelas Dinâmicas
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.
Criando Gráficos
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.
Perguntas frequentes
1. Como criar uma planilha de investimentos no Excel do zero?
Comece definindo as colunas essenciais: data da compra, ativo, categoria, tipo, preço, quantidade, impostos e status. Use validação de dados para criar listas suspensas (renda fixa ou variável, em carteira ou vendida) e formate o intervalo como tabela. Depois, adicione as fórmulas de lucro e monte um painel com os totais.
2. Qual fórmula calcula o lucro dos investimentos?
O lucro unitário usa a função SE para conferir se o ativo já foi vendido, retornando a diferença entre preço atual e preço de compra. O lucro da operação multiplica esse valor pela quantidade e desconta impostos e taxas. No painel, a SOMASES totaliza o valor em carteira por status.
3. Como fazer o controle de renda fixa e variável na planilha?
Use a validação de dados na coluna Categoria para escolher entre renda fixa e variável, e na coluna Tipo para listar poupança, CDB, Tesouro Direto, ações e FII. Assim, cada ativo fica classificado corretamente e você pode criar tabelas dinâmicas que somam o valor atual por categoria, tipo ou descrição.
4. Dá para acompanhar os investimentos com gráficos no Excel?
Sim. A partir das tabelas dinâmicas você cria gráficos de rosca, coluna e pizza mostrando o valor atual por categoria, tipo ou descrição. Copie esses gráficos para a aba Painel, junto com o total em carteira, o lucro total e o total de taxas e impostos, formando uma visão completa da carteira.
Conclusão – Aprenda a Criar uma Planilha de Investimentos no Excel
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.
Caso prefira esse conteúdo no formato de vídeo-aula, assista ao vídeo abaixo ou acesse o nosso canal do YouTube!
O que você aprende neste vídeo
Resposta rápida: Tudo parte de um registro de operações único, que serve para renda fixa e variável: data, categoria, tipo, descrição, quantidade, preço de compra, preço atual, status, impostos e taxas. O lucro é calculado por SE a partir do status (só operação vendida gera lucro), e o painel sai de SOMASE (total em carteira) + tabelas dinâmicas.
Neste vídeo (26 min):
- 2:00 - A estrutura é a mesma para renda fixa e variável, muda o que se preenche em categoria e tipo.
- 3:00 - Validação de dados em lista na categoria (renda fixa / variável) e no tipo (poupança, CDB, tesouro, ações, FII).
- 5:00 - O status tem duas opções, em carteira ou vendida, e é ele que define o que entra em cada conta.
- 7:00 - Lucro unitário é preço atual menos preço de compra; lucro da operação multiplica isso pela quantidade.
- 8:00 - Sem o
SE, ativo ainda em carteira apareceria com lucro negativo, porque o preço de venda está vazio. - 9:00 - Formatar como Tabela propaga validações, formatos e fórmulas para cada linha nova.
- 11:00 - Uma coluna que costuma faltar: impostos e taxas, imposto sobre o lucro, taxa de administração, que muda o resultado real.
- 13:00 - Daí saem duas visões úteis: lucro antes e lucro depois de impostos e taxas.
- 17:00 - Virada importante: preço de venda vira preço atual, para o ativo em carteira também ser marcado a mercado.
- 18:00 - Com isso o gatilho do
SEmuda: não é mais “preço preenchido”, éstatus = "vendida". - 19:00 - Uma coluna de valor atual (preço atual × quantidade) prepara o total da carteira.
- 20:00 - O total em carteira é um
SOMASEsobre essa coluna, com critério em carteira no status. - 21:00 - Lucro total e total de impostos são
SOMAsimples, não precisam de critério. - 22:00 - O painel fecha com tabelas dinâmicas (valor atual por categoria, por descrição, por tipo) e os gráficos recortados para lá.
Apostila Básica de Excel
Essa é uma apostila básica de Excel para que você saia do zero de forma 100% gratuita!
Hashtag Treinamentos
Para acessar outras publicações de Excel Intermediário, clique aqui!
Posts mais recentes de Excel Intermediário
- PROCV com SE: Como Combinar as Funções no Excel
Aprenda a combinar PROCV com SE no Excel para calcular bonificações por desempenho, usando SE aninhado e buscando o salário em outra planilha com o PROCV. - ChatGPT no Excel: use a IA direto na sua planilha
Aprenda a usar o ChatGPT no Excel para limpar dados, corrigir fórmulas e criar gráficos sozinho. Veja como instalar o suplemento oficial da OpenAI. - Série Tabela Dinâmica no Excel: Guia Completo
Domine a Série Tabela Dinâmica no Excel: aprenda a criar, atualizar, usar segmentação de dados, linha do tempo e montar dashboards do zero, passo a passo.
Posts mais recentes da Hashtag Treinamentos
- Planilha de Controle de Notas Fiscais no Excel [Grátis]
Baixe a planilha de controle de notas fiscais no Excel grátis: calcule ISS e retenções, veja o que está em aberto e gere a cobrança de cada nota de serviço. - Qual IA usar na empresa: ChatGPT, Claude ou Perplexity?
Qual IA usar na empresa: veja as diferenças entre ChatGPT, Claude e Perplexity e quando cada um rende mais em cada área da sua equipe. - Apostilas Gratuitas em PDF: Excel, Power BI, Python e IA
Baixe apostilas gratuitas em PDF de Excel, Power BI, Python, Claude e agentes de IA, com exercícios e gabarito. Escolha a sua e comece hoje.
