Метод монте карло в excel
Аналитики могут оценить возможную доходность портфеля разными способами. Исторический подход, который является наиболее популярным, учитывает все уже произошедшие возможности. Однако на этом инвесторам не стоит останавливаться. Метод Монте-Карло – это стохастический метод (случайная выборка входных данных) для решения статистической задачи, а моделирование – это виртуальное представление проблемы. Моделирование методом Монте-Карло объединяет эти две составляющие, чтобы дать нам мощный инструмент, который позволяет нам получать распределение (массив) результатов для любой статистической задачи с многочисленными входными данными, которые отбираются снова и снова.
Ключевые выводы
- Метод Монте-Карло использует случайную выборку информации для решения статистической задачи; а симуляция – это способ виртуально продемонстрировать стратегию.
- В совокупности моделирование методом Монте-Карло позволяет пользователю получить множество результатов для статистической задачи с многочисленными точками данных, которые отбираются повторно.
- Моделирование Монте-Карло можно использовать в корпоративных финансах, ценообразовании опционов и особенно в управлении портфелем и планировании личных финансов.
- С другой стороны, моделирование ограничено тем, что не может учесть медвежьи рынки, рецессии или любой другой вид финансового кризиса, который может повлиять на потенциальные результаты.
Демистификация моделирования Монте-Карло
Моделирование методом Монте-Карло лучше всего понять, если подумать о человеке, бросающем кости. Начинающий игрок, который впервые играет в крэпс, понятия не имеет, каковы шансы выпадения шестерки в любой комбинации (например, четыре и два, три и три, один и пять). Каковы шансы выпадения двух троек, также известных как «жесткая шестерка»? Если бросать кости много раз, в идеале несколько миллионов раз, это даст репрезентативное распределение результатов, которое покажет нам, насколько вероятно, что выпадение шести будет твердой шестеркой. В идеале мы должны запускать эти тесты эффективно и быстро, что и предлагает симуляция Монте-Карло.
Цены на активы или будущая стоимость портфелей не зависят от броска кости, но иногда цены на активы действительно напоминают случайное блуждание. Проблема с рассмотрением только истории состоит в том, что она представляет собой, по сути, только один бросок или вероятный результат, который может быть применим или не применим в будущем. Моделирование методом Монте-Карло рассматривает широкий спектр возможностей и помогает снизить неопределенность. Моделирование Монте-Карло очень гибкое; это позволяет нам варьировать допущения риска по всем параметрам и, таким образом, моделировать диапазон возможных результатов. Можно сравнить несколько будущих результатов и настроить модель для различных анализируемых активов и портфелей.
Краткий обзор
Моделирование методом Монте-Карло может учитывать различные допущения риска во многих сценариях и, следовательно, применимо ко всем видам инвестиций и портфелей.
Применение моделирования Монте-Карло
Моделирование Монте-Карло имеет множество приложений в финансах и других областях. Монте-Карло используется в корпоративных финансах для моделирования компонентов денежного потока проекта , на которые влияет неопределенность. Результатом является диапазон значений чистой приведенной стоимости (ЧПС) вместе с наблюдениями за средней ЧПС анализируемой инвестиции и ее волатильностью. Таким образом, инвестор может оценить вероятность того, что NPV будет больше нуля. Монте-Карло используется для ценообразования опционов, когда генерируются многочисленные случайные пути изменения цены базового актива, каждый из которых имеет соответствующую выплату. Эти выплаты затем дисконтируются до настоящего времени и усредняются для получения фиксированным доходом и производных процентных ставок. Но симуляция Монте-Карло наиболее широко используется в управлении портфелем и личном финансовом планировании.
Использование в управлении портфелем
Моделирование методом Монте-Карло позволяет аналитику определить размер портфеля, который потребуется клиенту при выходе на пенсию, чтобы поддержать его желаемый пенсионный образ жизни и другие желаемые дары и завещания. Она факторы в распределение ставок реинвестирования, инфляция ставок, класс активов возвратов, налоговые ставки, и даже возможных продолжительности жизни. Результатом является распределение размеров портфеля с вероятностями удовлетворения желаемых потребностей клиента в расходах.
Затем аналитик использует моделирование Монте-Карло для определения ожидаемой стоимости и распределения портфеля на зависимость от пути; Стоимость портфеля и распределение активов в каждый период зависят от доходности и волатильности в предыдущем периоде. Аналитик использует различные распределения активов с разной степенью риска, различные корреляции между активами и распределение большого количества факторов, включая сбережения за каждый период и дату выхода на пенсию, чтобы прийти к распределению портфелей с учетом вероятности их поступления. при желаемой стоимости портфеля при выходе на пенсию. Различные уровни расходов и продолжительность жизни клиента могут быть учтены, чтобы определить вероятность того, что у клиента закончатся средства (вероятность разорения или риск долголетия ) до его смерти.
Профиль риска и доходности клиента является наиболее важным фактором, влияющим на решения по управлению портфелем. Требуемый доход клиента зависит от его целей выхода на пенсию и расходов; ее профиль риска определяется ее способностью и готовностью идти на риск. Чаще всего желаемая доходность и профиль риска клиента не синхронизированы друг с другом. Например, приемлемый для клиента уровень риска может сделать невозможным или очень трудным достижение желаемой прибыли. Более того, для достижения целей клиента может потребоваться минимальная сумма до выхода на пенсию, но образ жизни клиента не позволит сэкономить, или клиент может неохотно ее менять.
Пример моделирования Монте-Карло
Давайте рассмотрим пример молодой работающей пары, которая много работает и ведет роскошный образ жизни, включая дорогие праздники каждый год.У них есть цель выхода на пенсию – тратить 170 000 долларов в год (примерно 14 000 долларов в месяц) и оставить своим детям состояние в 1 миллион долларов.Аналитик запускает моделирование и обнаруживает, что их сбережений за период недостаточно для создания желаемой стоимости портфеля при выходе на пенсию;однако это достижимо, еслиудвоитьдолю акций с малой капитализацией (до 50–70% с 25 до 35%), что значительно увеличит их риск.Ни одна из вышеперечисленных альтернатив (более высокая экономия или повышенный риск) не приемлема для клиента.Таким образом, аналитик учитывает другие корректировки перед повторным запуском моделирования.аналитик откладывает их выход на пенсию на два года и снижает их ежемесячные расходы после выхода на пенсию до 12 500 долларов.Полученное распределение показывает, что желаемая стоимость портфеля достижима за счет увеличения доли акций малой капитализации всего на 8 процентов.Имея доступную информацию, аналитик советует клиентам отложить выход на пенсию и незначительно сократить свои расходы, с чем пара соглашается.
Моделирование Монте-Карло позволяет аналитикам и консультантам превращать инвестиционные шансы в варианты выбора. Преимущество Монте-Карло заключается в его способности учитывать диапазон значений для различных входных данных; это также его величайший недостаток в том смысле, что допущения должны быть справедливыми, потому что выход хорош ровно настолько, насколько хорош входные данные. Еще один серьезный недостаток заключается в том, что моделирование Монте-Карло имеет тенденцию недооценивать вероятность экстремальных медвежьих событий, таких как финансовый кризис. Фактически, эксперты утверждают, что симуляция, подобная модели Монте-Карло, не может учитывать поведенческие аспекты финансов и иррациональность, проявляемую участниками рынка. Однако это полезный инструмент для консультантов.
This article was adapted from Microsoft Excel Data Analysis and Business Modeling by Wayne L. Winston.
Who uses Monte Carlo simulation?
What happens when you type =RAND() in a cell?
How can you simulate values of a discrete random variable?
How can you simulate values of a normal random variable?
How can a greeting card company determine how many cards to produce?
We would like to accurately estimate the probabilities of uncertain events. For example, what is the probability that a new product’s cash flows will have a positive net present value (NPV)? What is the risk factor of our investment portfolio? Monte Carlo simulation enables us to model situations that present uncertainty and then play them out on a computer thousands of times.
Note: The name Monte Carlo simulation comes from the computer simulations performed during the 1930s and 1940s to estimate the probability that the chain reaction needed for an atom bomb to detonate would work successfully. The physicists involved in this work were big fans of gambling, so they gave the simulations the code name Monte Carlo.
In the next five chapters, you will see examples of how you can use Excel to perform Monte Carlo simulations.
Many companies use Monte Carlo simulation as an important part of their decision-making process. Here are some examples.
General Motors, Proctor and Gamble, Pfizer, Bristol-Myers Squibb, and Eli Lilly use simulation to estimate both the average return and the risk factor of new products. At GM, this information is used by the CEO to determine which products come to market.
GM uses simulation for activities such as forecasting net income for the corporation, predicting structural and purchasing costs, and determining its susceptibility to different kinds of risk (such as interest rate changes and exchange rate fluctuations).
Lilly uses simulation to determine the optimal plant capacity for each drug.
Proctor and Gamble uses simulation to model and optimally hedge foreign exchange risk.
Sears uses simulation to determine how many units of each product line should be ordered from suppliers—for example, the number of pairs of Dockers trousers that should be ordered this year.
Oil and drug companies use simulation to value "real options," such as the value of an option to expand, contract, or postpone a project.
Financial planners use Monte Carlo simulation to determine optimal investment strategies for their clients’ retirement.
When you type the formula =RAND() in a cell, you get a number that is equally likely to assume any value between 0 and 1. Thus, around 25 percent of the time, you should get a number less than or equal to 0.25; around 10 percent of the time you should get a number that is at least 0.90, and so on. To demonstrate how the RAND function works, take a look at the file Randdemo.xlsx, shown in Figure 60-1.
Note: When you open the file Randdemo.xlsx, you will not see the same random numbers shown in Figure 60-1. The RAND function always automatically recalculates the numbers it generates when a worksheet is opened or when new information is entered into the worksheet.
First, copy from cell C3 to C4:C402 the formula =RAND(). Then you name the range C3:C402 Data. Then, in column F, you can track the average of the 400 random numbers (cell F2) and use the COUNTIF function to determine the fractions that are between 0 and 0.25, 0.25 and 0.50, 0.50 and 0.75, and 0.75 and 1. When you press the F9 key, the random numbers are recalculated. Notice that the average of the 400 numbers is always approximately 0.5, and that around 25 percent of the results are in intervals of 0.25. These results are consistent with the definition of a random number. Also note that the values generated by RAND in different cells are independent. For example, if the random number generated in cell C3 is a large number (for example, 0.99), it tells us nothing about the values of the other random numbers generated.
Suppose the demand for a calendar is governed by the following discrete random variable:
Probability
How can we have Excel play out, or simulate, this demand for calendars many times? The trick is to associate each possible value of the RAND function with a possible demand for calendars. The following assignment ensures that a demand of 10,000 will occur 10 percent of the time, and so on.
Random number assigned
Greater than or equal to 0.10, and less than 0.45
Greater than or equal to 0.45, and less than 0.75
Greater than or equal to 0.75
To demonstrate the simulation of demand, look at the file Discretesim.xlsx, shown in Figure 60-2 on the next page.
The key to our simulation is to use a random number to initiate a lookup from the table range F2:G5 (named lookup). Random numbers greater than or equal to 0 and less than 0.10 will yield a demand of 10,000; random numbers greater than or equal to 0.10 and less than 0.45 will yield a demand of 20,000; random numbers greater than or equal to 0.45 and less than 0.75 will yield a demand of 40,000; and random numbers greater than or equal to 0.75 will yield a demand of 60,000. You generate 400 random numbers by copying from C3 to C4:C402 the formula RAND(). You then generate 400 trials, or iterations, of calendar demand by copying from B3 to B4:B402 the formula VLOOKUP(C3,lookup,2). This formula ensures that any random number less than 0.10 generates a demand of 10,000, any random number between 0.10 and 0.45 generates a demand of 20,000, and so on. In the cell range F8:F11, use the COUNTIF function to determine the fraction of our 400 iterations yielding each demand. When we press F9 to recalculate the random numbers, the simulated probabilities are close to our assumed demand probabilities.
If you type in any cell the formula NORMINV(rand(),mu,sigma), you will generate a simulated value of a normal random variable having a mean mu and standard deviation sigma. This procedure is illustrated in the file Normalsim.xlsx, shown in Figure 60-3.
Let’s suppose we want to simulate 400 trials, or iterations, for a normal random variable with a mean of 40,000 and a standard deviation of 10,000. (You can type these values in cells E1 and E2, and name these cells mean and sigma, respectively.) Copying the formula =RAND() from C4 to C5:C403 generates 400 different random numbers. Copying from B4 to B5:B403 the formula NORMINV(C4,mean,sigma) generates 400 different trial values from a normal random variable with a mean of 40,000 and a standard deviation of 10,000. When we press the F9 key to recalculate the random numbers, the mean remains close to 40,000 and the standard deviation close to 10,000.
Essentially, for a random number x, the formula NORMINV(p,mu,sigma) generates the pth percentile of a normal random variable with a mean mu and a standard deviation sigma. For example, the random number 0.77 in cell C4 (see Figure 60-3) generates in cell B4 approximately the 77th percentile of a normal random variable with a mean of 40,000 and a standard deviation of 10,000.
In this section, you will see how Monte Carlo simulation can be used as a decision-making tool. Suppose that the demand for a Valentine’s Day card is governed by the following discrete random variable:
Probability
The greeting card sells for $4.00, and the variable cost of producing each card is $1.50. Leftover cards must be disposed of at a cost of $0.20 per card. How many cards should be printed?
Basically, we simulate each possible production quantity (10,000, 20,000, 40,000, or 60,000) many times (for example, 1000 iterations). Then we determine which order quantity yields the maximum average profit over the 1000 iterations. You can find the data for this section in the file Valentine.xlsx, shown in Figure 60-4. You assign the range names in cells B1:B11 to cells C1:C11. The cell range G3:H6 is assigned the name lookup. Our sales price and cost parameters are entered in cells C4:C6.
You can enter a trial production quantity (40,000 in this example) in cell C1. Next, create a random number in cell C2 with the formula =RAND(). As previously described, you simulate demand for the card in cell C3 with the formula VLOOKUP(rand,lookup,2). (In the VLOOKUP formula, rand is the cell name assigned to cell C3, not the RAND function.)
The number of units sold is the smaller of our production quantity and demand. In cell C8, you compute our revenue with the formula MIN(produced,demand)*unit_price. In cell C9, you compute total production cost with the formula produced*unit_prod_cost.
If we produce more cards than are in demand, the number of units left over equals production minus demand; otherwise no units are left over. We compute our disposal cost in cell C10 with the formula unit_disp_cost*IF(produced>demand,produced–demand,0). Finally, in cell C11, we compute our profit as revenue– total_var_cost-total_disposing_cost.
We would like an efficient way to press F9 many times (for example, 1000) for each production quantity and tally our expected profit for each quantity. This situation is one in which a two-way data table comes to our rescue. (See Chapter 15, "Sensitivity Analysis with Data Tables," for details about data tables.) The data table used in this example is shown in Figure 60-5.
In the cell range A16:A1015, enter the numbers 1–1000 (corresponding to our 1000 trials). One easy way to create these values is to start by entering 1 in cell A16. Select the cell, and then on the Home tab in the Editing group, click Fill, and select Series to display the Series dialog box. In the Series dialog box, shown in Figure 60-6, enter a Step Value of 1 and a Stop Value of 1000. In the Series In area, select the Columns option, and then click OK. The numbers 1–1000 will be entered in column A starting in cell A16.
Next we enter our possible production quantities (10,000, 20,000, 40,000, 60,000) in cells B15:E15. We want to calculate profit for each trial number (1 through 1000) and each production quantity. We refer to the formula for profit (calculated in cell C11) in the upper-left cell of our data table (A15) by entering =C11.
We are now ready to trick Excel into simulating 1000 iterations of demand for each production quantity. Select the table range (A15:E1014), and then in the Data Tools group on the Data tab, click What If Analysis, and then select Data Table. To set up a two-way data table, choose our production quantity (cell C1) as the Row Input Cell and select any blank cell (we chose cell I14) as the Column Input Cell. After clicking OK, Excel simulates 1000 demand values for each order quantity.
To understand why this works, consider the values placed by the data table in the cell range C16:C1015. For each of these cells, Excel will use a value of 20,000 in cell C1. In C16, the column input cell value of 1 is placed in a blank cell and the random number in cell C2 recalculates. The corresponding profit is then recorded in cell C16. Then the column cell input value of 2 is placed in a blank cell, and the random number in C2 again recalculates. The corresponding profit is entered in cell C17.
By copying from cell B13 to C13:E13 the formula AVERAGE(B16:B1015), we compute average simulated profit for each production quantity. By copying from cell B14 to C14:E14 the formula STDEV(B16:B1015), we compute the standard deviation of our simulated profits for each order quantity. Each time we press F9, 1000 iterations of demand are simulated for each order quantity. Producing 40,000 cards always yields the largest expected profit. Therefore, it appears that producing 40,000 cards is the proper decision.
The Impact of Risk on Our Decision If we produced 20,000 instead of 40,000 cards, our expected profit drops approximately 22 percent, but our risk (as measured by the standard deviation of profit) drops almost 73 percent. Therefore, if we are extremely averse to risk, producing 20,000 cards might be the right decision. Incidentally, producing 10,000 cards always has a standard deviation of 0 cards because if we produce 10,000 cards, we will always sell all of them without any leftovers.
Note: In this workbook, the Calculation option is set to Automatic Except For Tables. (Use the Calculation command in the Calculation group on the Formulas tab.) This setting ensures that our data table will not recalculate unless we press F9, which is a good idea because a large data table will slow down your work if it recalculates every time you type something into your worksheet. Note that in this example, whenever you press F9, the mean profit will change. This happens because each time you press F9, a different sequence of 1000 random numbers is used to generate demands for each order quantity.
Confidence Interval for Mean Profit A natural question to ask in this situation is, into what interval are we 95 percent sure the true mean profit will fall? This interval is called the 95 percent confidence interval for mean profit. A 95 percent confidence interval for the mean of any simulation output is computed by the following formula:
In cell J11, you compute the lower limit for the 95 percent confidence interval on mean profit when 40,000 calendars are produced with the formula D13–1.96*D14/SQRT(1000). In cell J12, you compute the upper limit for our 95 percent confidence interval with the formula D13+1.96*D14/SQRT(1000). These calculations are shown in Figure 60-7.
We are 95 percent sure that our mean profit when 40,000 calendars are ordered is between $56,687 and $62,589.
A GMC dealer believes that demand for 2005 Envoys will be normally distributed with a mean of 200 and standard deviation of 30. His cost of receiving an Envoy is $25,000, and he sells an Envoy for $40,000. Half of all the Envoys not sold at full price can be sold for $30,000. He is considering ordering 200, 220, 240, 260, 280, or 300 Envoys. How many should he order?
A small supermarket is trying to determine how many copies of People magazine they should order each week. They believe their demand for People is governed by the following discrete random variable:
ЗАКАЗАТЬ РЕШЕНИЕ ЗАДАЧ МЕТОДОМ МОНТЕ-КАРЛО
Один из самых прикладных методов статистической оценки риска. К нему нужно отнестись с большим участием. В данной статье будет рассмотрен пример имитационного моделирования с использованием данного подхода.
Метод Монте-Карло получил своё название за то, что предназначен осуществить оценку предельно случайных событий. А что, как ни казино, которых в Монте-Карло много, связано со случайностью больше всего?
В процессе работы нам понадобится «генератор случайных чисел» из MS Excel и функция «Описательная статистика».
Оценка риска инвестиционного проекта
Есть следующие условия задачи:
Таким образом, нам нужно оценить три периода – за три года. Запишем все исходные данные в таблицу. Значения, полученные в ячейках D5-X5, имеют формулу для вычисления или есть в условиях задачи. Вы, как экономист, с формулами должны быть знакомы. Обратите внимание на заголовок, выделенный красным цветом на рисунке ниже – «Имитационная модель NCF1». Это говорит о том, что мы имитируем первый год, а всего их будет три на разных листах в MS Excel. На новый лист переключиться внизу окна программы.
Теперь в MS Excel переключаемся на «Данные» и выбираем пункт «Анализ данных».
В появившемся окне выбираем «Генерация случайных чисел». Выполняем генерацию с параметрами, продемонстрированными на картинке ниже, для пункта «Кол-во пользователей».
Параметры будут отталкиваться от среднего значения 250, оно есть в ожидаемых значениях в нашей таблице. Нужно выполнить 1000 генераций. Если вы знакомы со статистикой, то понимаете, что большее количество испытаний даёт более точную оценку. Используя метод Монте-Карло, можно имитировать и 10 000 значений для большей точности.
После мы имитируем все стохастические, то есть, меняющиеся значения по аналогии, как показано выше. Копируем формулы переменных или констант из ячеек D7-X7 под «Результаты имитации» с учетом имитированных значений. Получаем следующий результат.
Как видим, платежи по налогам за имущество, например, являются постоянным значением на весь год, поэтому это значение везде одинаковое, а другие меняются, потому что рассчитываются по формулам, и в эти формулы входят меняющиеся значение, имитированные нами. Не забывайте, что значений в каждом столбце должно быть по тысяче.
Теперь делаем то же самое, но для имитационной модели NCF2.
Это второй год работы проекта. Как видим, под «СКО» процентные соотношения увеличились. Об этом говорится в условии задачи, что налоги и зарплата должны расти каждый год.
Повторяем это действие в третий раз, увеличивая налоги и зарплаты, как говорит условие.
Наибольшую важность в оценке инвестиционного проекта имеет параметр NCF – чистый денежный поток. Копируем все значения NCF на четвертый лист с каждой из трёх предыдущих страниц.
Формула для расчета NPV есть вверху картинки. Используем её. Теперь точно так же заходим в «Данные», жмём на «Анализ данных» и выбираем там «Описательная статистика». Вот, что в появившемся окне вам нужно указать.
Во входном интервале выбирается 1000 полученных значений NPV. Выходной интервал можете выбрать произвольно. На выходе у вас будет таблица со статистическими данными.
Вы, как экономист, должны понимать, о чем говорит каждое значение, если нет, то нужно прочитать отдельную статью или главу учебника. Наша статья о том, метод Монте-Карло применяется с использованием функций MS Excel.
Заключение
Генерация случайных чисел – наше всё. Именно в оценке того, к чему может привести случайность, заключается статистический метод Монте-Карло. Это работает не только в экономике, но и везде, где есть случайность. Можете посмотреть, как это делается, применительно к зоологии в видео ниже.
Метод Монте - Карло придуман почти одновременно с компьютерами. С тех пор он стал популярным инструментом при изучении случайных процессов.
Суть метода: случайный процесс описывается математической моделью с использованием генератора случайных чисел, затем производится многократное моделирование, после чего на основе полученных данных вычисляются вероятностные характеристики рассматриваемого процесса. Метод используется для оценки результатов воздействия на выбранные параметры одного или нескольких случайных факторов, прекрасно демонстрирует тенденции и позволяет оценить среднеожидаемый результат, характерный разброс результатов и риски наступления тех или иных событий. Характерная для метода точность оценки (средне стандартное отклонение 1-2% при быстрой оценке) вполе достаточна для решения практических вопросов управления. Для использования метода Монте-Карло пользователь может не обладать высокой квалификацией, поскольку сам метод позволяет легко получить верный результат даже в сложных случаях. А надстройка "Моделирование Монте - Карло" без проблем устанавливается на любой компьютер, на котором уже установлен MS Excel. Примеры использования надстройки можно посмотреть здесь.
Описание надстройки "Моделирование Монте-Карло"
-
Надстройка "Моделирование Монте-Карло"
- реализует сбор и обработку информации в книге MS Excel при статистическом моделировании по методу Монте-Карло;
- выдает результаты в численной и графической форме на отдельном листе рабочей книги 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.
Блок "Разное"
Запуск надстройки "Монте-Карло"
После того, как все значения установлены, запустите надстройку, нажав:
Замечания
- Так как при моделировании используется команда Пересчитать, то Excel много раз пересчитывает ВСЕ ОТКРЫТЫЕ книги. Чтобы ускорить расчет лучше оставить открытой только книгу с построенной задачей для моделирования.
- Надстройка позволяет вводить данные только с одной и той же страницы книги Excel. Если удобнее организовывать данные так, чтобы они располагались на нескольких страницах, то можно использовать дополнительную общую страницу, на которую вынести ссылки на все нужные целевые и прочие ячейки, расположенные на других страницах книги.
Основная версия надстройки работает в 32-bit и 64-bit версиях MS Excel начиная с MS Office 2007. Более старые версии MS Excel не поддерживаются.
Файл надстройки имеет название "Monte-Carlo 20.12.xlam". Его можно запустить как обычный файл MS Excel (при этом нужно согласиться с запуском макросов надстройки), либо подключить как стандартную надстройку MS Excel.
Метод Монте-Карло назван в честь района Монако, известного своими казино. В этой статье мы будем использовать метод Монте-Карло для изучения зависимости эффективности ставок от фактора везения.
Что такое метод Монте-Карло?
Метод Монте-Карло используется в тех случаях, когда применение других математических подходов сопряжено с большим количеством трудностей.
Суть метода заключается в многократном использовании генератора случайных чисел. На основе полученных данных вычисляются вероятности в рассматриваемом процессе.
Он особенно полезен тем бетторам, которые в меньшей степени осведомлены о традиционных методах статистического тестирования, поскольку не требует обширных математических знаний.
Анализ эффективности ставок
Допустим, есть история из 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 с моделированием Монте-Карло. Его можно модифицировать и использовать для анализа эффективности своей игровой стратегии.
Читайте также: