Otimimização de uma carteira de ações – exemplo em Excel

Alguns leitores me pediram um exemplo de como fazer otimização de carteiras utilizando o Excel. Atendendo a estes pedidos, preparei um exemplo que permite otimizar uma carteira com até 50 ações do mercado brasileiro. Informações úteis para entender as contas feitas na planilha podem ser encontradas aqui, aqui e aqui.

Faça o download da planilha:

Download-Button_1700x1000_zpsce011000

 

 

Vamos aos detalhes.

Dados

Os dados na planilha dizem respeito às 50 ações com maior volume no período de agosto 2012 a agosto de 2013. Esta escolha é arbitrária e o leitor pode modificar a planilha à vontade para refletir melhor o universo de ações que lhe for mais conveniente. 

Estimação dos parâmetros de mercado

Para realizar a otimização de carteiras dentro do paradigma de Markowitz, é necessário estimar as seguintes quantidades para o horizonte de investimento do investidor:

  • Retornos esperados de todos os ativos
  • Matriz de covariância dos ativos

Os retornos esperados, como o próprio nome diz, são a expectativa que o investidor tem sobre o retorno total de cada ativo. A matriz de covariância possui informações sobre a variabilidade de cada ativo e também sobre como os preços dos ativos se movimentam de maneira conjunta. Esta informação permite que se explore um benefício de diversificação.

Existem diversas maneiras de estimar estes valores. Para este exemplo, optei por utilizar dados históricos em frequência diárias. Um ponto crucial em um problema de alocação de ativos é o horizonte de investimento do investidor. Para que o problema de otimização esteja bem definido, é necessário que as quantidades acima sejam projetadas para este horizonte. Por exemplo, se o horizonte do investidor é mensal, mas os dados utilizados na estimação são diários, é necessário de alguma maneira transformar as estimativas diárias para quantidades mensais.

Aqui surge uma complicação relacionada ao tipo de retorno que utilizamos. O paradigma média-variância de Markowitz é definido em termos de retornos lineares. O retorno linear de um ativo entre os tempos t-1 e t pode ser definido por

R_t = \frac{P_{t}}{P_{t-1}}-1,

onde P_t é o preço do ativo no instante t . Os retornos lineares possuem a característica importante de que eles podem ser agregados ou somados entre ativos diferentes. Ou seja, dada uma carteira de N ativos representada por um vetor de pesos \boldsymbol w = \left(w_1, \cdots, w_N \right) , onde w_i corresponde à proporção do valor da carteira investida no ativo i, o retorno da carteira pode ser calculado como uma combinação linear dos retornos dos ativos:

R_t^{Carteira} = \boldsymbol w ' \mathbf{R}_t=\sum_{i=1}^{N}{w_i R_{i,t}}

Por outro lado, para agregar retornos ao longo do tempo, a relação é multiplicativa. Por exemplo, o retorno total entre t-2 e t pode ser calculado como

R_{t-2,t} = (1+R_{t-2,t})(1+R_{t-1,t})-1.

A relação acima não é particularmente conveniente, especialmente para transformar retornos entre horizontes diferentes. Um alternativa é trabalhar com retornos compostos, também conhecido como log-retornos. O log-retorno entre os tempos t-1 e t pode ser definido por

r_t = \log{\left(\frac{P_{t}}{P_{t-1}}\right)}.

Os log-retornos tem a característica conveniente de poderem ser agregados ao longo do tempo. O log-retorno entre os tempos t-2 e t pode ser calculado como

r_{t-2,t} = r_{t-2,t}+r_{t-1,t}.

Para valores pequenos, por exemplo em horizontes diários, o log-retorno é uma aproximação boa do retorno. Porém, quanto maior o retorno, pior é esta aproximação.

Ao escolher trabalhar com log-retornos diários, torna-se trivial projetar as estimativas para qualquer horizonte de investimento desejado. Por exemplo, se o horizonte desejado é mensal, então a projeção dos retornos esperados e da matriz de covariância são obtidos através da multiplicação das estimativas diárias por 21 (número de dias úteis em um mês). Caso o horizonte seja anual, multiplicamos por 252, e assim por diante.

Há um segundo motivo para trabalhar com log-retornos. Do ponto de vista de modelagem, a escolha de modelar os log-retornos como variáveis com distribuição normal implica em uma distribuição log-normal para os preços dos ativos. Isto é mais intuitivo e realista do que modelar os retornos como variáveis normais, o que implicaria que os preços teriam distribuições normais e permitiria, por exemplo, a possibilidade de preços negativos.

Apesar destas vantagens, os log-retornos não são agregáveis entre ativos diferentes, ou seja, o log-retorno de uma carteira de ativos não é uma combinação linear dos retornos dos ativos. Logo, para obter estimativas de retorno esperado e matriz de covariância dos retornos lineares, uma transformação é necessária. Esta transformação é realizada de maneira interna pela planilha. Os detalhes podem ser obtidos neste artigo.

Otimização

A otimização realizada pela planilha é a descrita aqui. Há três opções principais:

  • Calcular Fronteira Eficiente – esta opção realiza o cálculo de 20 portfolios na fronteira eficiente. Os portfolios são definidos a partir de retornos esperados mínimos igualmente espaçados entre o retorno esperado da carteira de variância mínima e o maior retorno esperado dentre todos os ativos.
  • Otimizar Carteira – esta opção realiza a otimização da carteira com os parâmetros que estiverem selecionados.
  • Carteira de Variância Mínima – esta opção calcula a carteira de variância mínima global.

Finalmente, é necessário fazer alguns comentários e observações sobre as escolhas feitas na construção  da planilha:

  • As ações foram selecionadas entre as de maior volume. O número de ações (50) também representa uma escolha arbitrária. O leitor pode modificar a planilha para utilizar um número maior ou menor de ações.
  • A janela e frequência dos dados utilizados na estimação também representam uma escolha arbitrária. Outras escolhas são possíveis e podem levar a resultados diferentes.
  • Um grande número das ações escolhidas apresenta retorno negativo no período utilizada na planilha. Isto implica que algumas carteiras “otimizadas” podem apresentar retorno esperado negativo.
  • O otimizador do Excel (Solver) não é a melhor escolha para se realizar otimizações com um grande número de variáveis. Apesar de teoricamente ser possível expandir a planilha para um número arbitrário de ações, é improvável que os resultados obtidos com o Solver sejam confiáveis. Ao leitor interessado em realizar otimizações de maior escala, sugiro a utilização de um software como R (gratuito) ou Matlab.
  • Apesar de o exemplo utilizar apenas ações, a estrutura pode ser usada para otimizar carteiras com outras classes de ativos, desde que possa-se modelar os ativos através dos (log)retornos. Isto seria viável por exemplo com ETFs e moedas, por exemplo, mas não com títulos do governo.
  • A fronteira eficiente calculada é a fronteira no contexto de Markowitz. Caso o leitor queira introduzir um ativo livre de risco (que no caso brasileiro pode ser proxied pelo CDI, por exemplo), é possível construir uma nova fronteira eficiente, definida pela Reta de Alocação de Capital.
  • A estimativa da matriz de covariância utilizada na planilha é a covariância amostral. Em alguns casos, esta matriz pode não ser bem comportada. Neste caso outras alternativas (shrinkage, modelo de fatores) precisam ser utilizadas.
  • A planilha é oferecida gratuitamente aos leitores “no estado” e sem garantias de qualquer tipo. O uso desta planilha é de responsabilidade do leitor e, como sempre, vale o velho e bom disclaimer.

A planilha pode ser baixada na seção de Downloads ou diretamente neste link.
PlanilhaOtimizacao

Anúncios

11 comentários sobre “Otimimização de uma carteira de ações – exemplo em Excel

    • Angélica, primeiro calculo o desvio padrão simples dos log retornos. Depois uso um ajuste para encontrar a volatilidade dos retornos. A fórmula esta descrita em um comentário na planilha, com uma referência de um artigo que explica porque este ajuste é necessário.

      Espero que ajude!

      Abs

  1. Olá,

    Poderia, por gentileza, me explicar onde posso modificar o número de ações de 50 para 136?
    Copiei todos os dados, porém quando o macro realiza o cálculo, ela considera apenas as 50 primeiras ações.

    Obrigada

    • Oi Evelin,

      Quando montei a planilha, a ideia era apenas exemplificar os cálculos envolvidos, ou seja, ela não está preparada para automaticamente aceitar um número qualquer de ações. Para que a planilha funcione corretamente com um número qualquer de ações, será necessário redefinir alguns campos. Em primeiro lugar, é preciso colocar os novos dados de preços e após isto atualizar a aba com os retornos. Esta aba faz referência à aba de preços, portanto é importante ter atenção para que as referências estejam corretas. Após isto, na mesma aba de retornos, há o cálculo da matriz de covariância, que também precisa ser atualizado com a referência correta. Se você vai usar 136 ações a matriz de covariância terá tamanho 136 x 136.

      Em seguida, na aba Otimizacao, você precisará redefinir alguns nomes de campos nos quais a macro cola informações. No Excel, você precisa ir na aba Formulas e em seguida no “Name Manager”. As seguintes referências precisam ser atualizadas. Por exemplo:
      CovExp – matriz de covariância diária dos retornos na aba “Retornos”
      CovMat – matriz de covariância final na aba “Otimizacao”
      medias – vetor de retornos médios na aba “Otimizacao”
      MediasExp – vetor de retornos médios na aba “Retornos”

      e assim por diante. Se você atualizar estes nomes, deverá funcionar.

      Abs!

  2. Obrigado por este conteúdo e pelo ficheiro de EXCEL. Sabe de algum código MATLAB preparado para o cálculo da Fronteira Eficiente de Markowitz para mais de 2 ações?

    Obrigado!

    • Oi Claudia, obrigado. O Matlab possui um pacote (Financial Toolbox) com varias funções para cálculo de fronteira eficiente. Um outro recurso são os códigos do livro “Risk and Asset Allocation” do Attilio Meucci. Tem de graça no File Exchange do Matlab.

  3. Boa tarde, Dr. Nickel.

    estou com uma dúvida, na planilha, em alguns pontos o retorno esperado é menor que o retorno mínimo, isso está correto?
    No seu artigo você diz que o retorno esperado deve ser igual a um retorno desejado pelo investidor. Estou supondo que esse retorno mínimo é o retorno desejado pelo investidor.

    Obrigado.

    • Oi Fábio, sim, o retorno mínimo é o retorno desejado pelo investidor. Quando você diz que “em alguns pontos o retorno esperado é menor que o retorno mínimo”, onde você quer dizer?

      • Oi Dr. Nickel, desculpe a demora na resposta.
        Por exemplo, no ponto 7, o retorno mínimo foi 3,11% mas o retorno esperado é de 2,92%. O retorno esperado não deveria ser igual ao mínimo?

        Obrigado.

  4. Muito bom conteúdo, mas como sou novato na área de otimização de carteira, não consegui transformar a planilha para o meu presente objetivo que seria fazer a otimização de uma carteira com 4 ações com cotações diárias de 31/12/2012 a 29/12/2017. Alguém pode ajudar?

Deixe um comentário

Preencha os seus dados abaixo ou clique em um ícone para log in:

Logotipo do WordPress.com

Você está comentando utilizando sua conta WordPress.com. Sair /  Alterar )

Foto do Google+

Você está comentando utilizando sua conta Google+. Sair /  Alterar )

Imagem do Twitter

Você está comentando utilizando sua conta Twitter. Sair /  Alterar )

Foto do Facebook

Você está comentando utilizando sua conta Facebook. Sair /  Alterar )

Conectando a %s