Как использовать Excel для моделирования цен на акции

Некоторые активные инвесторы моделируют вариации акций или других активов, чтобы моделировать их цену и цену инструментов, которые на них основаны, например, деривативов. Моделирование стоимости актива в электронной таблице Excel может обеспечить более интуитивное представление его оценки для портфеля.

Построение модели ценообразования

Независимо от того, рассматриваем ли мы вопрос о покупке или продаже финансового инструмента, принять решение можно, изучив его как в числовом, так и в графическом виде. Эти данные могут помочь нам судить о следующем вероятном движении, которое может сделать актив, и о шагах, которые менее вероятны.

Прежде всего, модель требует некоторых предварительных гипотез. Мы предполагаем, например, что дневная доходность или «r (t)» этих активов обычно распределяется со средним значением «(μ)» и сигмой стандартного отклонения «(σ)». Это стандартные предположения, которые мы будем использовать здесь, хотя есть много других, которые можно использовать для повышения точности модели.

Который дает:

р(т)знак равноS(т)-S(т-1)S(т-1)знак равноμδт+σϕδтжчере:δтзнак равно1 дайзнак равно1365 оф а йеарμзнак равномеанϕ≅N(0,1)σзнак равноппулягедоболтяляту \ begin {align} & r (t) = \ frac {S (t) — S (t — 1)} {S (t — 1)} = \ mu \ delta t + \ sigma \ phi \ sqrt {\ delta t } \\ & \ textbf {где:} \\ & \ delta t = 1 \ \ text {day} = \ frac {1} {365} \ \ text {года} \\ & \ mu = \ text { mean} \\ & \ phi \ cong N (0, 1) \\ & \ sigma = \ text {годовая волатильность} \\ \ end {выровнено}Взаимодействие с другими людьмиг(т)знак равно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»>

Что приводит к:

Ну наконец то:

S(т)-S(т-1)знак равно S(т-1)μδт+S(т-1)σϕδтS(т)знак равно S(т-1)+S(т-1)μδт + S(т-1)σϕδтS(т)знак равно S(т-1)(1+μδт+σϕδт)\ 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 {выровнено}S(т)-S(t-1)знак равноS(т)знак равноS(т)знак равноВзаимодействие с другими людьми 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»>

И теперь мы можем выразить значение сегодняшней цены закрытия, используя закрытие предыдущего дня.

• Вычисление μ:

Чтобы вычислить μ, то есть среднее значение дневной доходности, мы берем n последовательных прошлых цен закрытия и применяем, что является средним из суммы n прошлых цен:

• Расчет волатильности σ — волатильности

φ — волатильность со средним значением нулевой случайной величины и единицей стандартного отклонения.

Вычисление исторической волатильности в Excel

В этом примере мы будем использовать функцию Excel «= НОРМСТОБР (СЛЧИС ())». На основе нормального распределения эта функция вычисляет случайное число  со средним значением, равным нулю, и стандартным отклонением, равным единице. Чтобы вычислить μ, просто усредните урожайность с помощью функции Ln (.): Логнормального распределения.

В ячейке F4 введите «Ln (P (t) / P (t-1)».

В ячейке F19 найдите «= СРЕДНЕЕ (F3: F17)»

В ячейке H20 введите «= СРЕДНЕЕ (G4: G17).

В ячейке H22 введите «= 365 * H20», чтобы вычислить годовую дисперсию.

В ячейке H22 введите «= КОРЕНЬ (H21)», чтобы вычислить среднегодовое стандартное отклонение.

Итак, теперь у нас есть «тренд» прошлой дневной доходности и стандартное отклонение ( волатильность ). Мы можем применить нашу формулу, указанную выше:

S(т)-S(т-1)знак равно S(т-1)μδт+S(т-1)σϕδтS(т)знак равно S(т-1)+S(т-1)μδт + S(т-1)σϕδтS(т)знак равно S(т-1)(1+μδт+σϕδт)\ 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 {выровнено}S(т)-S(t-1)знак равноS(т)знак равноS(т)знак равноВзаимодействие с другими людьми 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»>

Мы будем моделировать более 29 дней, поэтому dt = 1/29. Наша отправная точка — последняя цена закрытия: 95.

• В ячейке K2 введите «0».
• В ячейке L2 введите «95».
• В ячейке K3 введите «1».
• В ячейке L3 введите «= L2 * (1 + $ F $ 19 * (1/29) + $ H $ 22 * ​​SQRT (1/29) * NORMSINV (RAND ()))».

Затем мы перетаскиваем формулу вниз по столбцу, чтобы завершить всю серию смоделированных цен.

Эта модель позволяет нам найти имитацию активов до 29 заданных дат с той же волатильностью, что и предыдущие 15 выбранных нами цен, и с аналогичной тенденцией.

Наконец, мы можем нажать «F9», чтобы начать другое моделирование, поскольку у нас есть функция rand как часть модели.