Моделирование доходности акций в Excel. Практический пример

Характер изменения прошлых доходностей описывает функция распределения доходностей. Будем полагать, что в будущем изменение доходности ценной бумаги будет подчиняться этому распределению. Для проверки гипотезы о законе распределения доходностей используют два критерия: Хи-квадрат (критерий Пирсона) и критерий Колмогорова – Смирнова, которые показывают, соответствует ли распределение прошлых доходностей выбранному нами закону.

После определения закона, которому подчиняется распределение доходностей, мы смоделируем дальнейшую динамику курса акции.
Данные по динамике курса акций можно взять, например, с сайта finam.ru. Для примера экспортируем данные по акции Сургутнефтегаз (SNGS) за  год. Определим закон, по которому распределены доходности этой акции. Для того, что бы ускорить процесс проверки распределения доходностей акции, воспользуемся статистическим пакетом STATISTICA (Эту операцию можно проделать и в Excel см. выше). В итоге мы рассчитываем критерии Колмогорова–Смирнова и Хи-квадрат, которые показывают принадлежность распределения доходностей к гипотетическому распределению. В нашем случае была предложена гипотеза, что доходности акции Сургутнефтегаза распределены по Логнормальному закону распределения. Критерий Колмогорова – Смирнова равен 0.029, он находится в зоне не значимости, следовательно, распределение доходностей акции SNGS подчиняются логнормальному закону распределения. Критерий Хи-квадрат равен 4.17, а вероятность соответствия предложенного закона эмпирическому закону равна 84%, что довольно высоко. Отсюда можно сделать вывод, что распределение доходности акций Сургутнефтегаза последний год подчинялось логнормальному закону. Зная этот факт, смоделируем будущую динамику изменения курса акции.

Для моделирования динамику курса воспользуемся Excel. Первым делом экспортируем котировки акции и рассчитаем дневные доходности. Доходность рассчитывается как:
=Ln(A3/A2)

Если обратиться к статистике,  курс следующего дня будет рассчитываться по формуле:

S_t – курс акции в момент времени t;
S_t+∆t – значение курса в следующий момент времени с ∆t смещением (∆t=1 для одного дня);
μ – математическое ожидание доходности акции;
σ – стандартное отклонение акции;
Z  – нормально распределенная случайная величина N(0,1), то есть с математическим ожиданием 0 и стандартным отклонением 1.

Значение курса в следующий день будет рассчитываться как:

Где:
S₀ – последнее значение курса.

Рассчитаем значения стандартного отклонения и математического ожидания доходностей акции по формулам:
=СТАНДОТКЛОН(B:B)
=СРЗНАЧ(B:B)

Смоделируем значения будущего курса акции на 100 дней вперед. Для этого в колонке «Время» отложим значения от 0 до 100. В колонке «Норм_откл(Z)» случайным образом сгенерируем значения нормально распределенной случайной величины. В колонке «Курс» будем выводить полученные моделированием значения.

Для того что бы сгенерировать 100 случайных чисел воспользуемся надстройкой «Анализ данных». Перейдем из главного меню в подраздел Данные→Анализ данных→Генерация случайных чисел. Поставим число случайных величин 100, распределение выберем нормальное с параметрами среднего 0 и стандартного отклонения.

Следующим этапом рассчитаем в Excel значения курса по формуле указанной выше:
=H2*EXP($E$4*1+$E$3*G3*КОРЕНЬ(1))
В итоге мы получим возможный график будущего курса акции Сургутнефтегаз (SNGS).

Можно несколько раз смоделировать различные значения курса акции. На рисунке ниже показано три модели динамики акции.

Понимание закона распределения будущих доходностей позволяет смоделировать будущее поведение акции и инвестиционного портфеля. Это позволит грамотно оценивать будущие риски и принимать эффективные решения.