R1C1: Excel Musings – Monte Carlo Simulation Without VBA

THIS ARTICLE IS THE FIRST IN A SERIES – PART OF A NEW REGULAR COLUMN, FEATURING THE MUSINGS OF DAN MAYOH…

As professionals in the actuarial field many of us use spreadsheets – predominately Microsoft Excel – on a daily basis as a key tool in exercising our craft. Two key observations I’ve made in my travels regarding financial professionals who use Excel are:
(i) the level of Excel knowledge varies widely amongst those who use it every day; and
(ii) even the very best financial modellers among us are still able to constantly learn more.

Excel and spreadsheet education is a passion of mine, both learning and sharing. With that in mind I am trialling a regular monthly or bi-monthly section in our magazine, titled ‘R1C1 – Excel Musings’. I will be choosing one key topic / feature / tip to shine a light on each time, beginning right here with an interesting approach to Monte Carlo simulation in Excel. I have an outline of another half a dozen or so topics to roll out in future editions. But I would also love to hear from readers about what you would like to see covered in this column, and if you think the detail level is too technical, just right, or not technical enough. So if you have comments, please feel welcome to drop me an email. And now, without further delay…

shutterstock_8795887MONTE CARLO SIMULATION IN EXCEL – A NON-VBA APPROACH

As actuaries, examining probability problems by stochastic trial is something many of us do. Whilst Excel isn’t the only tool for this, there are times when it is the tool we wish to use, running a trial over and over again that is built up from Excel’s RAND() function. To begin to draw meaningful conclusions about the probability of our event happening, multiple trials are needed, typically 10,000 or more. A VBA loop is one way to achieve this, but what if we don’t wish to use VBA? That means we need our worksheets in Excel to replicate the necessary trial calculations. Depending on the amount of worksheet space needed for one trial, this may be easy to achieve via the dragging/copying of formulas, or it may require us to use a trick up our sleeves. To illustrate, I’m going to use an example based on dealing from a deck of playing cards. I could have chosen an example from the life insurance space that uses mortality tables, but if you are like me you find playing cards more interesting.

Let’s say we are asked to build a stochastic model to answer “what’s the probability of being dealt five cards all from the same suit?” (in poker parlance, a Flush, Straight Flush or Royal Flush). We can actually model a single trial of this in Excel quite compactly. One such way is shown in Diagram 1, using 58 cells across a single row. Cell BF3 will return 1 if all cards are the same suit, or 0 otherwise. (How and why these 58 cells arrive at the answer is not important for this discussion, but if you’re curious, the formulas are provided in the diagram, and you should be able to figure out why it works with a bit of careful thinking.) We can then simulate multiple trials by dragging cells A3:BF3 down to row 10,002, to give us 10,000 trials. The observed probability will then be the sum of column BF for the 10,000 trials, divided by 10,000.

Diagram 1: Simulating a trial of “If I deal 5 playing cards at random, will they all be the same suit?” in 58 cells.

diagram-1_cmy

No trick there. But what if we were asked a more complicated question, where one trial required a block of perhaps 100 x 58 cells to model, instead of a single 1 x 58 row of cells? For example, “what’s the probability of being dealt a five card poker hand that ranks as three of a kind or stronger?” (So this would also include three of a kind, straights, flushes, full houses etc.)

That can be modelled in Excel, but it will typically require a block of cells (or a very, very wide row). Hence it is not practical to replicate the calculation 10,000 times. Is there another option besides VBA? Yes. We can achieve the simulation of multiple trials using Excel’s Data Table* functionality.

USING A DATA TABLE TO SIMULATE MULTIPLE RANDOM TRIALS

Normally, a data table in Excel is used for sensitivity testing. It’s a way of substituting multiple values in the place of an assumption cell, and seeing the result for each case in a single table. What we will do in this case however is have the data table point to a blank cell, and return at each different row of the table the Boolean outcome of our single trial calculation in the form of a 0 or 1. This is shown in Diagram 2. By then averaging the results of the entire Data Table, we can start to estimate a probability for the event we are testing.

Diagram 2: Simulating 1000 random trials of a complex calculation in cells D106:D1105, using a Data Table.

diagram-2_cmy

Why does this work? Because RAND() is a volatile function, Excel recalculates each RAND() function and generates new random numbers in the calculation block for each scenario in the Data Table. Here our objective in using the Data Table is not to substitute in to an assumption cell a new value from the left column of our table (hence why we can point the table to a blank cell), but rather to force Excel to calculate another ‘scenario’, meaning we get a new set of numbers from our RAND() functions, which is equivalent to a new trial of the situation we are testing.

Using this method, we are able to simulate multiple stochastic trials without using VBA, whilst only needing to model once the block of cells required to simulate a trial. In this manner, 10,000 trials can be simulated quite easily and efficiently, without the need to use more than a relatively small section of the Excel worksheet.

shutterstock_5878876So there we have it – an alternative approach to Monte Carlo simulation in Excel without the need for VBA or repetitive blocks of calculations. And if you’re interested in actually building a stochastic model to answer the second question posed and wish to test it, the deterministic answer is 6,219/216,580 or approximately 2.87%.

* If you’re unfamiliar with Data Tables, they are worth knowing about, although should be used very sparingly in professional models due to the large calculation overhead that comes with them. They can be accessed via the keyboard shortcuts of Alt-D-T or Alt- A-W-T. Unfortunately, Excel’s built in Help is sparse on information about how to set up Data Tables, but a quick google search for ‘Excel Data Table’ should give you all of the information you need.

CPD: Actuaries Institute Members can claim two CPD points for every hour of reading articles on Actuaries Digital.