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

Обновлено: 20.11.2024

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

Вероятностный анализ с точки зрения недвижимости

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

Также отмечу, что некоторые концепции, представленные здесь, я адаптировал из превосходной дипломной работы Кита Чин-Ки Люна на тему: Помимо анализа DCF в финансовом моделировании недвижимости: вероятностная оценка сделок с недвижимостью.

Моделирование по методу Монте-Карло для недвижимости — уровень Excel Nerd: 1 000 000

Чем это руководство не является

Этот пост не является курсом вероятностного анализа. Таким образом, предполагается, что вы имеете базовые знания о вероятности, статистике, Excel и знаете, что такое моделирование методом Монте-Карло. Если вы хотите освежить в памяти вероятность или статистику в целом, я рекомендую пройти курс по этому предмету. Вот бесплатный МООК (массовый открытый онлайн-курс), предлагаемый Duke:

Сценарий — Сделка с квартирой

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

  1. Требуемое свойство содержит 10 единиц.
  2. За каждую единицу оцениваемого объекта взимается плата в размере 1000 долларов США в месяц; арендная плата выросла на 3% в прошлом году
  3. Есть одна свободная единица, и для простоты мы предполагаем, что одна свободная единица всегда будет
  4. Расходы составляют 3000 долларов США в месяц; расходы выросли на 2% в прошлом году
  5. Сопоставимые объекты недвижимости сегодня продаются по ставке капитализации от 5,5% до 6,0%, но ожидается, что ставки капитализации будут расти примерно на 5 базисных пунктов в год в ближайшие годы (ставка капитализации при выходе от 5,75% до 6,25%).
  6. Планируете владеть недвижимостью в течение пяти лет.
  7. Целитесь на уровне 8 % без использования заемных средств.

Настройка модели

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

  1. В столбцах B и D я опускаю свои базовые предположения.
  2. Ячейка G2, которую я обозначил как "Значение DCF"
  3. В строке 14, начиная с ячейки F14–K14, я добавляю заголовок периода с шестью периодами, включая нулевой период.
  4. Ячейка E15 с пометкой "Аренда"
  5. Ячейка E16 с пометкой "Расходы"
  6. Ячейка E17. Я обозначил "Чистый операционный доход".
  7. Ячейка E18 – "Остаточная стоимость".
  8. Ячейка L17, которую я обозначил как "Выходной колпачок"
  9. Ячейка E19. Я обозначил "Чистый денежный поток".
  10. Ячейка D14 обозначена как "Темп роста".
  11. В ячейке G15 я записываю формулу: =9*12*$D$4*(1+$D15)^(G14-1), что означает девять единиц (10 единиц минус одна свободная единица), умножить на 12 месяцев, умножить на $D$4 ($1000 арендной платы за единицу/месяц), умноженное на один плюс $D15 (вероятная скорость роста, рассчитанная в ячейке D15), увеличенная до периода (G15) минус один (я вычитаю один, потому что мы не хотим, чтобы арендная плата росла). в первый год). Поскольку созданы правильные абсолютные ссылки на ячейки (например, $D$4 и $D15), я могу скопировать формулу прямо в ячейку K15.
  12. Я использую аналогичный процесс для расходов, используя формулу: =$D$7*12*(1+$D16)^(G14-1) в ячейке G16, а затем копирую эту формулу в ячейку K16.
  13. В ячейках G17–K17 я вычитаю расходы из арендной платы (например, в ячейке G17 я записываю =G15-G16), чтобы получить чистый операционный доход за каждый год.
  14. В ячейке K18 я записываю формулу: =K17/L18, что означает деление чистой операционной прибыли за пятый год на вероятную ставку предельной капитализации (L18).
  15. В ячейках G19–K19 я суммирую чистые денежные потоки за каждый год: чистый операционный доход за годы с первого по четвертый и чистый операционный доход плюс остаточная стоимость за пятый год.
  16. Наконец, в ячейке G3 я рассчитываю текущую стоимость потока денежных средств в строке 19, дисконтированную обратно по ставке 8 % (целевой доход без использования заемных средств), используя формулу: =NPV(D12,G19:K19).

Настроив DCF, я могу перейти к добавлению вероятности к моим предположениям.

Добавление вероятности с помощью функции СЛУЧМЕЖДУ()

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

У нас есть несколько вариантов, два из которых наиболее распространены: равномерное распределение (постоянная вероятность, при которой все результаты равновероятны) и нормальное распределение (представьте себе вероятность кривой нормального распределения, при которой результирующее значение, вероятно, будет ближе к среднему). Для простоты мы выберем равномерное распределение.

  1. В ячейке D15 я добавляю равномерную изменчивость к темпу роста арендной платы по формуле: =$D$5*СЛУЧМЕЖДУ(-500,2000)/1000, что означает получение 3 % (рост арендной платы за прошлый год из ячейки $D$5). ) и умножьте его на случайное число от -0,5 до 2,0 (СЛУЧМЕЖДУ (-50 200)/100), чтобы результирующий темп роста арендной платы случайно упал между -1,5% и 6,0%.
  2. В ячейке D16 я добавляю равномерную изменчивость к темпу роста расходов по аналогичной формуле: =$D$8*СЛУЧМЕЖДУ(-500,2000)/1000, только в этом случае я беру прошлогодний темп роста расходов (2% из ячейки $D$8) и умножьте его на случайное число в диапазоне от -0,5 до 2,0 (СЛУЧМЕЖДУ (-50 200)/100), чтобы результирующий темп роста расходов случайно попал в диапазон от -1,0% до 4,0%.
  3. Наконец, в ячейке L18 я добавляю равномерную изменчивость к показателю предельных значений при выходе по формуле: =D10*СЛУЧМЕЖДУ(958,3 1041,7)/1000, что означает получение 6 % (среднее значение между ожидаемыми 5,75 % и 6,25 %). диапазон предельных значений выхода в пятом году) и умножить его на случайное число от 0,9583 до 1,0417 (СЛУЧМЕЖДУ (958,3 1041,7)/1000), чтобы результирующее значение выходного предела случайно попало в диапазон от 5,75% до 6,25%.

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

Выполнение моделирования методом Монте-Карло с использованием таблиц данных

Добавив вероятность в вашу модель, вы можете приступить к моделированию методом Монте-Карло. Этот процесс включает в себя создание таблицы данных, связанной с вашим значением DCF (G3), чтобы каждое моделирование записывало результирующее значение DCF из этого моделирования.

Вот как мы запускаем моделирование методом Монте-Карло, используя функцию таблицы данных в Excel:

Вероятный диапазон значений, при этом 1,2 млн долларов – это "ожидаемое значение"

Ожидаемая ценность — сколько вы готовы заплатить

Среднее (среднее) всех симуляций – это ваша "ожидаемая стоимость" или сумма, которую вы, возможно, готовы заплатить за рассматриваемое свойство с учетом ваших предположений. В моем случае ожидаемая стоимость составляет около 1,2 млн долларов США.

Мне также нравится вычислять минимальное, максимальное и стандартное отклонение моделирования, чтобы получить представление о диапазоне значений. Так, например, в этом случае минимум составляет около 925 000 долларов, а максимум — около 1,5 миллиона долларов. Это означает, что в одном случае мне нужно было заплатить 925 000 долларов США, чтобы получить доход в 8 %, а в другом – я мог заплатить 1,5 млн долларов, чтобы получить доход в 8 %.

В заключение отметим, что за наш гипотетический многоквартирный дом мы готовы заплатить от 925 000 до 1,5 млн долларов США, при этом наиболее вероятная цена покупки составляет 1,2 млн долларов США.

Видеоучебник – Запуск моделирования Монте-Карло в сфере недвижимости

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

Использование файла Excel из видео

Чтобы сделать это учебное пособие по моделированию методом Монте-Карло доступным для всех, оно предлагается по принципу «Плати, сколько сможешь» без минимальной суммы (если хотите, введите 0 долларов США) или максимальной суммы (ваша поддержка помогает поддерживать актуальность контента). — аналогичные модули курсов по недвижимости продаются по цене от 100 до 300 долларов США). Просто введите цену вместе с адресом электронной почты, на который нужно отправить ссылку для скачивания, а затем нажмите «Продолжить». Если у вас есть какие-либо вопросы о нашей программе «Плати, сколько вы можете» или о том, почему мы предлагаем наши модели на этой основе, обратитесь к Майку или Спенсеру.

Об авторе: Спенсер Бертон родился и вырос на северо-западе США и имеет почти 20-летний опыт работы в сфере жилой и коммерческой недвижимости. За свою карьеру он застраховал коммерческую недвижимость на сумму 30 миллиардов долларов в некоторых из крупнейших институциональных фирм по недвижимости в мире. В настоящее время он является главой отдела инвестиций в недвижимость и членом команды основателей Stablewood Properties.Спенсер имеет степень бакалавра международных отношений Университета штата Флорида и степень магистра финансов в сфере недвижимости Корнельского университета.

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

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

Ключевые выводы

  • Метод Монте-Карло направлен на решение сложных задач с использованием случайных и вероятностных методов.
  • Симуляцию методом Монте-Карло можно разработать с помощью Microsoft Excel и игры в кости.
  • Для получения результатов можно использовать таблицу данных — всего для подготовки моделирования методом Монте-Карло требуется 5000 результатов.

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

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

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

Когда использовать моделирование методом Монте-Карло

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

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

Игра в кости

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

• Игрок бросает три шестигранных кубика три раза.

• Если сумма трех бросков равна семи или 11, игрок выигрывает.

• Если сумма трех бросков: три, четыре, пять, 16, 17 или 18, игрок проигрывает.

• Если сумма равна любому другому результату, игрок снова играет и перебрасывает кости.

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

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

Чтобы подготовить моделирование методом Монте-Карло, вам потребуется 5 000 результатов.

Шаг 1. События с играми в кости

Сначала мы собираем ряд данных с результатами каждого из трех кубиков за 50 бросков. Для этого предлагается использовать функцию "СЛУЧМЕЖДУ(1,6)". Таким образом, каждый раз, когда мы нажимаем F9, мы генерируем новый набор результатов броска. Ячейка "Результат" представляет собой сумму результатов трех бросков.

Шаг 2. Диапазон результатов

Затем нам нужно разработать ряд данных, чтобы определить возможные результаты для первого раунда и последующих раундов. Имеется диапазон данных из трех столбцов. В первом столбце у нас есть числа от 1 до 18. Эти цифры представляют возможные результаты после трехкратного броска игральной кости: Максимум 3 x 6 = 18. Обратите внимание, что для ячеек один и два результаты равны N/ А так как невозможно получить единицу или двойку, используя три кубика. Минимум три.

Во втором столбце указаны возможные выводы после первого раунда. Как указано в первоначальном заявлении, игрок либо выигрывает (Win), либо проигрывает (Lose), либо переигрывает (Re-roll), в зависимости от результата (общего количества трех бросков кубиков).

В третьем столбце регистрируются возможные выводы для последующих раундов. Мы можем добиться этих результатов, используя функцию «ЕСЛИ».Это гарантирует, что если полученный результат эквивалентен результату, полученному в первом раунде, мы выиграем, в противном случае мы следуем первоначальным правилам исходной игры, чтобы определить, будем ли мы перебрасывать кости.

Шаг 3. Выводы

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

Можно получить результаты других бросков костей, используя функцию "ИЛИ" и индексную функцию, вложенную в функцию "ЕСЛИ". Эта функция сообщает Excel: «Если предыдущий результат — «Выигрыш» или «Проигрыш», перестаньте бросать кости, потому что, как только мы выиграем или проиграем, мы закончим. В противном случае мы переходим к столбцу следующих возможных выводов и определяем вывод результата.

Шаг 4. Количество бросков кубиков

Теперь мы определяем количество бросков костей, необходимых для проигрыша или выигрыша. Для этого мы можем использовать функцию «СЧЁТЕСЛИ», которая требует, чтобы Excel подсчитывал результаты «Переброса» и добавлял к ним число один. Он добавляет один, потому что у нас есть один дополнительный раунд, и мы получаем окончательный результат (победа или поражение).

Шаг 5. Моделирование

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

Затем мы создадим таблицу анализа чувствительности, используя данные объектов или таблицу данных таблицы (эта чувствительность будет вставлена ​​во вторую таблицу и третий столбец). В этом анализе чувствительности количество событий от одного до 5000 должно быть вставлено в ячейку A1 файла. На самом деле можно было выбрать любую пустую ячейку. Идея состоит в том, чтобы каждый раз принудительно выполнять перерасчет и, таким образом, получать новые броски кубиков (результаты новых симуляций), не нарушая существующие формулы.

Шаг 6. Вероятность

Наконец-то мы можем рассчитать вероятности выигрыша и проигрыша. Делаем это с помощью функции «СЧЁТЕСЛИ». Формула подсчитывает количество «выигрышей» и «проигрышей», затем делит их на общее количество событий, 5000, чтобы получить соответствующую пропорцию одного и другого. Наконец, мы видим, что вероятность выигрыша составляет 73,2 %, а вероятность проигрыша – 26,8 %.

Добавьте симуляцию Монте-Карло в свои табличные модели

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

В примерах в этом руководстве используются функции моделирования RiskAMP Монте-Карло.

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

Модель инвестиционного портфеля

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

Рисунок А. Исходная модель

На рисунке A модель основана на фиксированной (годовой) доходности в размере 5,4%. В течение 5 лет это дает доход в размере 30,08%.

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

Шаг 1. Добавление случайных данных

В модели Монте-Карло вместо фиксированной доходности в 5,4 % мы ожидаем, что доход будет нормально распределен со средним значением 5,4 % и стандартным отклонением 7,3 %. Для каждой возвращаемой ячейки в электронной таблице (столбец D) мы используем случайную функцию NormalValue :

Рисунок Б. Добавление случайных данных

На рисунке B доходность за каждый период была изменена с фиксированных 5,4% на случайно распределенную доходность с помощью функции, показанной на функциональной панели. Доходы в каждом периоде генерируются случайным образом. Если вы пересчитаете модель на этом шаге, вы увидите каждое изменение возврата. Суммарный доход ( F11 ) также может значительно отличаться от первоначального значения (30,08%).

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

Шаг 2. Запуск моделирования методом Монте-Карло

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

Надстройка RiskAMP включает ряд функций для анализа результатов моделирования методом Монте-Карло. Для начала посмотрим на средние результаты симуляции с помощью функции SimulationMean.

Рисунок C: Средние результаты моделирования

На рисунке C мы добавили средние результаты моделирования в столбец H, используя функцию, показанную на панели функций. В этом примере ячейка H11 вычисляет среднее значение ячейки F11 по всем испытаниям или итерациям моделирования методом Монте-Карло.

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

Вы увидите, что среднее значение, возвращаемое в ячейке H11, близко к исходному фиксированному значению 30,08 % (см. ячейку F11 на рис. A). Это ожидаемо, потому что случайные данные, которые мы используем для возврата, имеют в среднем 5,4%, что было фиксированным значением в исходной модели.

Шаг 3. Анализ данных

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

Начнем с того, что мы можем посмотреть на минимальное и максимальное значения, определенные во время моделирования, с помощью функций SimulationMin и SimulationMax:

Рисунок D: Минимальные и максимальные результаты моделирования

На рисунке D ячейка I11 содержит минимальное значение ячейки F11, наблюдаемое во время моделирования. Это значительно хуже, чем в среднем, и представляет собой риск, содержащийся в модели портфеля. Это означает, что существует некоторая вероятность того, что этот портфель в течение 5 лет принесет чистый убыток в размере 33%.

Глядя на абсолютные минимальные и максимальные значения, можно завысить выбросы или хвосты возможных результатов модели портфеля. Мы также можем посмотреть процентные вероятности, используя функцию SimulationPercentile:

Рисунок E. Процентильные результаты

На рисунке E ячейка I11 содержит функцию SimulationPercentile, как показано на панели функций. Чтобы понять, что означают процентили, представьте, что мы берем каждый результат, показанный в ячейке F11, по модели Монте-Карло, и располагаем их по порядку (от меньшего к большему). Первое значение будет минимальным, как показано выше; в результатах нет значений ниже минимального значения. Последнее значение будет максимальным; 100% значений в результатах равны или ниже последнего значения. Поэтому максимальным значением является 100-й процентиль.

Тогда 25-й процентиль представляет собой значение, равное или превышающее 25 % результатов, полученных во время моделирования. Другими словами, существует 25%-ная вероятность того, что любое значение в симуляции будет меньше или равно этой сумме; и в то же время существует 75%-ная вероятность того, что любое значение в симуляции будет выше или равно этой сумме.

На рисунке E в ячейке I11 показан результат 25-го процентиля для ячейки F11 . Это говорит нам о том, что при моделировании в 75 % случаев значение F11 выше или равно 16,61 %. Или существует вероятность 75 %, что наша модель будет иметь общий доход 16,61 % или выше.

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

Шаг 4. Определение уровней достоверности

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

Рисунок F: Уровни достоверности

Этот анализ отвечает на вопрос, какова вероятность того, что модель вернет не менее 50 % результатов во всех испытаниях симуляции? На рисунке F эта вероятность составляет около 16%; то есть существует вероятность 16 %, что модель даст доход в размере 50 % или более*.

Такого рода анализ может быть полезен при определении уровней достоверности. Например, при оценке альтернативных инвестиций мы можем сравнить вероятности достижения определенного минимального дохода.Или мы можем понять вероятность убытков, связанных с инвестициями, найдя вероятность того, что они вернутся на 0%.

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

Заключение

Вышеприведенное обсуждение описывает преобразование простой модели фиксированного портфеля в моделирование Монте-Карло, а также виды анализа, которые можно выполнить с помощью моделирования Монте-Карло. Это очень простой пример; доступно множество различных функций анализа, и существует множество различных способов генерации случайных данных в модели. Дополнительную информацию о различных функциях см. в справочном руководстве по RiskAMP (доступно в Excel или в меню «Пуск»).

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

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

Моделирование по методу Монте-Карло – это процесс использования кривых вероятности для определения вероятности исхода. Здесь вы можете почесать затылок и сказать… «Эй, Рик, кривая распределения имеет массив значений. Так как же точно определить вероятность результата?» И еще лучше, как это сделать в Microsoft Excel без каких-либо специальных надстроек

Думал, ты никогда не спросишь.

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

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

Опишите Монте-Карло

Описывая моделирование Монте-Карло, я часто ссылаюсь на фильм 1980-х годов «Военные игры», где молодой Мэтью Бродерик (до Ферриса Бьюллера) — хакер, использующий модем для коммутируемого доступа, чтобы взломать компьютеры Пентагона и начать Третью мировую войну. Что-то вроде. Затем он попросил компьютеры Пентагона создать множество симуляций игр Tic Tac Toe, чтобы научить компьютер тому, что никто не хочет ядерной войны, и в процессе спасти мир.

Спасибо, Феррис. Ты герой.

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

Кривые распределения

Существуют различные кривые распределения, которые можно использовать для настройки моделирования методом Монте-Карло. И эти кривые могут меняться местами в зависимости от переменной. У Microsoft нет формулы под названием «Моделирование по методу Монте-Карло» в строке меню 🙂

Равномерное распределение

При равномерном распределении существует равная вероятность в любом месте между минимумом и максимумом. Равномерное распределение выглядит как прямоугольник.

Нормальное (гауссово) распределение

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

Логнормальное распределение

Распределение, в котором логарифм нормально распределен со средним значением и стандартным отклонением. Таким образом, настройка аналогична нормальному распределению, но обратите внимание, что переменные mean и standard_dev предназначены для представления логарифма.

Распределение Пуассона

Это, вероятно, самый малоиспользуемый дистрибутив. По умолчанию многие люди используют кривую нормального распределения, когда Пуассон лучше подходит для их моделей.Пуассон лучше всего описывается, когда в самом начале имеется большое распределение, которое быстро рассеивается в виде длинного хвоста с одной стороны. Примером этого может быть колл-центр, где ни один вызов не отвечает до второго НУЛЯ. Далее следует большинство звонков, на которые ответили в течение первых двух интервалов (скажем, 30 и 60 секунд), с быстрым спадом громкости и длинным хвостом, и очень немногие звонки, на которые ответили в течение 20 минут (якобы).

Цель здесь не в том, чтобы показать вам все возможные дистрибутивы в Excel, так как это выходит за рамки этой статьи. Скорее, чтобы убедиться, что вы знаете, что есть много вариантов, доступных для вашего моделирования Монте-Карло. Не попадайтесь в ловушку, предполагая, что кривая нормального распределения подходит для всего вашего моделирования данных. Чтобы найти больше кривых, перейдите к разделу «Статистические функции» в книге Excel и изучите их. Если у вас есть вопросы, задайте их в разделе комментариев ниже.

Построение модели

Для этой настройки мы предполагаем нормальное распределение и 1000 итераций.

Входные переменные

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

Первое моделирование

  • Вероятность = функция RAND() для получения случайного числа на основе других критериев в распределении.
  • Среднее = среднее значение, использованное на шаге 1. Для дохода это C3.
  • Стандартное отклонение = стандартное отклонение, используемое на шаге 1. Для дохода это C4

Поскольку в качестве вероятности используется RAND(), при обновлении генерируется случайная вероятность. Мы воспользуемся этим преимуществом на следующем шаге.

1000 симуляций

  • Сначала мы хотим создать структуру таблицы. Мы делаем это, перечисляя числа от 1 до 1000 в строках. На изображении ниже пример списка номеров начинается с B12.
  • в следующем столбце, в ячейке C12, мы будем ссылаться на первую итерацию.

  • Далее выделите область, в которой мы хотим разместить 1000 итераций.
  • Выберите Данные > Таблицы данных.
  • Для ячейки ввода столбца: выберите пустую ячейку. В загружаемом файле выделена ячейка D11
  • Нажмите "ОК".
  • После того как на предыдущем шаге выбран вариант "ОК", вставляется таблица, в которой автоматически заполняются 1000 симуляций.

Сводная статистика

После запуска моделирования пришло время собрать сводную статистику. Это можно сделать несколькими способами. В этом примере я использовал функцию СЧЁТЕСЛИ(), чтобы определить процент убыточных симуляций и вероятность получения прибыли более 1 миллиона долларов. Как и ожидалось, вероятность более 1 миллиона долларов колеблется около 50%. Это связано с тем, что мы использовали кривые нормального распределения, которые равномерно распределены вокруг среднего значения, равного 1 миллиону долларов. Вероятность потери денег составляет 4,8%. Это было получено с помощью функции COUNTIF() для подсчета симуляций, которые были меньше нуля, и деления на 1000 полных итераций.

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