Nos últimos anos, tivemos um verdadeiro boom na Bolsa brasileira: em razão da queda da taxa de juros e necessidade de encontrar investimentos com retornos maiores que os proporcionados pela Renda Fixa, tivemos a entrada de diversos brasileiros na Bolsa.
Assista abaixo o vídeo demonstrativo da Planilha para Gestão de Carteira de Ações no Excel. Para adquirí-la, clique no banner acima!clique aqui!
Com isso, é comum vermos o questionamento de como é possível fazer o controle de compra e venda de suas ações dentro do Excel, para que o investidor tenha um panorama geral da sua carteira. É sobre isso que veremos neste artigo.
A ideia é mostrar como criar uma planilha de controle de compra e venda de ações do zero. O grupo ao qual se destina este artigo é principalmente o holder (aquele que compra as ações para manter, acumulando-as ao longo do tempo e fazendo preço médio).
Primeiro passo: crie uma planilha para controlar toda compra e venda feita
É isso. Precisamos de uma planilha para registrar toda compra e venda realizada. Até a próxima!! (rs)
Em uma planilha, crie colunas com nomes de informações que serão relevantes para a nossa base. Na base deste artigo, uso: Data, Tipo (Compra/Venda), Ativo, Número de Ações, Preço Unitário, Custos, Nota Corretagem. A figura 1 ilustra a base, já com essas colunas, algumas adicionais e informações preenchidas.
Toda vez que uma negociação de compra e venda for feita, as colunas de Data (A) a Nota de Corretagem (G) devem ser preenchidas. Por termos poucas informações (são 7 colunas a preencher), não leva muito tempo.
As informações são:
- Data: data em que a nota de corretagem foi emitida
- Tipo: preencher com “Compra” ou “Venda”, dependendo do movimento que foi realizado. Preencher apenas com essas duas opções, visto que usaremos as informações para cálculo de carteira atual e preço médio
- Ativo: ticker da ação, ou código do papel. Mesmo se existir lote fracionário, preencha sem o F (ou seja, mantenha a estrutura normal do código)
- Número de ações: número de papeis comprados naquela operação (você fica sabendo disso na hora de informar para compra/venda, e também na nota de corretagem)
- Preço unitário: o preço em que cada ação, naquela compra, foi comprada (você fica sabendo disso na hora de informar para compra/venda, e também na nota de corretagem)
- Custos: cada boleta realizada tem um custo total. Coloque nesta coluna o custo relacionado àquela compra
- Nota Corret.: preencha com a nota de corretagem, para controle futuro, se for preciso
Perceba que, na figura 1, temos duas colunas destacadas, não explicadas anteriormente. São as colunas de Custo Ações e Custo Total. Tratam-se de duas colunas com fórmulas, explicadas abaixo:
- Custo Ações = Número Ações * Preço Unitário (representa apenas o custo total da operação, sem considerar custos)
- Custo Total = Número Ações * Preço Unitário + Custos (representa o custo total da operação, considerando os custos)
Quando colocamos essas duas fórmulas no Excel, elas ficam de acordo com a figura 2.
Note na figura 2 que temos um elemento adicional: um SE(B2=”Compra”;1;-1). Esta etapa do código serve para calibrarmos os valores de Compra como positivos e de Venda como negativos. Quando formos fazer preço médio, é importante que atenuemos os preços o patrimônio com as vendas realizadas. Caso não fizéssemos, as operações de venda entrariam somando no nosso patrimônio, o que não é verdade (visto que ao realizar uma venda estamos diminuindo nossa posição em determinado ativo).
Em resumo, é isso: em cada operação de compra e venda, venha para a planilha e use uma linha para registrar a operação. As colunas H e I da Figura 2 serão fórmulas que devem ser “copiadas e coladas” para todas as novas operações registradas. Com isso feito, é hora de dar o próximo passo.
Pausa para o vídeo
Veja abaixo um vídeo em que a nossa planilha é utilizada para a função de Rebalanceamento de Carteira:
Criando um painel resumo
Usaremos o nosso controle de compra e venda de ações para criar um painel resumo, que te dará uma visão clara de como estão suas posições. Ele pode ser criado em uma nova aba, por exemplo, que aqui chamarei de “Resumo_Gestão”.
O layout (cor das linhas, fontes, bordas, etc…) fica a gosto do freguês. Neste exemplo, estou usando o layout mostrado na figura 3.
Para o preenchimento da coluna “Papel”, temos duas opções: ou colocamos uma a uma as empresas que quer controlar (geralmente um investidor “holder” lembra de cabeça tudo que tem), ou copiamos a coluna “Ativo” da nossa planilha de controle de compra e venda, colamos em algum lugar e removemos duplicadas (aba Dados > Ferramentas de Dados > Remover Duplicadas). Com este processo, teremos todos os papeis que tiveram alguma operação, sem repetição. Depois é só colar na coluna “Papel”.
Preço médio
Preencheremos inicialmente a coluna # ações, que representa a quantidade de ações daquele papel na carteira.
Olhando a figura 1, observamos que devemos somar todos os valores da coluna D, desde que a coluna C seja igual ao nome do papel e a coluna B esteja indicado como compra. Além disso, devemos subtrair, pelo mesmo racional, as linhas denotadas como venda. Para isso, utilizaremos a fórmula SOMASES, e a fórmula da primeira linha da tabela nesta coluna fica:
=SOMASES(Base_Operações!D:D;Base_Operações!C:C;C7;Base_Operações!B:B;”Compra”)-SOMASES(Base_Operações!D:D;Base_Operações!C:C;C7;Base_Operações!B:B;”Venda”)
A figura 4 exemplifica o cálculo citado.
Para o cálculo da cotação média, dada pela coluna “cot média”, devemos fazer um racional semelhante ao anterior, mas dessa vez utilizando a coluna I (ref. Figura 1), que nos traz o custo total gasto para encarteirar aquela ação (incluindo as taxas). No entanto, ao invés de subtrair o valor das ações vendidas, nós iremos somar, visto que o valor das ações de venda já está negativo, conforme visto na figura 2.
O cálculo é feito em duas etapas: primeiro somamos tudo que foi gasto para adquirir as ações (fazendo um SOMASES com a coluna I). Depois dividimos pelo número de ações (o que já acabamos de ver). Para o número de ações, podemos dividir inclusive pela célula que acabamos de calcular, na coluna E. Feito isso, teremos uma cotação “média”: tudo que foi gasto dividido pelo número de ações (isso significa que, ao invés de ter feito várias compras “picadas”, poderia ter sido feita uma única compra de n ações a um preço médio x).
Dessa forma, a coluna F terá a seguinte fórmula:
=(SOMASES(Base_Operações!I:I;Base_Operações!C:C;C7;Base_Operações!B:B;”Compra”)+SOMASES(Base_Operações!I:I;Base_Operações!C:C;C7;Base_Operações!B:B;”Venda”))/E7
A figura 5 exemplifica o cálculo citado:
As observações:
- Perceba que, conforme dito, dessa vez os SOMASES estão com sinal de + ao invés do sinal de –
- Os SOMASES estão entre (), visto que vamos dividir o valor da soma de ambos pela célula E7 (número de ações, de forma a obter o preço médio)
Para finalizar essa etapa, precisamos apenas chegar no valor total (coluna G), multiplicando os valores # ações (coluna E) por cot média (coluna F). A figura 6 mostra essa etapa realizada, inclusive com uma nova linha de “Total”, ao fim da tabela.
Preço atual
Terminada a etapa anterior, podemos ir para a etapa do preço atual, que é relativamente mais simples.
Precisaremos criar uma aba extra com as informações das cotações. Geralmente essas informações são obtidas no próprio portal da sua corretora. Basta copiar e colar em uma aba a parte para consultarmos. No nosso caso, a aba criada e já com os valores das cotações está na figura 7.
O preenchimento do painel (“Resumo_Gestão”), bloco Preço Atual, será feito da seguinte forma:
– para a coluna “# ações”, faremos uma ligação à coluna “# ações” do bloco “Preço Médio” (o racional é o mesmo e o número de ações também).
– para a coluna “cot atual”, faremos um PROCV para buscar os dados na aba Cotações (colunas A:B, retornando coluna 2)
– para a coluna “total”, multiplicaremos os dois valores (mesmo racional anterior)
As fórmulas são:
- Coluna # ações: =E7
- Coluna cot atual: =PROCV(C7;Cotações!A:B;2;0)
- Coluna total: =I7*J7
A representação está na figura 8:
Feito isso, é possível expandir a fórmula da linha 7 para as demais linhas da tabela, incluindo também uma linha final de “Total”. A nossa tabela com essa etapa concluída está na figura 9.
Um ajuste final: indicadores de rentabilidade e participação na carteira
Para terminar a visão da nossa gestão de carteira, vale incluirmos dois indicadores fundamentais para a gestão de carteira: rentabilidade e participação do ativo na carteira.
- Rentabilidade
A rentabilidade é quanto variou o investimento feito. Sua fórmula é: valor atual/valor médio – 1.
A coluna de rentabilidade no nosso painel será a divisão das colunas K por G (poderíamos fazer com os preços mas optamos por fazer com o valor total… o resultado é o mesmo).
Sendo assim, quando incluímos uma coluna de rentabilidade, sua fórmula deve ser (a exemplo da linha 7): =K7/G7-1
- Participação na carteira
A participação na carteira é o percentual que o valor atual daquele ativo representa do total investido. Sua fórmula é: valor atual/valor total (atual: de um ativo, total: soma do atual de todos os ativos).
A coluna de participação no nosso painel será a divisão da coluna K daquela linha pela célula K26, que é o total, fixada com cifrão ($).
Sendo assim, quando incluímos uma coluna de participação, sua fórmula deve ser (a exemplo da linha 7): =K7/$K$26
O resultado final
Após todo o trabalho, o nosso painel ficou como na figura 10.
A atualização, conforme vimos, é simples:
- Quando houver uma nova operação (compra ou venda), incluir uma linha no fim da base “Base_Operações”
- Caso seja um novo ativo, incluir uma linha final no painel “Resumo_Gestão”com seu nome na coluna C
- Para atualizar as cotações, atualize a tabela da aba “Cotações”
O painel se encarrega do resto.
Com este painel, conseguimos facilmente ver como está nossa carteira atualmente, como ela se comporta frente ao valor investido e onde está concentrado o seu dinheiro no valor atual. Ela é fundamental para uma gestão de ativos.
Até a próxima!
Planilha de Gestão de Carteira de Ações: tudo pronto
Por muitas vezes, mais fácil do que fazer é adquirir o material já pronto. Isso valoriza a sua hora e faz a solução chegar mais rápido. Dessa forma, criamos uma planilha completa de Gestão de Carteira de Ações. Partimos da planilha referência utilizada nesse artigo e criamos uma versão mais completa, por meio da qual é possível fazer uma boa gestão da sua carteira.
Note abaixo quais são os principais conteúdos da planilha:
– todos os ativos cadastrados precisam ter operações lançadas de compra e venda, o que é feito facilmente nessa aba de negociações. Basta inserir data, tipo da operação, ativo e valores envolvidos para a operação ser lançada
– a planilha principal é composta do cadastro dos ativos e cálculos diversos. Dentre os cálculos automáticos feitos pela planilha, estão: 1) cálculo do preço médio, 2) cálculo da posição atual, 3) cálculo do lucro por ativo, 4) rebalanceamento automático de carteira, 5) simulação de compra, calculando a posição futura
Planilha de Gestão de Carteira: motivos para adquirir
Com isso, veja abaixo os principais motivos para adquirir a planilha conosco:
– Garantia de 7 dias: você pode pedir a devolução de 100% do valor investido nesse período
– Ganho de tempo: você não precisa tentar fazer a planilha por conta, sendo que já há uma pronta
– Investimento com retorno: o valor investido é recuperado em forma de maior controle dos seus ativos
– Você conseguirá usar: diversos vídeos explicativos estão disponíveis. Basta copiar o que faço para o sucesso da planilha
– Uso imediato: após o pagamento você já acessa a plataforma e baixa a planilha
– Qualidade: são anos de Excel e de experiência para trazer um conteúdo adequado e resumido para você
– Diversas funcionalidades: a planilha oferece diversos cálculos prontos. Basta entrar com seus dados e aproveitar
Planilha de Gestão de Carteira: investimento
A planilha do Excel Genial está à venda por R$49,00, podendo ter o valor parcelado em três vezes.
Valor: R$49,00, dividido em até três vezes
Para adquirir a planilha, clique na imagem acima que te levará para a página de compras.
Invista no seu patrimônio por um preço adequado! Adquira uma planilha de qualidade!
Por fim, caso tenha dúvidas sobre o produto e deseja perguntar antes de comprar, fale conosco por meio desse link de Contato, clicando no link.
Por fim, quem viu essa planilha também se interessou pela planilha abaixo! Clique para saber mais!