Cómo utilizar Excel para simular los precios de las acciones
Tabla de contenido
Expandir
- Creación de una simulación de precios
- Computación de la volatilidad histórica
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.
Conclusiones clave
- Los operadores que buscan realizar una prueba retrospectiva de un modelo o estrategia pueden usar precios simulados para validar su efectividad.
- Excel puede ayudarlo con su back-testing usando una simulación de monte carlo para generar movimientos de precios aleatorios.
- Excel también se puede utilizar para calcular la volatilidad histórica y conectarla a sus modelos para una mayor precisión.
Creación de una simulación de modelo de precios
Ya sea que estemos considerando comprar o vender un instrumento financiero, la decisión se puede ayudar estudiándolo tanto numérica como gráficamente. Estos datos pueden ayudarnos a juzgar el próximo movimiento probable que podría realizar 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, «(σ)». Estos son los supuestos estándar que usaremos aquí, aunque hay muchos otros que podrían usarse para mejorar la precisión del modelo.
Lo que da:
r(t)=S(t)-S(t-1)S(t-1)=μδt+σϕδtwhere:δt=1 dunay=1365 def a yearμ=meanϕ≅norte(0,1)σ=unnnuunlized volatility\ begin {alineado} & r (t) = \ frac {S (t) – S (t – 1)} {S (t – 1)} = \ mu \ delta t + \ sigma \ phi \ sqrt {\ delta t } \\ & \ textbf {donde:} \\ & \ delta t = 1 \ \ text {día} = \ frac {1} {365} \ \ text {de un año} \\ & \ mu = \ text { mean} \\ & \ phi \ cong N (0, 1) \\ & \ sigma = \ text {volatilidad anualizada} \\ \ end {alineado}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»>
Lo que resulta en:
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 {alineado} 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 {alineado}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»>
Y ahora podemos expresar el valor del precio de cierre de hoy utilizando 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.
Calcular la volatilidad histórica en Excel
Para este ejemplo, usaremos la función de Excel «= NORMSINV (RAND ())». Con base en 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 celda F19, busque «= 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:
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 {alineado} 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 {alineado}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»>
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 de la columna para completar toda la serie de precios simulados.
Este modelo nos permite encontrar una simulación de los activos hasta las 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.