Teoria das carteiras: passos para otimização no Excel - Contabilidade & Métodos Quantitativos

Contabilidade & Métodos Quantitativos

Contabilidade & Métodos Quantitativos

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





Nenhum comentário:

Postar um comentário

Translate