Cómo usar Excel para simular precios de acciones

Algunos inversores activos modelan variaciones de una acción u otro activo para simular su precio y el de los instrumentos que se basan en él, como los derivados. Simular el valor de un activo en una hoja de cálculo de Excel puede proporcionar una representación más intuitiva de su valoración para una cartera.

Para llevar clave

• Los operadores que buscan realizar una prueba inversa de un modelo o estrategia pueden usar precios simulados para validar su efectividad.
• Excel puede ayudarlo con su back-testing utilizando una simulación monte carlo para generar movimientos aleatorios de precios.
• Excel también se puede utilizar para calcular la volatilidad histórica para conectarse a sus modelos para una mayor precisión.

Construyendo una simulación de modelo de precios

Ya sea que estemos considerando comprar o vender un instrumento financiero, la decisión puede ser ayudada estudiándola numérica y gráficamente. Estos datos pueden ayudarnos a juzgar el próximo movimiento probable que podría hacer el activo y los movimientos que son menos probables.

En primer lugar, el modelo requiere algunas hipótesis previas. Suponemos, por ejemplo, que los rendimientos diarios, o "r (t)", de estos activos se distribuyen normalmente con la media, "(μ)" y la desviación estándar sigma, "(σ)". Estas son las suposiciones estándar que usaremos aquí, aunque hay muchas otras que podrían usarse para mejorar la precisión del modelo.

Lo que da:

Lo que resulta en:

Finalmente:

Y ahora podemos expresar el valor del precio de cierre de hoy usando el cierre del día anterior.

• Cálculo de μ:

Para calcular μ, que es la media de los rendimientos diarios, tomamos los n precios de cierre pasados ​​sucesivos y aplicamos, que es el promedio de la suma de los n precios pasados:

• El cálculo de la volatilidad σ - volatilidad

φ es una volatilidad con un promedio de variable aleatoria cero y desviación estándar uno.

Cálculo de la volatilidad histórica en Excel

Para este ejemplo, utilizaremos la función de Excel "= NORMSINV (RAND ())". Con una base de la distribución normal, esta función calcula un número aleatorio con una media de cero y una desviación estándar de uno. Para calcular μ, simplemente promedie los rendimientos usando la función Ln (.): La distribución log-normal.

En la celda F4, ingrese "Ln (P (t) / P (t-1)"

En la búsqueda de celda F19 "= PROMEDIO (F3: F17)"

En la celda H20, ingrese “= PROMEDIO (G4: G17)

En la celda H22, ingrese "= 365 * H20" para calcular la varianza anualizada

En la celda H22, ingrese "= SQRT (H21)" para calcular la desviación estándar anualizada

Así que ahora tenemos la "tendencia" de los rendimientos diarios pasados ​​y la desviación estándar (la volatilidad). Podemos aplicar nuestra fórmula que se encuentra arriba:

Haremos una simulación durante 29 días, por lo tanto, dt = 1/29. Nuestro punto de partida es el último precio de cierre: 95.

• En la celda K2, ingrese "0".
• En la celda L2, ingrese "95".
• En la celda K3, ingrese "1."
• En la celda L3, ingrese "= L2 * (1 + $ F $ 19 * (1/29) + $ H $ 22 * ​​SQRT (1/29) * NORMSINV (RAND ()))".

A continuación, arrastramos la fórmula hacia abajo en la columna para completar la serie completa de precios simulados.

Este modelo nos permite encontrar una simulación de los activos hasta 29 fechas dadas, con la misma volatilidad que los 15 precios anteriores que seleccionamos y con una tendencia similar.

Por último, podemos hacer clic en "F9" para iniciar otra simulación ya que tenemos la función rand como parte del modelo.