23 Junho 2021 7:17

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 se baseiam nele, como derivativos. Simular o 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 vantagens

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

Construindo uma Simulação de Modelo de Preços

Estejamos considerando comprar ou vender um instrumento financeiro, a decisão pode ser auxiliada estudando-o tanto numérica quanto graficamente. Esses dados podem nos ajudar a julgar o próximo movimento provável que o ativo pode fazer e os movimentos que são menos prováveis.

Em primeiro lugar, 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 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á:

r(t)=S(t)-S(t-1)S(t-1)=µδt+σϕδtwHeRe:δt=1 day=1365 of a yearµ=eueumnϕ≅N(0,1)σ=umnnuumlized volumtility\ begin {alinhado} & r (t) = \ frac {S (t) – S (t – 1)} {S (t – 1)} = \ mu \ delta t + \ sigma \ phi \ sqrt {\ delta t } \\ & \ textbf {onde:} \\ & \ delta t = 1 \ \ text {day} = \ frac {1} {365} \ \ text {de um ano} \\ & \ mu = \ text { média} \\ & \ phi \ cong N (0, 1) \\ & \ sigma = \ text {volatilidade anual} \\ \ end {alinhado}​r(t)=S(t-1)

-10,-9.5,-14c0,-2,0.3,-3.3,1,-4c1.3,-2.7,23.83,-20.7,67.5,-54c44.2,-33.3,65.8,
-50.3,66.5,-51c1.3,-1.3,3,-2,5,-2c4.7,0,8.7,3.3,12,10s173,378,173,378c0.7,0,
35.3,-71,104,-213c68.7,-142,137.5,-285,206.5,-429c69,-144,104.5,-217.7,106.5,
-221c5.3,-9.3,12,-14,20,-14H400000v40H845.2724s-225.272,467,-225.272,467
s-235,486,-235,486c-2.7,4.7,-9,7,-19,7c-6,0,-10,-1,-12,-3s-194,-422,-194,-422
s-65,47,-65,47z M834 80H400000v40H845z”>

O que resulta em:

Finalmente:

S(t)-S(t-1)= S(t-1)µδt+S(t-1)σϕδtS(t)= S(t-1)+S(t-1)µδt + S(t-1)σϕδtS(t)= S(t-1)(1+µδt+σϕδt)\ begin {alinhado} S (t) – S (t – 1) = & \ S (t – 1) \ mu \ delta t + S (t – 1) \ sigma \ phi \ sqrt {\ delta t} \\ S (t) = & \ S (t – 1) + S (t – 1) \ mu \ delta t \ + \\ & \ S (t – 1) \ sigma \ phi \ sqrt {\ delta t} \\ S (t) = & \ S (t – 1) (1 + \ mu \ delta t + \ sigma \ phi \ sqrt {\ delta t}) \\ \ end {alinhado}S(t)-S(t-1)=S(t)=S(t)=​ S(t-1)μδt+S(t-1)σϕδt
-10,-9.5,-14c0,-2,0.3,-3.3,1,-4c1.3,-2.7,23.83,-20.7,67.5,-54c44.2,-33.3,65.8,
-50.3,66.5,-51c1.3,-1.3,3,-2,5,-2c4.7,0,8.7,3.3,12,10s173,378,173,378c0.7,0,
35.3,-71,104,-213c68.7,-142,137.5,-285,206.5,-429c69,-144,104.5,-217.7,106.5,
-221c5.3,-9.3,12,-14,20,-14H400000v40H845.2724s-225.272,467,-225.272,467
s-235,486,-235,486c-2.7,4.7,-9,7,-19,7c-6,0,-10,-1,-12,-3s-194,-422,-194,-422
s-65,47,-65,47z M834 80H400000v40H845z”>
-10,-9.5,-14c0,-2,0.3,-3.3,1,-4c1.3,-2.7,23.83,-20.7,67.5,-54c44.2,-33.3,65.8,
-50.3,66.5,-51c1.3,-1.3,3,-2,5,-2c4.7,0,8.7,3.3,12,10s173,378,173,378c0.7,0,
35.3,-71,104,-213c68.7,-142,137.5,-285,206.5,-429c69,-144,104.5,-217.7,106.5,
-221c5.3,-9.3,12,-14,20,-14H400000v40H845.2724s-225.272,467,-225.272,467
s-235,486,-235,486c-2.7,4.7,-9,7,-19,7c-6,0,-10,-1,-12,-3s-194,-422,-194,-422
s-65,47,-65,47z M834 80H400000v40H845z”>

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, pegamos os n preços de fechamento anteriores sucessivos e aplicamos, que é a média da soma dos n preços anteriores:

  • O cálculo da volatilidade σ – volatilidade

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

Como calcular a volatilidade histórica no Excel

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

Na célula F4, insira “Ln (P (t) / P (t-1)”

Na pesquisa de células F19 “= AVERAGE (F3: F17)”

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

 Na célula H22, digite “= 365 * H20” para calcular a variação anualizada

 Na célula H22, digite “= SQRT (H21)” para calcular o desvio padrão anual

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

S(t)-S(t-1)= S(t-1)µδt+S(t-1)σϕδtS(t)= S(t-1)+S(t-1)µδt + S(t-1)σϕδtS(t)= S(t-1)(1+µδt+σϕδt)\ begin {alinhado} S (t) – S (t – 1) = & \ S (t – 1) \ mu \ delta t + S (t – 1) \ sigma \ phi \ sqrt {\ delta t} \\ S (t) = & \ S (t – 1) + S (t – 1) \ mu \ delta t \ + \\ & \ S (t – 1) \ sigma \ phi \ sqrt {\ delta t} \\ S (t) = & \ S (t – 1) (1 + \ mu \ delta t + \ sigma \ phi \ sqrt {\ delta t}) \\ \ end {alinhado}S(t)-S(t-1)=S(t)=S(t)=​ S(t-1)μδt+S(t-1)σϕδt
-10,-9.5,-14c0,-2,0.3,-3.3,1,-4c1.3,-2.7,23.83,-20.7,67.5,-54c44.2,-33.3,65.8,
-50.3,66.5,-51c1.3,-1.3,3,-2,5,-2c4.7,0,8.7,3.3,12,10s173,378,173,378c0.7,0,
35.3,-71,104,-213c68.7,-142,137.5,-285,206.5,-429c69,-144,104.5,-217.7,106.5,
-221c5.3,-9.3,12,-14,20,-14H400000v40H845.2724s-225.272,467,-225.272,467
s-235,486,-235,486c-2.7,4.7,-9,7,-19,7c-6,0,-10,-1,-12,-3s-194,-422,-194,-422
s-65,47,-65,47z M834 80H400000v40H845z”>
-10,-9.5,-14c0,-2,0.3,-3.3,1,-4c1.3,-2.7,23.83,-20.7,67.5,-54c44.2,-33.3,65.8,
-50.3,66.5,-51c1.3,-1.3,3,-2,5,-2c4.7,0,8.7,3.3,12,10s173,378,173,378c0.7,0,
35.3,-71,104,-213c68.7,-142,137.5,-285,206.5,-429c69,-144,104.5,-217.7,106.5,
-221c5.3,-9.3,12,-14,20,-14H400000v40H845.2724s-225.272,467,-225.272,467
s-235,486,-235,486c-2.7,4.7,-9,7,-19,7c-6,0,-10,-1,-12,-3s-194,-422,-194,-422
s-65,47,-65,47z M834 80H400000v40H845z”>

Faremos uma simulação em 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) * INV. NORMP (RAND ())).”

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

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

Por último, podemos clicar em “F9” para iniciar outra simulação, pois temos a função rand como parte do modelo.