1. Catalog
  2. Finance
  3. Monte Carlo Simulation
  4. Monte Carlo Analysis (without macros) Excel Model
Monte Carlo Analysis (without macros) Excel Model
  • Monte Carlo Analysis (without macros) Excel Model
  • Monte Carlo Analysis (without macros) Excel Model
  • Monte Carlo Analysis (without macros) Excel Model
Originally published: 28/03/2019 08:15
Last version published: 14/12/2019 08:56
Publication number: ELQ-33616-2
View all versions & Certificate
certified

Monte Carlo Analysis (without macros) Excel Model

This post explains how to run full Monte Carlo analysis in Excel without macros

Andrei Okhlopkovby Andrei Okhlopkov
Associate Director, Private EquityFollow 75
20 reviews654 views|2 comments|Bookmark download for free
sensitivity analysisfinancial modellingmonte carlodata tablessensitivitiesuncertainty

Description
A commonly used sensitivity analysis method involves several (usually base, upside and downside) scenarios. Each scenario corresponds to a fixed set of model inputs. While it provides the results (outcomes) of the model at various outsets, it does not give a precise likelihood of a particular outcome to happen.

In contrast, the Monte Carlo method tests a large (several hundreds or thousands) "scenarios" in which the inputs are drawn as random numbers. The distributions of those numbers follow the patterns determined by the user (range, mean, distribution type, correlation with each other). The results of the model (IRR, net return etc.) are also determined by ranges of values. Analysing those ranges we can determine mathematically the chances of an output being within a specific limit or being higher or lower than a certain threshold.

My analysis is built on standard Excel functions and data tables without macros.

The underlying principle of data tables is that they "place", one-by-one, the values listed in their row or column heading into specific cells, recalculate the file under each of those values (or each combination of those values) and then put the resulting values from a certain cell within the file into the table. Excel Help provides a detailed description of how data tables are created and used (please check it out if you are new to data tables).

In this file, every sensitivity is tested 5000 times under randomly generated assumptions. The input column of the data table just lists the numbers from 1 to 5000, after which the INDEX function picks the corresponding random values from the lists. Then the model is tested under those assumptions, and the results (IRR and net return) are placed into the next two columns of the data table.

This Best Practice includes
1 Excel file, 1 PDF file

Andrei Okhlopkov offers you this Best Practice for free!

download for free

Add to bookmarks

Discuss

Further information

Perform sensitivity analysis in situations of uncertainty or limited input data

Financial modelling in situations involving uncertainty

None

Reviews

keyboard_arrow_downShow all

keyboard_arrow_leftkeyboard_arrow_right

More Best Practices from Andrei Okhlopkov

See all
keyboard_arrow_leftkeyboard_arrow_right

Related categories

See all
keyboard_arrow_leftkeyboard_arrow_right

Discussion feed for Monte Carlo Analysis (without Macros) Excel Model

The user community and author are here to help. Go ahead!

Post Comment

  • Alexei Kapkin
    3 months ago
    Eloquens Member
    Andrei, how do you deal with correlations among inputs without VBA? I understand that one need e.g. Cholesky decomposition to transform uncorrelated to correlated inputs? Alexei
    1arrow_drop_uparrow_drop_downReply reply
    • Andrei Okhlopkov
      3 months ago
      Downloadable Tool Authorstar
      Hi Alexei -
      If you look at "Indexation and interest" of the file, to make two correlated arrays I take numbers from an array and "mix" it with random numbers from an independent array. This "mixing" take place in column H (titled "Correlated") and the formula is:

      Random_number x Corr_coeff + New_random_number x SqRoot(1 - Corr_coeff^2)

      This formula essentially "decomposes" the definition of linear correlation. No need for VBA or complex matrices...

      Andrei
      arrow_drop_uparrow_drop_downReply reply


    4.7 / 5 (20 votes)

    please wait...