Lucas Noah The stock trading system often seems complex and arbitrary. It’s important to have it tested. Traders can assess the risk, consistency, and benefits of the trade. What process would you use for it?
What is Monte Carlo Simulation?
The Monte Carlo Simulation method is an excellent tool for this. It evaluates possibilities through random numbers and simulates all possible outcomes.
This simulation is most often used in different disciplines such as trade finance, science, engineering, and supply chain management; especially, in cases with too many variables at play. In this article, we discuss the intricacies with an example of using a Monte Carlo simulation for stock trading systems.
The idea is to use as many numbers as possible. This increases the number of probable outcomes depending on the random numbers. You would understand the changes in trade opportunities as variables change.
Monte Carlo Simulations obtain the statistical properties of any specified process. It uses repeated random sampling and randomized simulated trade sequences to evaluate the statistical properties of trading systems.
The Monte Carlo Simulation Formula
Distribution Curves
Following are the major distribution curve systems you can use to set up the formula:
Uniform Distribution
According to a uniform distribution, the likelihood of the minimum and maximum are the same. A uniform distribution looks like a rectangle.
Normal (Gaussian) Distribution
This is a standard bell-shaped curve. This formula can be described as evenly distributed on each side with the same median and mean and no skewness. The name of the function may be named differently depending on the version.
Lognormal Distribution
Here, the logarithm and the mean & standard deviation are normally distributed. It has a setup similar to a normal distribution, but the standard deviation and mean variables represent the logarithm.
Poisson Distribution
This Deviation is underutilized. Though a Poisson distribution is a better fit for many models, many people use a normal distribution instead. The appropriate description for this is a large distribution near the beginning that quickly dispels into a long tail on one side.
The idea here is to demonstrate multiple choices available for a Monte Carlo Simulation. It’s irrational to assume that the best fit for data modelling is a normal distribution curve. You can check out the Statistical Functions section in the Excel workbook to learn more.
Running a Monte Carlo Simulation in Microsoft Excel
We are assuming a model with 1,000 iterations using a normal distribution to demonstrate:
Input Variables
A normal distribution setup requires 3 variables; mean, standard deviation, and probability. In the first step, we will manage the standard deviation and mean. For this example, we are assuming a financial forecasting scenario consisting of Revenue, Fixed and Variable Expenses.
Here, Revenue minus Variable Expenses minus Fixed Expenses equals profit. Fixed expenses are sunk costs in plant and equipment without any assumed distribution curve. Only Revenue and Variable Expenses have assumed distribution curves.
First Simulation
The example used here refers to the settings for Revenue. You can paste the formula for variable expenses into cell D6. For Expenses and Revenue, we are using the function NORM.INV() with the following parameters:
- Probability = the function RAND() to elicit a random number based on the other criteria within the distribution
- Mean = The mean used in step 1.
- Standard Deviation = The Standard Deviation used in Step 1.
Since RAND() is used as the probability, a random probability is generated at refresh. We will use this to our advantage in the next step.
1,000 Simulations
You can do more than 1,000 iterations in multiple ways. Using the formula from step #2 is the simplest way. Afterwards, you can simply copy it and paste it 1,000 times. This is a very simple method, but it isn’t elegant or fancy. With this, we move to the tables.
- First, we list the numbers 1 to 1,000 in rows to create the outline of the table.
- Reference the first iteration in the next column.
- Highlight the area where you want to house the 1,000 iterations.
- Select Data > Data Tables
- Select a blank cell for Column Input
- Select OK
- A table that auto-populates the 1,000 simulations is inserted once OK is selected.
Summary Statistics
Once you have run the simulations, you need to gather the summary statistics. You can do this in various methods. This example uses the COUNTIF() function for determining the percentage of unprofitable simulations and the probability of more than $1 Million in profit. The probability of more than $1M is around 50%, as expected. Since we used normal distribution curves that are distributed evenly around the mean, this is natural. 4.8% is the probability of losing money. The COUNTIF() function was used to count simulations less than zero, and dividing them by the number of iterations (1,000) gets us the 4.8% probability.
You can follow the steps discussed in this example to create and run your own Monte Carlo Simulation.