Jak używać programu Excel do symulowania cen akcji

Niektórzy aktywni inwestorzy modelują zmiany akcji lub innych aktywów, aby zasymulować ich cenę oraz instrumenty na nich oparte, takie jak instrumenty pochodne. Symulacja wartości aktywów w arkuszu kalkulacyjnym Excel może zapewnić bardziej intuicyjną reprezentację jego wyceny dla portfela.

Tworzenie symulacji modelu cenowego

Niezależnie od tego, czy rozważamy zakup, czy sprzedaż instrumentu finansowego, w podjęciu decyzji można pomóc, analizując go zarówno numerycznie, jak i graficznie. Te dane mogą pomóc nam ocenić następny prawdopodobny ruch, który może wykonać składnik aktywów, i ruchy, które są mniej prawdopodobne.

Przede wszystkim model wymaga pewnych wcześniejszych hipotez. Zakładamy na przykład, że dzienne zwroty lub „r (t)” tych aktywów rozkładają się normalnie ze średnią „(μ)” i sigma odchylenia standardowego „(σ)”. Są to standardowe założenia, których będziemy tutaj używać, chociaż istnieje wiele innych, które można wykorzystać do poprawy dokładności modelu.

Co daje:

r(t)=S(t)-S(t-1)S(t-1)=μδt+σϕδtWhere:δt=1 day=1365 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 {gdzie:} \\ & \ delta t = 1 \ \ text {dzień} = \ frac {1} {365} \ \ text {of a year} \\ & \ mu = \ text { mean} \\ & \ phi \ cong N (0, 1) \\ & \ sigma = \ text {zmienność roczna} \\ \ end {aligned}Wcześniejszer(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”>

Co skutkuje w:

Wreszcie:

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 {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)=Wcześniejsze 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”>

A teraz możemy wyrazić wartość dzisiejszej ceny zamknięcia, używając zamknięcia z poprzedniego dnia.

• Obliczenie μ:

Aby obliczyć μ, które jest średnią dziennych zwrotów, bierzemy n kolejnych przeszłych cen zamknięcia i stosujemy, które jest średnią z sumy n przeszłych cen:

• Obliczenie zmienności σ – zmienność

φ jest zmiennością o średniej zmiennej losowej zero i odchyleniu standardowym.

Obliczanie zmienności historycznej w programie Excel

W tym przykładzie użyjemy funkcji programu Excel „= NORMSINV (RAND ())”. Na podstawie rozkładu normalnego funkcja ta oblicza liczbę losową  o średniej równej zero i odchyleniu standardowym wynoszącym jeden. Aby obliczyć μ, po prostu uśrednij plony za pomocą funkcji Ln (.): Rozkład logarytmiczno-normalny.

W komórce F4 wpisz „Ln (P (t) / P (t-1)”

W wyszukiwaniu komórek F19 „= ŚREDNIA (F3: F17)”

W komórce H20 wpisz „= ŚREDNIA (G4: G17)

W komórce H22 wprowadź „= 365 * H20”, aby obliczyć roczną wariancję

W komórce H22 wprowadź „= SQRT (H21)”, aby obliczyć roczne odchylenie standardowe

Mamy więc teraz „trend” przeszłych dziennych zwrotów i odchylenie standardowe ( zmienność ). Możemy zastosować powyższą formułę:

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 {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)=Wcześniejsze 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”>

Symulację wykonamy przez 29 dni, dlatego dt = 1/29. Punktem wyjścia jest ostatnia cena zamknięcia: 95.

• W komórce K2 wpisz „0”.
• W komórce L2 wpisz „95”.
• W komórce K3 wpisz „1”.
• W komórce L3 wpisz „= L2 * (1 + $ F $ 19 * (1/29) + $ H $ 22 * ​​SQRT (1/29) * NORMSINV (RAND ()))”.

Następnie przeciągamy formułę w dół kolumny, aby zakończyć całą serię symulowanych cen.

Model ten pozwala nam znaleźć symulację aktywów do 29 podanych dat, z taką samą zmiennością jak poprzednie 15 wybranych przez nas cen i z podobnym trendem.

Na koniec możemy kliknąć „F9”, aby rozpocząć kolejną symulację, ponieważ mamy funkcję rand jako część modelu.