Comment utiliser Excel pour simuler les cours des actions

Table des matières

Développer

• Construire une simulation de prix
• Calcul de la volatilité historique

Certains investisseurs actifs modélisent les variations d’une action ou d’un autre actif pour simuler son prix et celui des instruments qui en dépendent, tels que les dérivés. La simulation de la valeur d’un actif sur une feuille de calcul Excel peut fournir une représentation plus intuitive de sa valorisation pour un portefeuille.

Création d’une simulation de modèle de tarification

Que l’on envisage d’acheter ou de vendre un instrument financier, la décision peut être facilitée en l’étudiant à la fois numériquement et graphiquement. Ces données peuvent nous aider à juger du prochain mouvement probable que l’actif pourrait effectuer et des mouvements moins probables.

Tout d’abord, le modèle nécessite quelques hypothèses préalables. Nous supposons, par exemple, que les rendements quotidiens, ou «r (t)», de ces actifs sont normalement distribués avec la moyenne «(μ)» et l’ écart type sigma, «(σ)». Ce sont les hypothèses standard que nous utiliserons ici, bien qu’il y en ait beaucoup d’autres qui pourraient être utilisées pour améliorer la précision du modèle.

Qui donne:

r(t)=S(t)-S(t-1)S(t-1)=μδt+σϕδtwhere:δt=1 jay=1365 of a yearμ=meunnϕ≅N(0,1)σ=annualized volatility\ begin {aligné} & r (t) = \ frac {S (t) – S (t – 1)} {S (t – 1)} = \ mu \ delta t + \ sigma \ phi \ sqrt {\ delta t } \\ & \ textbf {où:} \\ & \ delta t = 1 \ \ text {jour} = \ frac {1} {365} \ \ text {d’une année} \\ & \ mu = \ text { signifie} \\ & \ phi \ cong N (0, 1) \\ & \ sigma = \ text {volatilité annualisée} \\ \ end {aligné}​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 »>

Ce qui se traduit par:

Pour terminer:

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 {aligné} 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 {aligné}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 »>

Et maintenant, nous pouvons exprimer la valeur du cours de clôture d’aujourd’hui en utilisant la clôture de la veille.

• Calcul de μ:

Pour calculer μ, qui est la moyenne des rendements quotidiens, nous prenons les n cours de clôture passés successifs et appliquons, qui est la moyenne de la somme des n prix passés:

• Le calcul de la volatilité σ – volatilité

φ est une volatilité avec une moyenne de la variable aléatoire zéro et de l’écart type un.

Calcul de la volatilité historique dans Excel

Pour cet exemple, nous utiliserons la fonction Excel « = NORMSINV (RAND ()) ». Avec une base de la distribution normale, cette fonction calcule un nombre aléatoire  avec une moyenne de zéro et un écart type de un. Pour calculer μ, il suffit de faire la moyenne des rendements en utilisant la fonction Ln (.): La distribution log-normale.

Dans la cellule F4, entrez « Ln (P (t) / P (t-1) »

Dans la recherche de cellule F19 « = MOYENNE (F3: F17) »

Dans la cellule H20, entrez « = MOYENNE (G4: G17)

Dans la cellule H22, entrez « = 365 * H20 » pour calculer la variance annualisée

Dans la cellule H22, saisissez « = SQRT (H21) » pour calculer l’écart-type annualisé

Nous avons donc maintenant la «tendance» des rendements quotidiens passés et l’écart type (la volatilité ). Nous pouvons appliquer notre formule ci-dessus:

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 {aligné} 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 {aligné}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 »>

Nous allons faire une simulation sur 29 jours, donc dt = 1/29. Notre point de départ est le dernier cours de clôture: 95.

• Dans la cellule K2, entrez «0».
• Dans la cellule L2, entrez «95».
• Dans la cellule K3, entrez «1».
• Dans la cellule L3, entrez « = L2 * (1 + $ F $ 19 * (1/29) + $ H $ 22 * ​​SQRT (1/29) * NORMSINV (RAND ())). »

Ensuite, nous faisons glisser la formule vers le bas de la colonne pour terminer la série entière de prix simulés.

Ce modèle nous permet de trouver une simulation des actifs jusqu’à 29 dates données, avec la même volatilité que les 15 anciens prix que nous avons sélectionnés et avec une tendance similaire.

Enfin, nous pouvons cliquer sur « F9 » pour démarrer une autre simulation puisque nous avons la fonction rand dans le cadre du modèle.