Метод монте карло в excel

Обновлено: 07.07.2024

Моделирование методом Монте-Карло используется для моделирования вероятности различных результатов в процессе, который нелегко предсказать из-за вмешательства случайных переменных . Это метод, используемый для понимания влияния риска и неопределенности на модели прогнозирования и прогнозирования.

Моделирование методом Монте-Карло можно использовать для решения ряда проблем практически во всех областях, таких как финансы, машиностроение, цепочка поставок и наука. Это также называется моделированием множественной вероятности.

Ключевые моменты

Понимание моделирования Монте-Карло

Столкнувшись со значительной неопределенностью в процессе прогнозирования или оценки, вместо простой замены неопределенной переменной одним средним числом, имитация Монте-Карло может оказаться лучшим решением при использовании нескольких значений.

Поскольку бизнес и финансы страдают от случайных величин, моделирование методом Монте-Карло имеет широкий спектр потенциальных приложений в этих областях. Они используются для оценки вероятности перерасхода средств в крупных проектах и вероятности того, что цена актива изменится определенным образом.

Их также используют страховщики и бурильщики. Моделирование методом Монте-Карло находит бесчисленное множество приложений вне бизнеса и финансов, например, в метеорологии, астрономии и физике элементарных частиц.

История моделирования Монте-Карло

Симуляторы Монте-Карло названы в честь популярного места для азартных игр в Монако, поскольку случайность и случайные результаты являются центральными для техники моделирования, так же как и для таких игр, как рулетка, кости и игровые автоматы.

Этот метод был впервые разработан Станиславом Уламом, математиком, работавшим над Манхэттенским проектом. После войны, восстанавливаясь после операции на мозге, Улам развлекался, играя в бесчисленные пасьянсы. Он заинтересовался нанесением результатов каждой из этих игр, чтобы наблюдать за их распределением и определять вероятность выигрыша. После того, как он поделился своей идеей с Джоном фон Нейманом, они вместе разработали симуляцию Монте-Карло.

Метод моделирования Монте-Карло

Основа моделирования Монте-Карло заключается в том, что вероятность изменения результатов не может быть определена из-за вмешательства случайных величин. Поэтому моделирование Монте-Карло фокусируется на постоянно повторяющихся случайных выборках для достижения определенных результатов.

При моделировании методом Монте-Карло переменной, имеющей неопределенность, присваивается случайное значение. Затем модель запускается, и предоставляется результат. Этот процесс повторяется снова и снова, присваивая рассматриваемой переменной много разных значений. После завершения моделирования результаты усредняются для получения оценки.

Расчет моделирования Монте-Карло

Анализируя исторические данные о ценах, вы можете определить дрейф, стандартное отклонение , дисперсию и среднее движение цены ценной бумаги. Это строительные блоки моделирования Монте-Карло.

Чтобы спроектировать одну возможную траекторию цены, используйте исторические данные о цене актива, чтобы сгенерировать серию периодических ежедневных доходов с использованием натурального логарифма (обратите внимание, что это уравнение отличается от обычной формулы процентного изменения):

Затем используйте функции AVERAGE, STDEV.P и VAR.P для всего результирующего ряда, чтобы получить входные данные средней дневной доходности, стандартного отклонения и дисперсии соответственно. Дрейф равен:

В качестве альтернативы дрейф можно установить на 0; этот выбор отражает определенную теоретическую ориентацию, но разница не будет большой, по крайней мере, для более коротких временных рамок.

ЗАКАЗАТЬ РЕШЕНИЕ ЗАДАЧ МЕТОДОМ МОНТЕ-КАРЛО
Один из самых прикладных методов статистической оценки риска. К нему нужно отнестись с большим участием. В данной статье будет рассмотрен пример имитационного моделирования с использованием данного подхода.

Метод Монте-Карло получил своё название за то, что предназначен осуществить оценку предельно случайных событий. А что, как ни казино, которых в Монте-Карло много, связано со случайностью больше всего?

В процессе работы нам понадобится «генератор случайных чисел» из MS Excel и функция «Описательная статистика».

Оценка риска инвестиционного проекта

Есть следующие условия задачи:

Таким образом, нам нужно оценить три периода – за три года. Запишем все исходные данные в таблицу. Значения, полученные в ячейках D5-X5, имеют формулу для вычисления или есть в условиях задачи. Вы, как экономист, с формулами должны быть знакомы. Обратите внимание на заголовок, выделенный красным цветом на рисунке ниже – «Имитационная модель NCF1». Это говорит о том, что мы имитируем первый год, а всего их будет три на разных листах в MS Excel. На новый лист переключиться внизу окна программы.

Теперь в MS Excel переключаемся на «Данные» и выбираем пункт «Анализ данных».

В появившемся окне выбираем «Генерация случайных чисел». Выполняем генерацию с параметрами, продемонстрированными на картинке ниже, для пункта «Кол-во пользователей».

Параметры будут отталкиваться от среднего значения 250, оно есть в ожидаемых значениях в нашей таблице. Нужно выполнить 1000 генераций. Если вы знакомы со статистикой, то понимаете, что большее количество испытаний даёт более точную оценку. Используя метод Монте-Карло, можно имитировать и 10 000 значений для большей точности.

После мы имитируем все стохастические, то есть, меняющиеся значения по аналогии, как показано выше. Копируем формулы переменных или констант из ячеек D7-X7 под «Результаты имитации» с учетом имитированных значений. Получаем следующий результат.

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

Теперь делаем то же самое, но для имитационной модели NCF2.

Это второй год работы проекта. Как видим, под «СКО» процентные соотношения увеличились. Об этом говорится в условии задачи, что налоги и зарплата должны расти каждый год.

Повторяем это действие в третий раз, увеличивая налоги и зарплаты, как говорит условие.

Наибольшую важность в оценке инвестиционного проекта имеет параметр NCF – чистый денежный поток. Копируем все значения NCF на четвертый лист с каждой из трёх предыдущих страниц.

Формула для расчета NPV есть вверху картинки. Используем её. Теперь точно так же заходим в «Данные», жмём на «Анализ данных» и выбираем там «Описательная статистика». Вот, что в появившемся окне вам нужно указать.

Во входном интервале выбирается 1000 полученных значений NPV. Выходной интервал можете выбрать произвольно. На выходе у вас будет таблица со статистическими данными.

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

Заключение

Генерация случайных чисел – наше всё. Именно в оценке того, к чему может привести случайность, заключается статистический метод Монте-Карло. Это работает не только в экономике, но и везде, где есть случайность. Можете посмотреть, как это делается, применительно к зоологии в видео ниже.

После запуска файла Monte Carlo 20.12.xlam в MS Excel появится панель управления надстройкой:

Блок "Задать цели"

Здесь необходимо указать ячейки, которые содержат формулы, зависящие от генераторов случайных чисел. Это может быть стандартный генератор Excel =СЛЧИС( ), или генераторы из данной надстройки, или другие генераторы случайных чисел, доступные для страницы Excel.
Целевые ячейки можно вводить, указывая целый диапазон, напр. A1:D1 , или несвязанные ячейки, напр. A1; D8; F11 . Для ввода целевых ячеек выделите нужные ячеки на рабочем листе Excel и щелкните по значку справа от поля Целевые ячейки. После этого нужные ячейки появятся в поле.

Your browser does not support the HTML5 canvas tag.

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

Это поле показывает, сколько раз будет проведено моделирование для оценки среднего значения целевых ячеек. По умолчанию задано значение 10 тыс. реализаций, но это число неограничено. Однако, для большого числа реализаций (1 млн. и более) время моделирования может оказаться значительным (десятки минут и часы). Начать лучше с 10 тысяч испытаний, а если понадобится уточнить результат, можно по времени счета, указанному на странице "Итоги", оценить, сколько будет продолжаться расчет при увеличении статистики в 100 и более раз.

Блок "Показывать"

отказаться от вывода кумулят;
отказаться от данных для построения диаграмм;
отказаться от вывода самих диаграмм (это бывает необходимо, если задано много целевых ячеек и значений параметра. Вряд ли при задании трех целевых ячеек и 10 значений параметра кто-то захочет рассматривать 30 диаграмм.)
выводить кумуляты, но не запрашивать частотных распределений и снять галочку только с пункта "Гистограммы";
выводить все гистограммы в одном масштабе. При этом все диаграммы для заданной целевой ячейки будут показаны на одних и тех же отрезках осей координат. Для разных целевых ячеек масштабы, все равно будут разными.
задать число разрядов в гистограммах, если не устраивает значение по умолчанию. Число разрядов частотных диаграмм по умлчанию устанавливается равным 50 (25 при статистике меньше 3 000).

Для поиска минимума или максимума целевых ячеек можно использовать моделирование с параметром, для этого нужно отметить чекбокс "Задать переменную решения", и на панели надстройки появится блок "Задать переменную":

Блок "Задать переменную"

Поле В ячейку переменной предназначено для адреса ячейки изменяемого параметра, т.е. величины, которую нужно поварьировать.
Параметр может быть только один.

Your browser does not support the HTML5 canvas tag.

Блок "Разное"

Метод Монте-Карло назван в честь района Монако, известного своими казино. В этой статье мы будем использовать метод Монте-Карло для изучения зависимости эффективности ставок от фактора везения.

Что такое метод Монте-Карло?

Метод Монте-Карло используется в тех случаях, когда применение других математических подходов сопряжено с большим количеством трудностей.

Суть метода заключается в многократном использовании генератора случайных чисел. На основе полученных данных вычисляются вероятности в рассматриваемом процессе.

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

Анализ эффективности ставок

Допустим, есть история из 1500 ставок одинакового размера с доходностью (Yield) 0,85 %. Но как понять, является ли это оправданной закономерностью или же результатом влияния удачи или невезения?

Первый шаг предполагает сравнение этой величины со значением математического ожидания. Концепция используемой методологии предполагает оценку справедливого коэффициента каждой ставки и, следовательно, величины ценности (валуйности).

Например, коэффициент букмекера 2,10 при справедливом коэффициенте 2,00 содержит ценность (value) 5 % или 1,05 (рассчитанное путем деления 2,10 ⁄ 2,00).

Справедливый (или объективный) коэффициент 2,00 предполагает вероятность выигрыша 50 %. Если сделать 100 ставок, 50 из которых будут выигрышными (прибыль по каждой ставке 1,10 доллара), а 50 — проигрышными (убыток по каждой ставке — 1 доллар), то размер чистой прибыли составит 5 долл. (или 5 % от оборота в 100 долл.).

Аналогичным образом, КФ букмекера 3,30 при справедливом коэффициенте 2,95 будет содержать ценность 11,8 %. В приведенной ниже таблице представлены данные прогнозирования.

Матч	Ставка	Кэф БК	Справедливый КФ	Валуйность
Рубин — Спартак	П2	2,1	1,9	10,5%
Левски — Славия	П2	3,2	3	6,7%
Вердер — Ганновер	П1	2	1,95	2,3%
Ганза — Карлсруэ	П2	3,3	2,95	11,9%
Шабаб — Видад	П2	2,2	2,15	2,3%
Хуарес — Толука	П1	2,35	2,2	6,8%

Определить общую валуйность и размер ожидаемой прибыли для полной истории ставок достаточно просто, поскольку нужно просто вычислить среднее значение.

Для портфеля из 1500 ставок оно было равно 4,1 %, и это указывает на то, что если бы использованная система ставок функционировала точно так, как прогнозировалось, ожидаемая прибыль по ставкам на сумму 1500 доллар составляла бы 61,5 доллара.

В действительности же доход для этих ставок был равен 13,7 доллара. По-видимому, такой низкий результат объясняется невезением (при условии, конечно, что использованная модель прогнозирования функционировала должным образом).

Вопрос заключается в том, насколько сильна эта зависимость? Для поиска ответа на этот вопрос можно воспользоваться методом Монте-Карло.

Моделирование Монте-Карло в Excel

Выполнить моделирование по методу Монте-Карло в Excel достаточно просто.

Нажатие клавиши F9 позволяет выполнить перерасчет всех случайных чисел для совершенно нового моделирования и вычисления теоретической доходности новой выборки.

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

К счастью, в Excel предусмотрен быстрый и простой способ выполнения множества моделирований за один раз с помощью функции таблицы данных. Для доступа к ней перейдите в Данные ⇒ Анализ «что если» ⇒ Таблица данных .

Вычислите доходность своей выборки в любой свободной ячейке Excel, как описано в шаге три выше.

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

После этого вызовите таблицу данных в Excel. Отобразится окно, подобное приведенному ниже.

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

Нажмите ОК и посмотрите, что сделает Excel. Ячейки, выделенные ниже первой, будут заполнены новыми вычисленными значениями доходности, каждое из которых представляет собой одно моделирование. В рамках этого примера было выполнено 10 моделирований, как показано ниже.

Измерение влияния удачи на результаты ставок

Можно выполнить столько моделирований, сколько мы захотим, хотя чем больше их количество, тем больше времени займут вычисления в Excel. Для целей этой статьи было выполнено 100 000 моделирований (что заняло около пяти минут).

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

Средняя доходность составила 4,05 %, что почти совпадает с величиной ценностного ожидания данной истории ставок. Однако наблюдается широкий разброс значений от –12,23 % (самый низкий показатель эффективности) до 23,26 % (самый высокий показатель).

Действительно, убыточными оказались почти 17 % моделирований, хотя величина теоретического ценностного ожидания для использованной истории ставок превышала 4 %, в то время как доходность могла быть выше фактического показателя 0,85 % в 76 % случаев.

Фактически, используя эти данные, мы могли бы вычислить вероятность достижения какого-либо определенного порогового значения доходности в Excel без необходимости в проведении каких-либо статистических тестирований.

Метод Монте-Карло позволяет сделать все это с минимумом усилий с нашей стороны. Полное распределение 100 000 результатов моделирования доходности приведено в таблице ниже (с шагом 0,1 % по оси X). Тот, кто имеет представление о нормальном распределении, может увидеть, что это совпадение почти идеальное.

Конечно, если бы фактический размер доходности был равен, скажем, –5 % или ниже (что возможно только в 1 % случаев), можно было бы задуматься о несовершенстве использованной системы ставок. Таким образом, метод Монте-Карло является полезным инструментом для проведения таких субъективных оценок.

Несовершенная система ставок или невезение?

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

Использованная в этой статье история ставок включала более 1500 ставок с прогнозируемым математическим ожиданием свыше 4 %. Несмотря на это преимущество, результаты моделирований по методу Монте-Карло продемонстрировали, что проигрыш возможен более чем в одном случае из пяти.

Если бы ваша стратегия ставок содержала похожее преимущество, как бы вы повели себя, сделав 1500 ставок, которые ничего бы вам не принесли? Сохранили бы уверенность в своей методологии, списали бы низкие результаты на невезение или потеряли бы веру в свой подход?

Один из способов, который может помочь в решении этой дилеммы, предполагает увеличение размера выборки. Опять же, для того чтобы понять, как меняется общая картина при увеличении размера истории ставок, можно обратиться к методу Монте-Карло.

В рамках мысленного эксперимента первоначальное количество ставок (1500) было увеличено в десять раз (путем простого воспроизведения исходной выборки коэффициентов ставок еще девять раз). Дополнительное моделирование, проведенное 100 000 раз, позволило получить приведенные далее значения доходности.

Среднее значение доходности = 4,04 %
Наименьшее значение доходности = –1,21 %
Наибольшее значение доходности = 10,17 %
Вероятность доходности < 0 % = 0,1 %
Вероятность доходности > 0,85 % = 99,2 %

Ниже представлено новое распределение, которое было получено после выполнения 100 000 моделирований, наложенное на исходное распределение для первоначальной выборки из 1500 ставок.

Очевидная разница между двумя выборками — это размер спреда или диапазона возможных значений доходности, который намного ýже в случае с большей историей ставок. Такой результат вполне предсказуем и является просто следствием закона больших чисел.

Оценка результатов моделирования по методу Монте-Карло

Чем больше история ставок, тем вероятнее, что фактическая эффективность будет ближе к ожидаемой — конечно, при условии, что система прогнозирования функционирует должным образом.

Напрашивающийся вывод состоит в том, что если после более 15 000 ставок доходность будет оставаться на отметке 0,85 % или ниже, это утверждение следует поставить под сомнение.

В конечном счете, метод Монте-Карло не позволит вам точно понять, какие факторы, не считая везения, определяют результативность вашей системы ставок. Тем не менее это действительно полезный инструмент, который поможет в формировании обоснованных суждений в этом отношении, а также даст вам возможность оценить диапазон обоснованно ожидаемых исходов, возможных в пределах действия удачи и невезения.

В качестве бонуса вы можете скачать файл Excel с моделированием Монте-Карло. Его можно модифицировать и использовать для анализа эффективности своей игровой стратегии.

Читайте также: