Modeling Stock Prices Using Monte-Carlo Simulation and Excel

Modeling Stock Prices Using Monte-Carlo Simulation and Excel

Seifedine Kadry (American University of the Middle East, Kuwait) and Abdelkhalak El Hami (INSA de Rouen, France)
DOI: 10.4018/978-1-4666-9885-7.ch008
OnDemand PDF Download:


Monte Carlo simulation or experiments is a computerized mathematical technique that allows people to account for risk in quantitative analysis and decision making. Risk analysis is part of every decision we make. We are constantly faced with uncertainty, ambiguity, and variability. The technique is used by professionals in such widely disparate fields as finance, project management, energy, manufacturing, engineering, research and development, insurance, oil & gas, transportation, and the environment. The technique was first used by scientists working on the atom bomb; it was named for Monte Carlo, the Monaco resort town renowned for its casinos. Since its introduction in World War II, Monte Carlo simulation has been used to model a variety of physical and conceptual systems. Monte Carlo simulation furnishes the decision-maker with a range of possible outcomes and the probabilities they will occur for any choice of action... It shows the extreme possibilities—the outcomes of going for broke and for the most conservative decision—along with all possible consequences for middle-of-the-road decisions. Monte Carlo simulation performs risk analysis by building models of possible results by substituting a range of values—a probability distribution—for any factor that has inherent uncertainty. It then calculates results over and over, each time using a different set of random values from the probability functions. Depending upon the number of uncertainties and the ranges specified for them, a Monte Carlo simulation could involve thousands or tens of thousands of recalculations before it is complete. Monte Carlo simulation produces distributions of possible outcome values. By using probability distributions, variables can have different probabilities of different outcomes occurring. Probability distributions are a much more realistic way of describing uncertainty in variables of a risk analysis. In this chapter, a history of the Monte-Carlo simulation and its mechanism is given. We will study, step by step, how to apply this technique to modeling and predict stock prices in financial market Excel through a real data.
Chapter Preview

Steps In Simulation Study

Before simulation is applied to the problem, a simulation analysis needs to be conducted to assure the changes will be beneficial. This section will show how to perform a proper simulation analysis through different steps (Banks et al., 2005; Cellier and Kofman, 2006):

Step 1: Formulate the Problem

This step is vital. For simulation to be effective, it needs to solve the right problem. In this step, we take a look to the problem to understand it then to formulate the problem statement. For example, find the average profit of a product subject to random parameters.

Step 2: Select the Input Variables

Use the problem statement to create variables for the simulation. There are two types of variables. Decision Variables are variables that can be controlled by the programmer, for example fixed cost. Uncontrollable Variables are variables that are random and can be approximated, but not controlled by the programmer, for instance sales quantity, variable cost.

Step 3: Make Constraints on Decision Variables

Assign values and constraints to the variables that can be controlled.

Step 4: Identify the Output Variables

Establish what variables you want the simulation to output. During this step, consider your problem statement. What are you trying to solve? Try to program output variables that are broad enough to help see the problem. For example, profit.

Step 5: Collect Real Data

Gather information from the system to input into the simulation. This can be done using a survey, historical sales…etc.

Step 6: Model Development

In this step, we re-write the problem statement using mathematical equation. For example:

Complete Chapter List

Search this Book: