Introduction | Exploring the World of Probability with Excel
Excel is widely used for spreadsheets, data analysis, and creating charts.
In business settings, Excel tools sometimes evolve around the particular needs and working habits of individual users. Over time, they can become so personalized that only their creators fully understand how they work.
For personal research, analysis, and decision-making, however, Excel can be an extraordinarily powerful tool.
What I find particularly fascinating is the use of random numbers and VBA (Visual Basic for Applications) to perform stochastic simulations.
By generating random numbers and repeatedly performing calculations under specified conditions, we can simulate a wide variety of phenomena.
Depending on the calculations involved and how the program is written, it is possible to perform tens of millions, or even hundreds of millions, of iterations.
And the value of these calculations goes far beyond obtaining a single numerical result.
By examining the many outcomes that can arise under the same conditions, we can explore the probabilistic structure underlying a phenomenon.
In this article, I will begin with my own discovery of Excel’s potential and explore what we can learn from Markov chains, Monte Carlo methods, and probabilistic simulation.
1. How Studying Finance Revealed Excel’s Potential to Me
I first discovered what Excel was truly capable of when I began studying finance in a graduate program for working professionals.
The program covered a broad range of subjects, including business administration and economics. Among them, I was particularly drawn to finance, with its extensive use of mathematics.
It was there that I encountered the concept of a random walk, along with the possibilities of using Excel to explore it.
A random walk is a process in which a position or state changes through a sequence of movements determined by probabilities.
For example, imagine a point on a number line that moves one step to the right or left each time a coin is tossed.
The direction of each step is determined randomly, and the point’s position changes as the process continues.
Using Excel to generate random numbers, we can simulate this movement.
If we repeatedly run the simulation from the same starting point, different paths emerge.
In one trial, the point may move far from its starting position. In another, it may return close to where it began.
By repeating the experiment, we can investigate how frequently the point reaches different positions after a given number of steps.
I found this deeply fascinating.
By repeating individual movements governed by probability, we could observe the distribution of outcomes that emerged from the process as a whole.
Since then, using Excel to represent social phenomena and human activities as mathematical models, and analyzing them statistically, has become one of my hobbies.
I have even had the opportunity to present the results of such work at an international medical conference.
Excel had become a tool that allowed me to explore familiar phenomena through mathematics.
2. Using Random Numbers and VBA for Repeated Calculations
Excel includes functions for generating random numbers.
For example, the RAND function generates pseudorandom numbers greater than or equal to zero and less than one.
VBA also provides functions such as Rnd for generating pseudorandom numbers.
Pseudorandom numbers are sequences of values produced by a defined computational procedure. With a suitable random number generator, we can use them to simulate phenomena whose outcomes vary according to probability.
VBA adds the ability to design our own computational procedures using variables, conditional statements, loops, and other programming structures.
For example, we can construct a model in which an event occurs with a specified probability and repeat the experiment 10,000 or even one million times.
By aggregating the results, we can examine how frequently the event occurs and how much the outcomes vary.
We can also increase the number of trials and observe how our estimates change.
What Should We Extract from Hundreds of Millions of Calculations?
For relatively simple calculations, VBA can execute hundreds of millions of iterations. The time required depends on the complexity of the calculations, the implementation, and the computer’s performance.
An important question is what information we actually need to record.
Suppose we want to determine how many times a particular event occurs during 100 million trials.
We can use a VBA variable to count occurrences and write the final count or proportion to a worksheet.
Excel worksheets have a limit on the number of rows they can contain, but the number of iterations performed within a program is a separate matter.
We can also calculate statistics such as means and variances by processing the results incrementally as each trial is completed.
What should we calculate? What information should we retain? What results do we actually want to obtain?
Being able to design these processes ourselves is one of the most interesting aspects of VBA programming.
Random Number Quality and Reproducibility
In probabilistic simulation, the quality of the random numbers matters alongside computational speed.
Excel’s RAND function and VBA’s Rnd function use different pseudorandom number generation mechanisms.
Both can be used for learning about probabilistic simulation and conducting small-scale numerical experiments. However, each random number generator has its own statistical properties.
For example, traditional implementations of VBA’s Rnd are known to have a period of approximately 16.77 million values.
The period of a pseudorandom number generator is the length of its sequence before that sequence begins to repeat.
When a simulation requires hundreds of millions of consecutive random values, the period and statistical properties of the generator can affect the reliability of the results.
For such applications, selecting a random number generator appropriate to the scale and purpose of the calculation becomes important.
Reproducibility is another consideration.
VBA’s Rnd allows the random sequence to be reproduced through appropriate management of its initial value, or seed. Excel’s worksheet RAND function, on the other hand, updates its values when recalculated and provides no ordinary function argument for specifying a seed.
Being able to reproduce the same random sequence is useful when comparing results before and after modifying a program.
The number of trials, random number quality, computational methods, and reproducibility all contribute to the reliability of probabilistic simulations.
For readers interested in programming, I have also written a related article.
Related article : Why I Still Recommend Excel VBA as a First Programming Environment ★ From Simple Code to Better Decisions
3. Understanding Changes in State Through Markov Chains
One way to represent probabilistic phenomena is through Markov chains.
A Markov chain is a probabilistic model in which the current state provides the information needed to determine the probabilities of transitioning to the next state.
For example, suppose we classify the weather into two states: sunny and rainy.
We assign different probabilities for tomorrow’s weather depending on whether today is sunny or rainy.
By generating random numbers according to these probabilities, we can simulate how the weather changes from day to day.
If we run the simulation over a long period, we can investigate how frequently sunny and rainy conditions occur under the specified transition probabilities.
The random walk introduced earlier can also be modeled as a Markov chain when the next movement is determined by the current state and specified probabilities.
Markov chains can be applied to a wide variety of processes involving changes in state, including phenomena in society, economics, and human behavior.
For example, we might investigate how individuals within a population move between different states over time according to specified transition probabilities.
How should the states be defined? What probabilities should govern transitions between them?
By answering these questions, we can represent the phenomenon as a system of probabilistic state transitions.
Excel VBA then allows us to run the model repeatedly and observe the results.
4. Exploring Many Possible Outcomes with Monte Carlo Methods
A Markov chain is a probabilistic model describing changes in state. Monte Carlo methods, by contrast, are computational techniques that use repeated random sampling to obtain numerical estimates.
Consider a problem whose outcome depends on several uncertain factors.
We assign probability distributions to those factors and use random numbers to generate their values.
We then perform the calculation and obtain an outcome.
Repeating this process produces a collection of possible outcomes.
By analyzing them, we can estimate the average result, its variability, the probability of falling within a particular range, and the frequency of extreme outcomes.
Monte Carlo methods can also provide numerical approximations for problems that are difficult to solve analytically.
Markov chains and Monte Carlo methods can be used together.
By repeatedly simulating transitions governed by a Markov chain, we can examine the paths and outcomes produced by the model.
With Excel VBA, we can combine random number generation, conditional processing, repeated calculations, and statistical aggregation within a single program.
We can then display the results in Excel worksheets and examine them through tables and charts.
The ability to build a model from our own ideas, run it, and directly observe what happens is one of the great attractions of Excel VBA.
5. From a Single Observed Reality to Many Possible Outcomes
What we observe in the real world is what actually happened.
Suppose a stock price rises substantially over the course of a year.
We can observe the price movements that occurred during that period.
But if we represent price changes using a probabilistic model, we can simulate alternative paths beginning from the same starting point.
Along one path, the price may rise. Along another, it may fall.
By generating many such paths and examining their outcomes, we can investigate the distribution of possible price changes and how frequently different outcomes occur.
We can expand our perspective from a single observed outcome to the many outcomes that could arise under the same model.
I believe this is one of the most fascinating aspects of probabilistic simulation.
What Can We Learn by Building a Model?
Constructing a probabilistic model requires us to identify the elements that make up a phenomenon.
What states or variables should we define?
What probability distributions govern their behavior?
How are the variables related to one another?
And what results do we want to evaluate?
By answering these questions, we express our understanding of the phenomenon in a form that can be calculated.
Running the model allows us to investigate the outcomes implied by our assumptions.
Changing the conditions or probability distributions then allows us to observe how those outcomes change.
How Much Confidence Can We Place in the Results?
This brings us to another important question: how reliable are the results of our simulation?
Suppose we perform 100 million trials and estimate the probability of a particular outcome.
The reliability of that estimate depends on several factors: the validity of the model, its underlying assumptions, the quality of the random numbers, and the correctness of the calculations, as well as the number of trials.
A simulation based on a finite number of random samples also involves statistical sampling error. Increasing the number of trials and examining the stability of the estimates can help us assess that uncertainty.
The meaning of the results also depends on how closely the model’s assumed probability distributions reflect the phenomenon being studied.
For example, the estimated probability of a major decline in stock prices will depend on the probability distribution used to represent future price movements.
Comparing model results with observed data and examining the effects of alternative assumptions can help us evaluate the model’s validity.
At this point, another important idea emerges.
The purpose of simulation develops from performing large numbers of calculations into using their results to understand the phenomenon itself.
Which conditions have the greatest influence on the outcomes?
Which assumptions cause substantial changes in the distribution of results?
How much precision is needed to make a useful judgment about the problem we are investigating?
By pursuing such questions, we can deepen our understanding of the phenomenon.
This entire process is what makes probabilistic simulation with Excel so appealing to me.
We turn our ideas into a model, run that model repeatedly, and examine the results.
Sometimes, relationships we had never previously noticed begin to emerge.
Ideas that once existed only in our minds become countless simulated outcomes before our eyes, and those outcomes help us develop our thinking further.
6. Expanding the Possibilities Beyond Excel
Once you become interested in probabilistic simulation using Excel, many other areas of study begin to open up.
In finance, for example, we can model uncertainties such as future asset prices and interest rates.
Taking this approach further leads to the concept of real options.
Real options analysis examines the economic value of retaining the flexibility to make investment or business decisions after observing how future conditions develop.
By incorporating uncertain future conditions and the actions available at different points in time, we can compare the value of alternative strategies.
Excel VBA can also be used to explore models of this kind.
Another technique you may encounter as you advance in probabilistic computation is MCMC, or Markov Chain Monte Carlo.
MCMC methods construct a Markov chain whose stationary distribution is the target probability distribution and use the chain to generate samples.
These methods are useful for numerical inference involving complex probability distributions that are difficult to work with directly.
Understanding Markov chains and Monte Carlo methods provides a foundation for learning how MCMC works.
Computing Environments in 2026
Today, Python in Excel also makes it possible to use Python within supported versions of Microsoft 365 Excel.
Libraries such as NumPy and SciPy can be used for numerical computing and statistical analysis, with Python calculations executed in Microsoft’s cloud environment.
Independent Python environments offer additional libraries for working with complex probabilistic models and large-scale simulations.
Specialized simulation software is another option, depending on the application.
You can begin by exploring probabilistic models and programming through Excel VBA, then expand into these other environments as your interests and objectives develop.
The experience of setting up conditions on an Excel worksheet, running a program, and observing its results remains valuable when working with other computational tools.
The Pleasure of Learning from Books
There are many practical guides and technical books on Excel VBA and probabilistic simulation.
You can begin with an introductory VBA programming book and gradually move into probability theory and finance according to your interests.
Books on finance often contain terminology from business administration and financial economics, which may seem unfamiliar at first.
Learning the foundations of finance, however, can give you both new ways to use Excel and knowledge that helps you understand modern economies and societies.
Exploring unfamiliar subjects one step at a time is one of the pleasures of independent learning.
Conclusion | How Excel Can Change the Way You See the World
By combining Excel’s random number functions with VBA programming, we can construct probabilistic models of familiar phenomena and run repeated simulations.
We can use Markov chains to represent changes in state and Monte Carlo methods to explore many possible outcomes.
From there, we can extend our studies into real options analysis and MCMC.
For me, discovering this side of Excel was a remarkable experience.
It allowed me to begin with a single observed outcome and explore the probabilistic structure that might underlie it.
By constructing models, running simulations, and examining the results, I could gradually develop a deeper understanding of the phenomena that interested me.
Along the way, we develop both the ability to construct models and the ability to evaluate how much confidence we can place in their results.
The fascination I discovered while studying finance in graduate school remains just as strong today.
Excel offers an accessible environment for enjoying this kind of intellectual exploration.
If you already use Excel, never exploring random numbers and VBA would be a tremendous missed opportunity.
If Markov chains, Monte Carlo methods, real options analysis, or MCMC interest you, I encourage you to try experimenting with them in Excel.
Behind a single observed reality lies an extraordinary range of possibilities.
When you begin exploring those possibilities through models you have built yourself, you may discover entirely new ways of looking at the world.
It really is fascinating.
Want to know more about the author and the thinking behind this site?
→ About Rikutsu-Kone-Taro
