Como usar o Excel para simular preços de ações

Alguns investidores ativos modelam variações de uma ação ou outro ativo para simular seu preço e o dos instrumentos que são baseados nele, como derivativos. A simulação do valor de um ativo em uma planilha do Excel pode fornecer uma representação mais intuitiva de sua avaliação para um portfólio.

Principais Takeaways

• Os traders que desejam testar um modelo ou estratégia de volta podem usar preços simulados para validar sua eficácia.
• O Excel pode ajudar no seu back-test usando uma simulação de monte carlo para gerar movimentos aleatórios de preços.
• O Excel também pode ser usado para calcular a volatilidade histórica para conectar-se aos seus modelos para maior precisão.

Criando uma simulação de modelo de preços

Independentemente de estarmos pensando em comprar ou vender um instrumento financeiro, a decisão pode ser auxiliada pelo estudo tanto numérico quanto gráfico. Esses dados podem nos ajudar a julgar a próxima jogada provável que o ativo pode fazer e as que são menos prováveis.

Primeiro de tudo, o modelo requer algumas hipóteses anteriores. Assumimos, por exemplo, que os retornos diários, ou "r (t)", desses ativos são normalmente distribuídos com a média "(μ)" e o desvio padrão sigma "(σ)". Essas são as premissas padrão que usaremos aqui, embora existam muitas outras que poderiam ser usadas para melhorar a precisão do modelo.

Que dá:

O que resulta em:

Finalmente:

E agora podemos expressar o valor do preço de fechamento de hoje usando o fechamento do dia anterior.

• Cálculo de μ:

Para calcular μ, que é a média dos retornos diários, tomamos os n preços de fechamento passados ​​sucessivos e aplicamos, que é a média da soma dos n preços passados:

• O cálculo da volatilidade σ - volatilidade

φ é uma volatilidade com média da variável aleatória zero e desvio padrão um.

Computando a volatilidade histórica no Excel

Neste exemplo, usaremos a função do Excel "= NORMSINV (RAND ())." Com base na distribuição normal, essa função calcula um número aleatório com média de zero e desvio padrão de um. Para calcular µ, basta calcular a média dos rendimentos usando a função Ln (.): A distribuição log-normal.

Na célula F4, digite "Ln (P (t) / P (t-1)"

Na pesquisa de células F19 "= MÉDIA (F3: F17)"

Na célula H20, insira “= MÉDIA (G4: G17)

Na célula H22, insira "= 365 * H20" para calcular a variação anualizada

Na célula H22, insira "= SQRT (H21)" para calcular o desvio padrão anualizado

Portanto, agora temos a "tendência" dos retornos diários passados ​​e o desvio padrão (a volatilidade). Podemos aplicar nossa fórmula encontrada acima:

Faremos uma simulação por 29 dias, portanto dt = 1/29. Nosso ponto de partida é o último preço de fechamento: 95.

• Na célula K2, digite "0".
• Na célula L2, digite "95".
• Na célula K3, digite "1".
• Na célula L3, digite "= L2 * (1 + $ F $ 19 * (1/29) + $ H $ 22 * ​​SQRT (1/29) * NORMSINV (RAND ()))".

Em seguida, arrastamos a fórmula para baixo na coluna para concluir toda a série de preços simulados.

Esse modelo nos permite encontrar uma simulação dos ativos em até 29 datas, com a mesma volatilidade dos 15 preços anteriores que selecionamos e com uma tendência semelhante.

Por fim, podemos clicar em "F9" para iniciar outra simulação, pois temos a função rand como parte do modelo.