Antes de mais nada, gostaria de fazer o seguinte registro: esta é a terceira postagem consecutiva sobre análise risco/retorno com Excel, continuação da postagem Carteira de Risco Mínimo com 2 Ativos: aplicações com o Microsoft Excel - é praticamente a mesma planilha, ensinarei agora como incluir nela a tabela e os gráficos do conjunto de oportunidades de investimento para carteiras com 2 ativos. Então, vamos lá!
O desenvolvimento do modelo tem a ação preferencial do Itaú Unibanco (ITUB4) e a unit da Klabin (KLBN11) como "cobaias"; representando o mercado, o IBOVESPA. Por questão de facilidade de acesso, coletei os dados do google finance; baixei três planilhas com os históricos das cotações diárias do IBOVESPA, da KLBN11 e da ITUB4; os históricos pertencem ao período de 30/12/2014 até 31/07/2017; como preferi trabalhar com retornos ao mês, selecionei apenas as cotações do último dia útil de cada mês; então, como são 32 meses, obtive três amostras, cada uma com 31 valores do retorno ao mês (de 30/01/2015 até 31/07/2017).
Observe a Figura 1 com a estrutura da planilha; está praticamente igual ao modelo da postagem anterior, acrescentei apenas a estrutura da tabela do conjunto de oportunidades de investimento (colunas O a T). Ainda sobre a Figura 1: a soma das proporções dos ativos numa carteira deve ser sempre 1,00 (100%); resolvi trabalhar com 21 carteiras, a primeiro com 100% de KLBN11 e 0% de ITUB4; a última, 0% de KLBN11 e 100% de ITUB4; as demais, são composições obtidas com 5% de variação para as proporções (de cima para baixo, redução da participação de KLBN11 e aumento para ITUB4); você pode trabalhar com mais ou menos carteiras, vai depender da taxa de variação definidas às proporções. Lembre-se de acessar a postagem anterior para baixar o arquivo com a estrutura básica da planilha.
Figura 1
Conforme apresenta a Figura 2, insira as fórmulas para calcular à carteira 1 as variáveis risco, retorno e beta, respectivamente nas células R4, S4 e T4. Na célula R4, duas fórmulas podem ser usadas: uma com a correlação entre os ativos, sendo =RAIZ((P4*$L$4)^2+(Q4*$M$4)^2+2*P4*Q4*$L$7); ou com a covariância, =RAIZ((P4*$L$4)^2+(Q4*$M$4)^2+2*P4*Q4*$L$7); como as fórmulas das células R4, S4 e T4 devem ser arrastadas/copiadas até a última carteira, então os valores comuns a todos os cálculos precisam participar com suas células fixadas (aquelas com cifrões); os dois cifrões aparecem quando você pressiona a tecla F4 uma vez durante a edição da fórmula dentro da célula; se você pressionar F4 duas vezes, a fixação mudará para =RAIZ((P4*L$4)^2+(Q4*M$4)^2+2*P4*Q4*L$7) e =RAIZ((P4*L$4)^2+(Q4*M$4)^2+2*P4*Q4*L$7), com o cifrão entre a coluna (letra) e a linha (número) de cada célula, sem haver qualquer prejuízo aos cálculos. Em S4 e T4 devem ser congeladas as células com os valores dos retornos e dos betas dos ativos; =P4*$L$3+Q4*$M$3 para o retorno da carteira, podendo ser =P4*L$3+Q4*M$3; =P4*$L$5+Q4*$M$5 para o beta, com iguais resultados quando =P4*L$5+Q4*M$5.
Figura 2
A Figura 3 tem apenas um propósito: informar a necessidade de arrastar/copiar as fórmulas das células R4, S4 e T4 até a última carteira.
Figura 3
Após o preenchimento dos intervalos R4:R24 e S4:S24, 21 pares ordenados estarão disponíveis: X para o risco da carteira; Y, retorno da carteira. Proceda agora da forma como sugere o conteúdo da Figura 4: selecione o intervalo R4:S24; insira o gráfico de dispersão com linhas suaves para obter a primeira versão do gráfico do conjunto de oportunidades de investimento, representado pela relação desvio padrão da carteira (X) e retorno da carteira (Y).
Resolvi melhorar a aparência do gráfico alterando o padrão de cores e inserindo os títulos dos eixos horizontal (risco da carteira) e vertical (retorno da carteira), exatamente como ratifica a Figura 5. Você pode (deve) fazer a mesma coisa!
Ainda não acabou! Falta inserir a carteira de risco mínimo no gráfico. Seguindo a orientação exposta na Figura 6, insira uma nova série no gráfico, na verdade o par ordenado da carteira de risco mínimo (assunto da postagem anterior) - acesse Selecionar Dados ao clicar com o lado direito do mouse sobre o gráfico para começar a inserir os dados da carteira de risco mínimo (K14 e I14 são, respectivamente, o risco e o retorno).
Figura 6
Para fazer o par ordenado da carteira de risco mínimo aparecer na curva, com gráfico selecionado clique em Formatar (veja menu), depois selecione Série "CRM" para Formatar Seleção e inserir um marcador. Instruções demonstradas na Figura 7. CRM é a sigla para carteira de risco mínimo, obviamente.
Figura 7
Aparência final do gráfico na Figura 8.
Figura 8
Você agora deve fazer mais um gráfico, agora com a relação entre beta e retorno da carteira. Repita todos os passos demonstrado à elaboração do gráfico anterior, mas com os intervalos T4:T24 (beta, no eixo horizontal) e S4:S24 (retorno, no eixo vertical). Veja o resultado final na Figura 9.
Figura 9
Resultado final da planilha na Figura 10.
Figura 10
Pronto, trabalho encerrado!
Registre dúvidas e sugestões nos comentários.
Cordialmente,
Adail Marcos
Nenhum comentário:
Postar um comentário