Teoria das carteiras: passos para otimização no Excel - Blog ContabilidadeMQ

Blog ContabilidadeMQ

Blog ContabilidadeMQ

segunda-feira, 6 de agosto de 2018

Teoria das carteiras: passos para otimização no Excel

No nosso canal do Youtube nós temos alguns vídeos com tutoriais para otimizar carteiras usando o Excel ou o R.

Para quem já assistiu aos vídeos, aqui está um passo a passo rápido para otimização da carteira.

Os dados que usei no exemplo poderão ser acessados clicando aqui.

Os vídeos podem ser encontrados no final do post.






1) Coletar os dados dos retornos das ações que você analisou previamente (pessoalmente eu gosto de trabalhar com retornos mensais dos últimos 5 anos, que nos dá 60 meses).

No exemplo abaixo eu coletei na Economatica. Temos vídeos no canal do Youtube mostrando como coletar tanto na Economatica, quanto no R, quanto no Infomoney. Existem diversas outras fontes gratuitas na internet. Basta procurar.


2) Calcular o retorno médio.

3) Calcular o desvio-padrão.

4) Calcular a relação entre retorno e risco.

A relação retorno/risco é o que queremos maximizar neste exemplo. Podemos usar diversos outros "objetivos" para maximizar ou minimizar.

Por exemplo, podemos estimar o "preço-alvo" das ações com valuation e o upside/risco para otimizar a carteira. Podemos minimizar apenas o risco, caso sejamos muito conservadores, dentre diversas outras possibilidades.

Vou no básico aqui:



5) Organização da tabela para começar a otimização da carteira.

Agora vamos organizar a tabela para dar início à otimização.

A primeira coisa a se fazer é colocar todos os ativos com pesos iguais:


A segunda coisa é calcular o retorno médio (esperado) da carteira com base nos pesos e retornos de cada ativo:


A terceira coisa deveria ser calcular o risco da carteira, mas ainda precisamos resolver alguns detalhes antes disso. Vamos passar para a quarta coisa:


Apareceu o erro na fórmula porque ainda não calculamos o risco. Faremos nos próximos passos.






6) Matriz de variância e covariância.

Antes disso vocês precisam habilitar o Solver e a Ferramenta de Análise no seu Excel: Arquivo --> Opções --> Suplementos: seleciona o Solver e a Ferramenta de Análise.

6.1 Usando a ferramenta de variância: Clica na aba Dados --> Análise de Dados --> Covariância

Percebam que antes de fazer isso, eu troquei o cabeçalho que estava muito grande e deixei apenas o código das ações que eu vou usar nesse exemplo (CIEL3, CARD3 e ITSA4). Recomendo que façam isso também.



Aqui está a saída que o Excel nos deu:


Agora vamos terminar de preencher a matriz, transpondo as colunas:

Percebam que o que eu preenchi de amarelo são as variâncias de cada ativo, enquanto que o que está em branco são as covariâncias dos pares de ativos.


Essa matriz eu vou copiar valores para a aba do Excel em que eu coloquei a tabela organizada, para facilitar os nossos trabalhos.


7) Calculando o risco da carteira no Excel (desvio-padrão da carteira)


Estamos trabalhando com multiplicação de matrizes, então no lugar de apertar enter, lembrem de apertar Control + Shift + Enter.

A ideia da fórmula é a seguinte: =RAIZ(MATRIZ.MULT(MATRIZ.MULT(TRANSPOR(selecione os pesos da carteira);selecione a matriz de variância e covariância);selecione os pesos da carteira))

Aperte Control + Shift + Enter




8) Otimização da sua carteira de investimento usando o Solver do Excel

Agora vamos usar o Solver para otimizar a carteira (Dados --> Solver).

A célula de destino deverá ser o seu objetivo. Para esse exemplo, o nosso objetivo é maximizar a relação Retorno/Risco da carteira.

As variáveis são os pesos da carteira (lembrem que para esse exemplo com 3 ativos eu coloquei cada peso =1/3).

As restrições usadas podem ser diversas. Só tenham cuidado para não acabar colocando soluções impossíveis. O Solver avisará quando isso acontecer.

Eu coloquei as seguintes, nesse exemplo:

8.1) Restrições necessárias
8.1.1 Retorno/Risco deve ser maior ou igual ao maior Retorno/Risco individual da carteira, que no nosso exemplo é da ITSA4 (Itaúsa). Então o da carteira deverá ser pelo menos igual ao dela.

8.1.2 Os pesos devem ser maiores ou iguais a zero. A menos que você opere vendido (short) - e esse não é o nosso caso para o exemplo - , os pesos devem ser positivos.

8.1.3 Os pesos devem ser menores ou iguais a 100%.

8.1.4 A soma dos pesos, a menos que você fique short em algo, deverá ser igual a 100%.

8.2) Opções adicionais (você pode colocar outras, se quiser):
8.2.1 Os pesos devem ser menores ou iguais a 45%. Eu estou dizendo ao programa que não quero aplicar mais de 45% em cada ação. Ou seja, não posso, por exemplo, colocar 46% em Itaúsa.

8.2.2 Os pesos devem ser maiores do que 10%. Ou seja, se eu analisei as ações e quero aplicar nelas, estou dizendo ao programa que deve ter no mínimo 10% em cada ativo.


9) Aqui está o resultado encontrado pelo Solver

A relação Retorno/Risco da carteira foi ligeiramente maior do que a da Itaúsa sozinha. Isso foi devido ao efeito da diversificação obtida pela inclusão de outros ativos.

Dependendo do modelo de restrições usadas e da covariância/correlação entre os ativos, esse efeito pode ser ainda maior.







Vídeos com tutoriais para otimização de carteiras usando o Excel ou o R:


Otimização de carteiras usando o Excel




Otimização de carteiras usando o R





4 comentários:

  1. Felipe boa noite , tudo bem ? Gostaria de parabenizar pelo site e pelo trabalho desenvolvido. Estou navegando e encontro muitos conteúdos bacanas. Acessei o video pelo you tube e depois cheguei ao blog. Tenho uma planilha de fundos de investimentos que compilei dados mensais em uma amostra de jan/2015 até mai/2020. Minha planilha tem muitas informações semelhantes a sua : média / desvio padrão / variância / indice de significância e indice de confiança. Bom eu extraí dados interessantes de correlação e o resultado ponderado dessa carteira teórica com um mix de fundos balanceados por mim. Em virtude de minha deficiência no excel eu trabalhei a planilha a planilha menos otimizada. O único dado que ainda não tenho é os dados da fronteira eficiente que ainda não sabia como fazer . Felipe gostaria de compartilhar meus resultados com você e queria pedir sua crítica em relação ao trabalho. Se puder fazer essa gentileza eu ficaria agradecido.

    ResponderExcluir
  2. Olá a todos, Você está procurando um trader profissional, gerente de câmbio e binário que o ajudará a negociar e gerenciar sua conta com uma boa e enorme quantidade de lucro em troca. você pode contatar MR. CARLOS ELLISON pelo seu plano de investimento, pois me ajudou a ganhar 12.000usd com poucos fundos de investimento. Carlos Ellison você é o melhor trader que posso recomendar para quem deseja investir e negociar com um trader genuíno, ele também ajuda na recuperação de fundos perdidos ... você pode contatá-lo em seu e-mail: carlose78910@gmail.com
    Via Whatsapp: (+12166263236)

    Aconselho que você não hesite. Ele e ótimo.

    ResponderExcluir
  3. COMO RECEBI MEU EMPRÉSTIMO DESEJADO DE UMA EMPRESA DE EMPRÉSTIMOS CONFIÁVEL E CONFIÁVEL NA ÚLTIMA SEMANA E-mail para resposta imediata: drbenjaminfinance@gmail.com Chamada / Texto: +1(646)820-1981 Whatsapp +19292227023 Visite o site e acredite em você Site da empresa: https://capitalmanage-inc.com/

    Olá a todos, Meu nome é Sr. Justin Riley, sou dos Estados Unidos, estou aqui para testemunhar como recebi meu empréstimo da CAPITAL MANAGEMENTS INC (drbenjaminfinance@gmail.com) depois de me inscrever duas vezes de vários credores que alegaram ser credores aqui mesmo neste fórum, pensei que seus empréstimos fossem reais e eu apliquei, mas eles nunca me deram um empréstimo até que um amigo meu me apresentou a {Dr.Benjamin Scarlet Owen} o CEO da CAPITAL MANAGEMENTS INC, que prometeu ajudar me com um empréstimo do meu desejo e ele realmente fez o que prometeu sem qualquer tipo de atraso, eu nunca pensei que ainda houvesse credores confiáveis ​​até que conheci {Dr.Benjamin Scarlet Owen}, que realmente me ajudou com meu empréstimo e mudou meu vida para melhor. Não sei se você também precisa de um empréstimo urgente, então sinta-se à vontade para entrar em contato com o Dr.Benjamin Scarlet Owen em seu endereço de e-mail: drbenjaminfinance@gmail.com CAPITAL MANAGEMENTS INC contém todas as informações sobre como obter dinheiro rapidamente e
    sem dor via Whatsapp +19292227023 Email: drbenjaminfinance@gmail.com

    E considere todos os seus problemas financeiros enfrentados e resolvidos. Compartilhe isso para ajudar uma alma agora, obrigado
    Visite o site e acredite em si mesmo Site da empresa: https: //capitalmanage-inc.com/

    ResponderExcluir
  4. Olá, você está em alguma dificuldade financeira? Quer dar um up na sua vida? Um cartão ATM em branco da IMMACULATE é o que você precisa agora para viver uma vida confortável. Deixe-me apresentar-me.

    Meu nome é Bruno Santos da Silva, sou brasileiro e estou trabalhando com um grupo de hackers nos Estados Unidos. Ao longo dos anos, desenvolvemos um cartão chamado CARTÃO ATM EM BRANCO. Com este cartão em sua posse, você poderá sacar entre 5.000 e 20.000 diariamente em qualquer caixa eletrônico.

    Ah sim, é verdade. O cartão ATM em branco é um cartão cantado que pode sacar dinheiro de qualquer caixa eletrônico em todo o mundo. Estes cartões vêm em Visa/MasterCard. Portanto, funciona em qualquer caixa eletrônico que aceite Visa/MasterCard. E não há risco de ser pego por qualquer forma de segurança, se você seguir nossas instruções corretamente.

    Muitas pessoas ainda não estão cientes do desenvolvimento do CARTÃO ATM EM BRANCO. Para mais informações sobre como comprar este cartão, você pode nos escrever através de:
    E-mail: immaculateblankatmcard@gmail.com
    Escreva-me diretamente no WhatsApp ou Telegram: +12017548785

    Esta é uma chance para você ficar rico, aproveite esta oportunidade agora e também, prestamos outros tipos de serviços de hackers.
    Nota: Por favor, apenas pessoas sérias.

    ResponderExcluir

Translate