Как посчитать волатильность в excel
Talkin go money
Как создать график инвестиционного портфеля (Декабрь 2021).
Table of Contents:
Чтобы вычислить волатильность данной безопасности в Microsoft Excel, сначала определите временной интервал, для которого будет вычисляться метрика. Для этого примера используется 10-дневный период. Затем введите все цены закрытия акций за этот период в ячейки B2 до B12 в последовательном порядке, с последней ценой внизу. Обратите внимание, что вам понадобятся данные в течение 11 дней для расчета доходности за 10-дневный период.
Волатильность по своей сути связана со стандартным отклонением или степенью, в которой цены отличаются от их среднего значения. В ячейке C13 введите формулу «= STDV (C3: C12)», чтобы вычислить стандартное отклонение за период.
Как упоминалось выше, волатильность и отклонения тесно связаны. Это видно из тех технических показателей, которые инвесторы используют для определения волатильности акций, таких как полосы Боллинджера, которые основаны на стандартном отклонении запаса и простой скользящей средней (SMA). Тем не менее, историческая волатильность представляет собой годовую цифру, поэтому для пересчета среднеквадратичного отклонения, вычисленного выше, в полезную метрику, она должна быть умножена на коэффициент годовой оценки на основе используемого периода. Годовой коэффициент является квадратным корнем, однако сколько-нибудь периодов существует через год.
В приведенной ниже таблице показана волатильность для McDonald’s в течение 10-дневного периода:
В приведенном выше примере использовались ежедневные цены закрытия, а в среднем 252 торговых дня в год. Поэтому в ячейке C14 введите формулу «= SQRT (252) * C13», чтобы преобразовать стандартное отклонение за этот 10-дневный период в годовую историческую волатильность.
Как вы можете рассчитать корреляцию с помощью Excel?
Узнать, как вычислить коэффициент корреляции Пирсона между двумя массивами данных в Microsoft Excel с помощью функции CORREL.
Как вы можете рассчитать значение риска (VaR) в Excel?
Узнать, какое значение имеет риск, что указывает на портфель и как рассчитать стоимость риска портфеля с использованием Microsoft Excel.
Как вы можете рассчитать уменьшающуюся предельную доходность в Excel?
Узнать больше о производственных издержках и применить закон уменьшения предельных доходностей с помощью Excel. Узнайте больше о том, как эти расчеты приносят пользу бизнесу.
Волатильность в Excel
Я получил вопрос от читателя, который спросил: «Можно ли рассчитать волатильность в Excel?» Ответ: Да, можно. Однако, есть несколько вещей, о которых вы должны знать. Не особо углубляясь в детали, скажу лишь, что есть много способов рассчитать волатильность. Два из наиболее распространенных способа касаются подразумеваемой и исторической (или статистической) волатильности. Историческая довольно-таки проста для расчета в Excel, и я покажу вам, как это делается в этом посте. Расчет подразумеваемой на порядок сложнее, и хотя вы можете посчитать её в Excel, но эту тему оставим на следующий раз, потому как она касается опционов, а это не простая тема.
Сегодня же давайте просто посмотрим, как рассчитать простую историческую волатильность в Excel.
1. Соберите свои исходные данные в виде цены закрытия для каждого периода времени. Хотелось бы взять данные с сайта Московской биржи, но им похоже жалко, поэтому данная информация доступна только платным подписчикам. Что ж, на других рынках немного иначе, поэтому там получить данные проще. Возьмём к примеру информацию по Лукойлу с сайта Yahoo Finance за январь: https://finance.yahoo.com/quote/LKOD.IL/history?period1=1514754000&period2=1518469200&interval=1d&filter=history&frequency=1d (справа ссылка Download Data, ниже синей кнопки Apply). Открыв скачанный файл, мы увидем примерно следующее:
Данные в столбцах open, high, low, adj close, volume нам не нужны. Можете или скрыть столбцы или удалить их вовсе. Т.е. нас интересуют только колонки Date и Close. Немного навёл красоты:
2. Данные собраны, теперь можно посчитать изменчивость каждого дня, т.е. насколько изменилась цена сегодня по отношению ко вчерашней. Делается это просто: данные дня делим на данные предыдущего, вычитаем единицу и преобразовываем формат ячейки в процентный. Аналогичную процедуру проделываем для всех строк. Т.е. вначале вот так:
А затем это проделываем для каждой строки и получается вот так:
И преобразуем в процентный вид:
3. Теперь нам нужно посчитать стандартное отклонение. Если коротко, то это то, насколько что-то отклоняется от нормы. Ну примерно, если говорить простыми словами. Т.е. насколько сегодняшняя цена отличается от цен за какой-то период. Чтобы это посчитать, нужно воспользоваться соответсвующей формулой в Excel, называющейся STDEV или СТАНДОТКЛОН.В.
Как мы видим, здесь расчёт выбран за 10 дней, но это сделано только для иллюстрации. Вы можете выбрать любой период.
4. В заключении, нам нужно сделать одно — узнать, какова же историческая волатильность, а она — это по сути ежегодное стандартное отклонение. Для этого, мы возьмём волатильность за неделю, т.е. 5 дней, когда открыты рынки. Затем, умножим на корень из 52. Почему 52? Потому что в году 52 торговые недели. Таким образом получается:
Talkin go money
Алексей Каленкович, Как правильно считать историческую волатильность? (Декабрь 2021).
Table of Contents:
Стоимость финансовых активов меняется ежедневно. Инвесторам нужен индикатор для количественной оценки этих ходов, которые часто трудно предсказать. Спрос и предложение являются двумя основными факторами, влияющими на изменения цен на активы. В свою очередь, ценовые движения отражают амплитуду колебаний, которые являются причинами пропорциональных прибылей и убытков. С точки зрения инвестора неопределенность, связанная с такими влияниями и колебаниями, называется риском.
Цена опциона зависит от его способности двигаться или нет, или, другими словами, его способности быть неустойчивым. Чем вероятнее двигаться, тем дороже его премия будет ближе к истечению срока. Таким образом, вычисление того, как изменчивый базовый актив хорош для понимания того, как оценивать производные от этого актива.
Одним из способов измерения вариации актива будет количественное определение ежедневных доходов (процентное изменение на ежедневной основе) актива. Это подводит нас к определению и обсуждению концепции исторической волатильности.
r (t) = ln (P (t) / P (t-1)) с Ln (x) = натуральная логарифмическая функция.
Общий доход R в момент времени t равен:
R = r1 + r2 + r3 + 2 + … + rt-1 + rt, что эквивалентно:
R = Ln (P1 / P0) + … Ln (Pt-1 / Pt-2) + Ln (Pt / Pt-1)
Мы имеем следующее равенство:
Ln (a) + Ln (b) = Ln (a * b) > Итак, это дает:
R = Ln [(P1 / P0 * (P2 / P1) * … (Pt / Pt-1]
R = Ln [(P1. P2 … Pt-1. Pt) / (P0. P1. P2 … Pt-2. Pt-1)]
И после упрощения получаем R = Ln (Pt / P0).
Вывод обычно вычисляется как разница относительных изменений цен Это означает, что если у актива есть цена P (t) в момент времени t и P (t + h) в момент времени t + h> t, r возвращается:
Таким образом, можно также вычислить общий доход R, используя только начальные и конечные цены.
Чтобы в полной мере оценить разницу волатильности в течение года, мы умножаем эту волатильность, полученную выше, фактором, который учитывает изменчивость активов в течение одного года.
Чтобы вычислить квадратное число отклонений от среднего значения дневной доходности за 365 дней, умножим дисперсию на количество дней (365). Годовое стандартное отклонение определяется путем принятия квадратного корня из результата:
Для годовой дисперсии, если предположить, что год составляет 365 дней, и каждый день имеет ту же самую ежедневную дисперсию. Каждый раз мы получаем:
Годовой разброс = 365. σ²daily
Наконец, поскольку волатильность определяется как квадратный корень дисперсии:
Волатильность = √ (дисперсия в годовом выражении)
Волатильность = √ (365. Σ²daily)
Волатильность = √ (365 [Σ ( )
Мы моделируем из функции Excel =
цена акций, которая меняется ежедневно между 94 и 104. Результат: ■ Вычисление ежедневных возвратов
В столбце E мы вводим «Ln (P (t) / P (t-1))».
■ Вычисление Квадрат ежедневных возвратов
В столбце G мы вводим «(Ln (P (t) / P (t-1)) ^ 2.»
■ Вычисление Daily Variance
— В ячейке F25 мы получаем «= sum (F6: F19)».
Вычисление ежедневного стандартного отклонения
Чтобы вычислить стандартное отклонение на ежедневной основе, нам нужно вычислить квадратный корень ежедневной дисперсии. Итак: — В ячейке F28 вычисляется «= Квадрат. Корень (F26).»
— В ячейке G29 F28 отображается в процентах.
■ Вычисление годовой дисперсии
Для вычисления годовой дисперсии от ежедневной дисперсии предполагается, что каждый день имеет одинаковую дисперсию, и мы умножаем ежедневную дисперсию на 365 с включенными выходными днями. Итак:
— В ячейке F30 мы имеем «= F26 * 365».
■ Вычисление стандартного отклонения в год
— В ячейке F32 мы получаем «= ROOT (F30)».
— В ячейке G33 F32 отображается в процентах.
Этот квадратный корень из годовой дисперсии дает нам историческую волатильность.
С использованием исторической волатильности для оценки будущего риска
Используйте эти расчеты, чтобы выявить риск, связанный с вашими инвестициями.
Вычисление значения базовых точек в Excel
Обсуждение базовых точек, а также расчеты базовой точки с использованием Excel.
Подразумеваемые против исторической волатильности: основные различия
Обнаруживает различия между исторической и подразумеваемой волатильностью и как эти два показателя могут определить, имеют ли опционы продавцы или покупатели преимущество.
Стоимость опциона в Excel
В начале 70-х годов прошлого века экономисты Фишер Блэк, Майрон Шоулз и Роберт Мертон вывели формулу ценообразования опционов Блэка–Шоулза, которая позволяет получить оценку европейских колл- и пут-опционов. За свою работу Шоулз и Мертон были удостоены Нобелевской премии по экономике в 1997 г. (Блэк умер до 1997 г., а Нобелевская премия не присуждается посмертно.) Их научный труд произвел революцию в области корпоративных финансов. В данной заметке приведены основные положения этой важной научной работы, а также показано, как рассчитать стоимость опциона с помощью Excel.[1]
Рис. 1. Стоимость колл-опциона
Скачать заметку в формате Word или pdf, примеры в формате Excel
Основные сведения об опционах
Колл-опцион дает владельцу опциона право купить акцию (или иной актив, например, валюту) по цене исполнения. Пут-опцион дает владельцу опциона право продать акцию по цене исполнения.
Американский опцион может быть исполнен не позднее даты, известной как дата исполнения (часто называемой датой истечения срока действия опциона). Европейский опцион может быть исполнен только в последний день срока его действия.
Давайте рассмотрим итоги сделки по приобретению шестимесячного европейского колл-опциона на акции IBM с ценой исполнения 110 долларов. Пусть P — это курс акции IBM через шесть месяцев. Выигрыш от колл-опциона на эти акции составит 0 долларов, если P 110. Если значение P больше 110 долларов, владелец может исполнить опцион, купив акцию за 110 долларов и немедленно продав ее на спотовом рынке за P долларов, и тем самым получить прибыль (P – 110) долларов. На рис. 1 представлен выигрыш по этому колл-опциону. Выигрыш можно записать как формулу в Excel =МАКС(0;Р-110). Если Р ≤ 110, говорят опцион не в деньгах.
Ситуация с пут-опциона противоположна (рис. 2). Выигрыш по пут-опциону составит 0 долларов, если P > 110, и (110 – Р) долларов, если P Анализ «что-если»), показанном на рис. 8, определите волатильность (значение в ячейке B10), при которой цена опциона (формула в С13) достигает значения 1,85 долларов.
Как показано на рис. 7, этот опцион подразумевает годовую волатильность для Cisco в размере 34%.
Рис. 8. Настройки в диалоговом окне Подбор параметра
На сайте http://www.ivolatility.com/ и http://www.ivolatility.ru/ предоставляются оценки волатильности любой акции, как исторические, так и подразумеваемые.
Реальные опционы
Ценообразование опционов может повлиять на повышение эффективности долгосрочных инвестиций компании или на процесс принятия финансовых решений. Использование ценообразования опционов для оценки фактических инвестиционных проектов называется реальными опционами. Идею реальных опционов приписывают Джуди Левен, в прошлом финансовому директору компании Merck. По существу, реальные опционы позволяют назначить явную цену для управленческой гибкости, которую часто упускают из вида при традиционном составлении плана долгосрочных инвестиций. Рассмотрим два примера.
Пусть вы являетесь владельцем нефтяной скважины. Сегодня наиболее правдоподобное предположение о стоимости нефти в скважине — 50 млн. долларов. Через 5 лет (оставаясь владельцем скважины) вам предстоит принять решение о разработке нефтяной скважины, которая обойдется вам в 70 млн. долларов. Бизнесмен с сомнительной репутацией готов купить скважину сегодня за 10 млн. долларов. Следует ли продать скважину?
Безусловно, цена нефти через 5 лет может вырасти. Даже если предположить, что цена нефти будет расти на 5% в год, через 5 лет нефть будет стоить 63,81 млн долларов. Традиционный план долгосрочного инвестирования предполагает, что нефть обесценится, поскольку стоимость ее разработки превышает стоимость нефти в скважине. Но не торопитесь — через 5 лет цена нефти в скважине будет другой, т.к. многие вещи (такие как мировая цена на нефть) могут измениться. Существует вероятность, что через 5 лет нефть будет стоить не меньше 70 млн. долларов. Если через 5 лет нефть будет стоить 80 млн. долларов, разработка скважины через пять лет принесет 10 млн долларов.
По сути, вы владеете пятилетним европейским колл-опционом на эту скважину, т.к. выигрыш от скважины через 5 лет такой же, как выигрыш по европейскому колл-опциону с курсом акции 50 млн. долларов, ценой исполнения 70 млн. долларов и сроком опциона 5 лет. Можно предположить, что годовая волатильность подобна волатильности акции типичной нефтяной компании (например, 30%). Если использовать безрисковую ставку 4,879%, можно определить, что цена такого колл- опциона составляет 11,47 млн. долларов (рис. 9). Из этого следует, что вы не должны продавать скважину за 10 млн. долларов.
Рис. 9. Реальный опцион на нефтяную скважину
Конечно, фактическая волатильность для этой нефтяной скважины неизвестна. По этой причине с помощью таблицы данных с одним входом определим, как цена опциона зависит от оценки волатильности (см. рис. 9). Как видно из таблицы, пока волатильность скважины составляет, по меньшей мере, 27%, опцион на нефтяную скважину стоит более 10 млн. долларов.
В качестве второго примера рассмотрим биотехнологическую компанию по производству лекарств, разрабатывающую лекарственный препарат для фармацевтической фирмы. В биотехнологической компании в настоящее время предполагают, что цена разрабатываемого препарата составляет 50 млн. долларов. Безусловно, цена лекарственного препарата со временем может понизиться. Для защиты от резкого падения цены биотехнологической компании требуется, чтобы препарат через 5 лет имел гарантированную цену 50 млн. долларов. Если страховая компания собирается подписать такое обязательство, какая справедливая цена должна быть назначена?
По существу, биотехнологическая компания запрашивает платеж в 1 млн. долларов для каждого миллиона долларов, на который цена препарата упадет ниже уровня 50 млн. долларов, через 5 лет. Это эквивалентно пятилетнему пут-опциону на цену лекарственного препарата. Предположим, что ставка по казначейскому векселю составляет 5% и годовая волатильность для акций сопоставимых компаний равна 40% (рис. 10), тогда цена этого опциона — 10,51 млн. долларов. Такой тип опциона часто называют опционом на отказ от проекта, но он эквивалентен пут-опциону. (На рис. 10 таблица данных показывает, как цена опциона отказа от проекта зависит от предполагаемой волатильности, составляющей 30–45% от цены лекарственного препарата.)
Рис. 10. Вычисление стоимости пут-опциона отказа от проекта
Вычисление исторической волатильности в Excel
Стоимость финансовых активов меняется ежедневно. Инвесторам нужен индикатор для количественной оценки этих изменений, которые часто трудно предсказать. Спрос и предложение – два основных фактора, влияющих на изменение цен на активы. В свою очередь, движения цен отражают амплитуду колебаний, которые являются причинами пропорциональных прибылей и убытков. С точки зрения инвестора неопределенность, связанная с такими влияниями и колебаниями, называется риском.
Цена опциона зависит от его базовой способности двигаться или, другими словами, от его способности быть нестабильным. Чем больше вероятность его движения, тем дороже его премия будет ближе к истечению срока. Таким образом, расчет волатильности базового актива помогает инвесторам устанавливать цену на производные инструменты на основе этого актива.
Ключевые выводы
Измерение вариации актива
Один из способов измерить вариацию актива – количественно оценить дневную доходность (процентное изменение на ежедневной основе) актива. Это подводит нас к определению и концепции исторической изменчивости. Историческая волатильность основана на исторических ценах и представляет собой степень изменчивости доходности актива. Это число без единицы измерения и выражается в процентах.
Вычисление исторической волатильности
Если мы назовем P (t) ценой финансового актива ( валютного актива, акций, валютной пары и т. Д.) В момент времени t, а P (t-1) ценой финансового актива в момент t-1, мы определим дневная доходность актива r (t) в момент времени t на:
где Ln (x) = функция натурального логарифма.
Общий доход R в момент времени Т:
R = Ln (P1 / P0) +… Ln (Pt-1 / Pt-2) + Ln (Pt / Pt-1)
Имеем следующее равенство:
И после упрощения имеем:
Доходность обычно рассчитывается как разница в относительных изменениях цен. Это означает, что если актив имеет цену P (t) в момент времени t и P (t + h) в момент времени t + h> t, доходность (r) составляет:
Когда доходность небольшая, например, всего несколько процентов, у нас есть:
Мы можем заменить r логарифмом текущей цены, поскольку:
Например, из ряда цен закрытия достаточно взять логарифм отношения двух последовательных цен, чтобы вычислить дневную доходность r (t).
Таким образом, можно также вычислить общий доход R, используя только начальную и конечную цены.
Годовая волатильность
Чтобы полностью оценить различную волатильность в течение года, мы умножаем эту волатильность на коэффициент, который учитывает изменчивость активов в течение одного года.
Для этого мы используем дисперсию. Дисперсия – это квадрат отклонения от средней дневной доходности за один день.
Чтобы вычислить квадратное число отклонений от средней дневной доходности за 365 дней, мы умножаем дисперсию на количество дней (365). Среднегодовое стандартное отклонение находится путем извлечения квадратного корня из результата:
Для годовой дисперсии, если мы предположим, что год составляет 365 дней, и каждый день имеет одинаковую дневную дисперсию, σ²daily, мы получим:
Годовая дисперсия = 365. σ² Ежедневная годовая
дисперсия = 365. [Σ (r (t)) ² / (n – 1)]
Наконец, поскольку волатильность определяется как квадратный корень из дисперсии:
Волатильность = √ (отклонение в годовом исчислении)
Моделирование
Данные
Мы моделируем с помощью функции Excel = RANDBETWEEN курс акций, который ежедневно колеблется между значениями 94 и 104.
Расчет ежедневной доходности
Расчет квадрата ежедневной прибыли
Вычисление дневной дисперсии
Вычисление дневного стандартного отклонения
Чтобы вычислить стандартное отклонение на ежедневной основе, мы вычисляем квадратный корень из дневной дисперсии. Так:
Вычисление годовой дисперсии
Чтобы вычислить годовую дисперсию из дневной дисперсии, мы предполагаем, что каждый день имеет одинаковую дисперсию, и умножаем дневную дисперсию на 365 с учетом выходных. Так:
Вычисление годового стандартного отклонения
Чтобы вычислить среднегодовое стандартное отклонение, нам нужно только вычислить квадратный корень из годовой дисперсии. Так:
Этот квадратный корень из годовой дисперсии дает нам историческую волатильность.
(Дополнительную информацию см. В разделе « Что на самом деле означает волатильность».)