ETFFIN Finance >> Curso de Finanças Pessoais >  >> Ações >> Negociação de Ações

Simulação de Preço de Ações no Excel: Backtesting e Valuation

Alguns investidores ativos modelam variações de uma ação ou outro ativo para simular seu preço e o dos instrumentos baseados 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 Conclusões

  • Traders que buscam testar um modelo ou estratégia (backtest) podem usar preços simulados para validar sua eficácia.
  • O Excel pode ajudar no backtesting usando uma simulação de Monte Carlo para gerar movimentos aleatórios de preço.
  • O Excel também pode ser usado para calcular volatilidade histórica para inserir em seus modelos para maior precisão.

Construindo uma Simulação de Modelo de Precificação

Seja considerando comprar ou vender um instrumento financeiro, a decisão pode ser auxiliada estudando-o tanto numerica 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.

Primeiro, o modelo requer algumas hipóteses prévias. 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 "(σ)". Estas são as suposições padrão que usaremos aqui, embora existam muitas outras que poderiam ser usadas para melhorar a precisão do modelo.

​ r ( t ) = S ( t ) − S ( t − 1 ) S ( t − 1 ) ∼ N ( μ , σ ) where: S ( t ) = close t S ( t − 1 ) = close t − 1 \begin{aligned} &r ( t ) = \frac { S ( t ) - S ( t - 1 ) }{ S ( t - 1 ) } \sim N ( \mu, \sigma ) \\ &\textbf{where:} \\ &S ( t ) = \text{close}_t \\ &S ( t - 1 ) = \text{close}_{t - 1} \\ \end{aligned} ​r(t)=S(t−1)S(t)−S(t−1)​∼N(μ,σ)where:S(t)=closet​S(t−1)=closet−1​​

O que resulta em:

​ r ( t ) = S ( t ) − S ( t − 1 ) S ( t − 1 ) = μ δ t + σ ϕ δ t where: δ t = 1   day = 1 3 6 5   of a year μ = mean ϕ ≅ N ( 0 , 1 ) σ = annualized volatility \begin{aligned} &r ( t ) = \frac { S ( t ) - S ( t - 1 ) }{ S ( t - 1 ) } = \mu \delta t + \sigma \phi \sqrt { \delta t } \\ &\textbf{where:} \\ &\delta t = 1 \ \text{day} = \frac { 1 }{ 365 } \ \text{of a year} \\ &\mu = \text{mean} \\ &\phi \cong N ( 0, 1 ) \\ &\sigma = \text{annualized volatility} \\ \end{aligned} ​r(t)=S(t−1)S(t)−S(t−1)​=μδt+σϕδt​where:δt=1 day=3651​ of a yearμ=meanϕ≅N(0,1)σ=annualized volatility​

O que resulta em:

​ S ( t ) − S ( t − 1 ) S ( t − 1 ) = μ δ t + σ ϕ δ t \begin{aligned} &\frac { S ( t ) - S ( t - 1 ) }{ S ( t - 1 ) } = \mu \delta t + \sigma \phi \sqrt { \delta t } \\ \end{aligned} ​S(t−1)S(t)−S(t−1)​=μδt+σϕδt​​

Finalmente:

​ S ( t ) − S ( t − 1 ) =   S ( t − 1 ) μ δ t + S ( t − 1 ) σ ϕ δ t S ( t ) =   S ( t − 1 ) + S ( t − 1 ) μ δ t   +   S ( t − 1 ) σ ϕ δ t S ( t ) =   S ( t − 1 ) ( 1 + μ δ t + σ ϕ δ t ) \begin{aligned} 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{aligned} S(t)−S(t−1)=S(t)=S(t)=​ S(t−1)μδt+S(t−1)σϕδt​ S(t−1)+S(t−1)μδt + S(t−1)σϕδt​ S(t−1)(1+μδt+σϕδt​)​

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

​ μ = 1 n ∑ t = 1 n r ( t ) \begin{aligned} &\mu = \frac { 1 }{ n } \sum_{ t = 1 } ^ { n } r ( t ) \\ \end{aligned} ​μ=n1​t=1∑n​r(t)​

  • O cálculo da volatilidade σ - volatilidade

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

Calculando Volatilidade Histórica no Excel

Para este exemplo, usaremos a função do Excel "= NORMSINV (ALEATÓRIO ())". Com base na distribuição normal, esta função calcula um número aleatório com média zero e desvio padrão um. Para calcular μ, simplesmente faça 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)"

Simulação de Preço de Ações no Excel: Backtesting e Valuation

Na célula F19, busque "= MÉDIA (F3:F17)"

Simulação de Preço de Ações no Excel: Backtesting e Valuation

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

Simulação de Preço de Ações no Excel: Backtesting e Valuation

Na célula H21, insira "= 365*H20" para calcular a variância anualizada

Simulação de Preço de Ações no Excel: Backtesting e Valuation

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

Simulação de Preço de Ações no Excel: Backtesting e Valuation

Agora temos a "tendência" dos retornos diários passados 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 ) σ ϕ δ t S ( t ) =   S ( t − 1 ) + S ( t − 1 ) μ δ t   +   S ( t − 1 ) σ ϕ δ t S ( t ) =   S ( t − 1 ) ( 1 + μ δ t + σ ϕ δ t ) \begin{aligned} 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{aligned} S(t)−S(t−1)=S(t)=S(t)=​ S(t−1)μδt+S(t−1)σϕδt​ S(t−1)+S(t−1)μδt + S(t−1)σϕδt​ S(t−1)(1+μδt+σϕδt​)​

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

  • Na célula K2, insira "0."
  • Na célula L2, insira "95."
  • Na célula K3, insira "1."
  • Na célula L3, insira "= L2 * (1 + $F$19 * (1/29) + $H$22 *RAIZ(1/29)*NORMSINV (ALEATÓRIO ()))."

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

Simulação de Preço de Ações no Excel: Backtesting e Valuation

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

Simulação de Preço de Ações no Excel: Backtesting e Valuation

Por fim, podemos clicar em "F9" para iniciar outra simulação, já que temos a função ALEATÓRIO como parte do modelo.