Using Monte Carlo Simulation Analysis for Finance

Using Monte Carlo Simulation Analysis for Finance

The long held assumptions that risk assessment metrics and risk premiums don’t change much have been dramatically challenged. The rapid, global meltdown of financial markets and the real economy in 2008 and 2009 has shown that risk premiums can change quickly in both developed and emerging businesses and markets.

As a result, expected value and base case valuation techniques that have dominated much of the history of modern corporate finance may now make a (long overdue) shift towards more probabilistic approaches to financial valuation and modeling.

Most probabilistic financial modeling software are based on Monte Carlo analysis, a risk analysis tool used for managing uncertainty. The Financial Modeling Guide covered Monte Carlo analysis in a previous article on Financial Simulation Analysis & Modeling, but it is worthwhile to re-visit this topic from a beginner’s perspective for financial analysts new to probabilistic based financial modeling and valuation techniques.

In simple terms, Monte Carlo analysis combines distributions, and thereby propagating more than just summary statistics. Rather than analytic calculations, Monte Carlo analysis uses intensive random number generation.

Let us use a simple equation to illustrate the Monte Carlo analysis:

Risk = Probably x Consequence

Instead of just solving the equation using simple arithmetic, Monte Carlo analysis draws 2 random numbers from pre-defined distributions, multiples the 2 numbers and stores the result. This process is then iterated several thousands of times, and the results are displayed as a new combined distribution.

2 main types of distribution can be used for Monte Carlo simulation analysis. Empirical distributions rely on larger sets of real world, historical data and sample sizes are used the most often. When data sets and sample sizes are small, theoretical distributions in the form of binomial distributions and lognormal distributions, for instance, may also be appropriate when there is some mechanical or probabilistic basis. All distributions used for Monte Carlo analysis are normally correlated significantly (i.e. with a probability factor of more than 0.75).

A Monte Carlo analysis can be quite easily developed using a random number generator. However several excellent Excel based templates, Excel add-ins and enterprise-grade Monte Carlo simulation software exist that are able to perform random number generation iterations to about 10,000 iterations. Examples of Monte Carlo simulation software include Crystal Ball, @Risk and others, which are readily accessible to financial analysts.

While a Monte Carlo simulation analysis using several thousands of iterations may be perceived as being more analytically robust, invalid statistical assumptions and distributions can create invalid results, leading to inappropriate results.

Financial analysts new to statistical and probabilistic approaches are therefore well advised to have a firm understanding of the concept of uncertainty and statistical analysis before attempting Monte Carlo simulation analysis for financial modeling and analysis.

There is an excellent Monte Carlo Simulation Excel Add-In available in the Finance 3.0 forums, which takes a random number input (using either a manual data input or Excel’s RAND function) and recalculates the Excel worksheet for as many repetitions as you request. It outputs the results to a new Excel worksheet with summary statistics and a histogram. The Excel add-in allows you to run Monte Carlo simulations in more than one cell and also includes several other options.

This Excel add-in require you to be signed in to Finance 3.0 for free access and download.

Not a member? Sign up to Finance 3.0 today, free and quick registration process.

Visit Finance 3.0 - don't miss out on this high quality financial learning opportunity

Comment on this article