Definição das proporções da carteira de risco mínimo com 5 ativos: aplicação da ferramenta SOLVER do Microsoft EXCEL

Depois de explorar bastante a análise risco/retorno para ativos isolados e carteiras com dois ativos de risco, agora chegou a vez de carteiras com mais de dois ativos. A teoria fundamental encontra-se em portfolio selection, de Harry Markowitz.

Nsta postagem trago um procedimento específico com o SOLVER do EXCEL para determinar as proporções ideais de até cinco ativos numa mesma carteira. Qual seria o significado de "proporções ideias"? Refiro-me aos valores dos pesos encarregados de gerar o menor risco possível, então, trata-se da iniciativa de encontrar as proporções da carteira de risco mínimo para cinco ativos. É um método específico para ser trabalhado com planilhas, fundamentado nos ensinamentos de Moore e Weatherford (2005) em Tomada de Decisão em Administração com Planilhas Eletrônicas; venho adotando em sala de aula há muito tempo; também já o recomendei em minhas orientações de trabalhos de conclusão de curso em duas oportunidades; pode ser adaptado para quantos ativos você desejar, mas aqui demonstrarei o desenvolvimento com cinco ativos de risco.

Utilizei os seguintes ativos como cobaias: NATU3, CIEL3, KLBN11, JBSS3 e VLID3. Representando o mercado, o IBOVESPA. Por questão de facilidade de acesso, coletei os dados do google finance; baixei seis planilhas com os históricos das cotações diárias; período de 30/12/2014 até 31/07/2017; trabalhei somente com retornos ao mês, então selecionei apenas as cotações de fechamento do último dia útil de cada mês; como são 32 meses, obtive seis amostras com 31 valores do retorno ao mês (de 30/01/2015 até 31/07/2017).

Veja o modelo de planilha na Figura 1 - faça logo o download antes de continuar com a leitura desta postagem, assim você poderá seguir todos os passos na prática (clique aqui). 

Figura 1

Definir os históricos mensais dos retornos dos ativos NATU3, CIEL3, KLBN11, JBSS3 e VLID3 e do mercado (IBOV) é o primeiro passo a ser implementado. Veja a sequência de imagens na Figura 2: inserir =(Q3/Q4-1)*100 na célula I2; arrastar/copiar copiar a fórmula da célula I2 até N2; selecionar e depois arrastar até a linha 33 o intervalo I2:N2. 

Figura 2

Observe a Figura 3: a matriz das proporções nesta postagem é uma matriz linha com cinco colunas (1x5); trata-se do intervalo A3:E3; como todos sabem, a soma das proporções dos ativos numa carteira deve ser sempre igual a 1,00 (100%); insira =SOMA(A3:E3) na célula F3, pois será necessário confirmar a resposta da soma dos pesos no final do processo, além de ser uma restrição para usar o SOLVER.

Figura 3 

A segunda matriz, a matriz das covariâncias, tem cinco linhas e cinco colunas (5x5). Siga atentamente as instruções indicadas na Figura 4: nas células B7, B8, B9, B10 e B11, insira as fórmulas =COVARIAÇÃO.P($J$3:$J$33;J3:J33), =COVARIAÇÃO.P($K$3:$K$33;J3:J33), =COVARIAÇÃO.P($L$3:$L$33;J3:J33), =COVARIAÇÃO.P($M$3:$M$33;J3:J33) e =COVARIAÇÃO.P($N$3:$N$33;J3:J33), respectivamente; em seguida, selecione e arraste até a coluna F o intervalo B7:B11; no final, a matriz covariância será o intervalo B7:F11.

Figura 4

Terceira matriz, a matriz dos betas dos ativos, também é uma matriz linha com cinco colunas (1x5). Observe a Figura 5: insira =INCLINAÇÃO(J3:J33;$I$3:$I$33) na célula A15 (calcula o beta da NATU3); o congelamento do intervalo I3:I33 ($I$3:$I$33) tem a ver com o fato de ser o histórico do retorno mensal do mercado; arraste/copie a fórmula da célula A15 até a coluna E.   

Figura 5

Agora vamos para a melhor parte, explicada a partir da Figura 6: para calcular a variância, o desvio padrão e o beta da carteira com cinco ativos, insira nas células A19, C19 e E19 as fórmulas =SOMARPRODUTO(MATRIZ.MULT(A3:E3;B7:F11);A3:E3), =RAIZ(A19) e =SOMARPRODUTO(A15:E15;A3:E3), respectivamente. 

Figura 6

Só para ter como melhorar a compreensão do conceito de diversificação, nas linhas 21 a 23 inseri mais uma matriz, também do tipo linha com cinco colunas (1x5), com os valores dos desvios padrões dos ativos. Observe a Figura 7: insira =DESVPAD.P(J3:J33) na célula A23 para calcular o risco da NATU3; arraste/copie o procedimento até a célula E3.

Figura 7

Nada mais faltando, vamos agora à aplicação do SOLVER. Siga as instruções das 8 a 11. 

Na Figura 8: em Dados, clique em ?Solver - se você não encontrar no local indicado, então instale o suplemento a partir das opções do Microsoft Excel (Opções / Suplementos / Ir / habilitar Solver). 

Figura 8

Na Figura 9: após clicar em ?Solver, utilize a célula A19 (variância da carteira) como referência à solução; em "Definir Objetivo:", habilite "Mín." (para encontrar a variância da carteira de risco mínimo); em "Alterando Células Variáveis", inclua o intervalo A3:E3 (matriz das proporções); em "Sujeito às Restrições":, adicione a necessidade de a soma das proporções ser igual a 1, portanto, F3=1 (clique em "Adicionar" para inserir a restrição); não desabilite, sob nenhuma hipótese, "Tornar Variáveis Irrestritas Não Negativas"; e o método deve ser GRG Não Linear; clique em Resolver.

    

Figura 9

Na Figura 10: depois de clicar em Resolver, a janela "Resultados do Solver" perguntará se você deseja manter a solução do SOLVER; sua resposta deve ser afirmativa, então pressione OK para aceitar.  

Figura 10

Vamos analisar o resultado final, exposta na Figura 11: o intervalo A3:E3 traz as proporções obtidas com o SOLVER; se um investidor adotar as mesmas, aceitando como válida a hipótese de os níveis de risco percebidos para cada ativo no período de análise (passado) se repetirem no futuro, NATU3, CIEL3, KLBN11, JBSS3 e VLID3 deverão compor uma carteira com 15,32%, 37,72%, 36,22%, 8,31% e 2,42%, respectivamente. Lembre-se: trata-se da composição responsável por gerar o menor risco possível dentre todas as possibilidades, portanto, trate-a como carteira de risco mínimo. Analise agora a qualidade da diversificação: o risco da carteira, estimado em aproximadamente 4,26%, é bem inferior ao risco do ativo de menor risco, a CIEL3, com desvio padrão de 7,07%.  

Figura 11

Pronto, mais uma postagem finalizada! E tome análise risco/retorno! Esta é a quarta postagem seguida sobre o tema.

Façam bom proveito. Registrem dúvidas e sugestões nos comentários. 

Cordialmente,

Adail Marcos