The past couple posts have been qualitative in nature and I thought I would spend this time to discuss a foundational component of how I modeled the World Cup, about 5,000 times per model version, discussing how to simulate an event incorporating an element of chance. I understand not all of you will be interested in sports. And I understand soccer may not be your cup of tea so I’ll do a couple examples for different facets of life. Modeling randomness can be a great tool. Sports. Personal Finance. The weather. Chance is everywhere. Modeling randomness can be learned and practiced! In the end it will allow you to make better decisions given certain assumptions. The assumption setting is a story for another day and most likely requires some background research. That being said many of us don’t even make decisions based off of what we believe in! Isn’t that foolish?
There will be a financial example a tad later but we will begin with some soccer simulations. Let’s start with one matchup, a battle of some of the big boys in England, Arsenal and Liverpool. In the below setup we are accounting for three outcomes: 1) An Arsenal Win, 2) A Liverpool Win, and 3) A Tie. In column F you can see the given probabilities of those outcomes. In the second chart I show the formulas in each cell (how you would actually type them in). A few things to notice:
- The important non zero inputs are F3 and F4. This is where we are getting the win probabilities for Arsenal and Liverpool respectively. The tie is the complement of the sum of those two win percentages.
- You can think of the cumulative probabilities as just stacking the three probabilities on top of each other step by step (you’ll see why we need this soon)
- We are giving Arsenal a 45% chance to win, Liverpool a 25% chance, and the resulting 30% for a tie (this may be slightly biased as I’m an avid Arsenal fan).
With the formulas shown:
Once we have our initial assumptions we need to find a framework to simulate. In the next couple of panels we will utilize the rand() function in Excel. With this function Excel randomly generates a number greater than or equal to 0 and less than 1. We are going to assign a result (Arsenal Win, Liverpool, Win, or Tie) based on the random number generated.
Where do the cumulative probabilities come into play? We will utilize our cumulative probabilities for the mapping between the random number and the result. If the random number is less than .45 that will be an Arsenal win. If it’s less than .7 but greater than or equal to .45 that will be a Liverpool win. And if greater than or equal to .7 that will be a tie. Mathematical mappings could be stated as:
[0 , .45) -> An Arsenal Win
[.45 , .7) -> A Liverpool Win
[.7 , 1) -> A Tie
In this example we have five simulated games but the same principles apply to larger samples. I want to implore you to look into the Index() and Match() functions. While they can resemble a VLookup() function (my third blog post has more information on that function if you’re interested) their combination enables more versatility.
Couple things to tinker around with:
- Go to an empty cell in the spreadsheet and click the Delete key. The random numbers should change and thus will alter the results. If the random numbers aren’t changing check to see if your calculations are set to Manual under Calculation Options (highlighted below).
- View how the results change every time you recalculate the cells.
- Now try to run it for 2,000 simulations. Do the results seem more predictable?
Now we’re going to take the same principles and apply them to a theoretical index fund. Here instead of assigning probabilities to a certain team winning or losing we’ll be assigning them to a certain gain/loss of the index over the year. We’ll then apply each year’s return/loss to the prior years and eventually get to the end of year 15. Our goal in this example is to capture the ending value of the 15th year for 1,000 different simulations. Depicting those 1,000 ending values in a histogram would be marvelous!
Hopefully the probability table below looks familiar. Instead of having three game outcomes, as in the soccer example, we have six possible annual returns, in column E. In column I we have the ending year index value. In this example we are building on the prior year’s fund value. For example the end of year three is, in the example below, 1.15*1.09*.85 = 1.07 (cell I15). Remember we’re interested in year 15 and also keep in mind this is just one simulation; our goal is to look at 1,000 simulations of the 15th year! I decided to name the 15th year ending fund value, “Cumulative15Yr”. Why do you think that specific cell is named?
We need to somehow record 1,000 simulations! Ugh, that’s so tough! Wait, what tool would be useful for doing a very very very repetitive task over and over and over and over again? You know this, think! Back on blog post 2, “Simple Loop in VBA”, we looked at the construction of such a beastly, time saving, and dominating tool.
Instead of copying the 15th year 1,000 times in a column somewhere let’s let the loop do the work for us, and use that saved time to go make a snack (you deserve it). And remember Pro Tip: Name your important cells! In this case it was the 15th year fund value. I, just a personal preference, like to name the cell that will be our anchor to list out the results, wait for it…. Anchor. Genius I know.
In this VBA loop below we record the 15th year fund value, recalculate the random variables to get a new path of our fund values, and then once again record the 15th year fund value. The code is so tiny and yet so powerful.
Execute the code and you’ll see something like this appear below the Anchor cell, recording 1,000 ending fund values:
And the very last part we’ll highlight our returns and make a histogram:
There are all sorts of ways to slice and dice this data but this process allows you to see methodically if you believe A) that the index returns have that given distribution then B) you’ll get a distribution of the 15th year fund value looking something like what we obtained.
Fun Tinkering/Thought Ideas:
- Look at the ending fund value over a longer time horizon like 20, 30, or 50 years. How do you think that’ll affect the distribution?
- Try doing 5,000 simulations instead of 1,000. How did that change things?
- Mess with the probabilities and returns.
- Include a large loss but low probability event.
Questions/Feedback can be sent to firstname.lastname@example.org and if you sign up on the email list I can send you a pdf of The Bet is Round.
It does not matter how slowly you go as long as you do not stop. – Confucius