How can I implement Monte-Carlo Simulations in MS Excel?

· MS Excel, Programming
Authors

Monte-Carlo simulation is a very import tool for assessing all kinds of risks and chances. It it widely used in project management, option pricing and business valuation. Often, the input data and the reporting should be placed in MS Excel. This article presents the different options available for combining Monte-Carlo simulation and MS Excel.

Monte Carlo Simulation Darts

Overview

There are several options for combining Monte-Carlo simulation with MS Excel. They differ in comfort, performance and re-usability.

Option 1: Direct Monte-Carlo Simulation in MS Excel

You can use the “=random()” function in Excel cells and create Sheets with simulations. There is a brief tutorial about this kind of Monte-Carlo simulation by Microsoft.

pro: No cost, easy to understand

contra: Hard to reuse, poor performance, hard to maintain if models get large, fitting of models very limited.

Option 2: Monte-Carlo Addin for MS Excel

There are numerous addins for Monte-Carlo simulation. Some of these addins are Chrystal Ball, @Risk, Ersatz, ModelRisk, Insight and RiskAmp (some free of charge). There is a great comparison of theses addins on Wikipedia.

pro: Easy-to-use, good fitting of models

contra: Hard to reuse models, hard to maintain if models get large

Option 3: Monte-Carlo simulation in Excel VBA

Much better than the direct Monte-Carlo simulation in MS Excel cells is the encapsulation of the Monte-Carlo model in VBA. This way, you can create models which are much easier to maintain. Especially, if you write your Monte-Carlo simulation as a User-Defined Function (UDF) (and not as a macro) your resulting Excel sheets stay somewhat understandable.

pro: Free of charge, better maintainable than direct in Excel cells, easy to reuse models

contra: Must learn a language, hard to maintain if models get large

Option 4: Use external Excel workflow: reading and writing the Excel sheet

The approach which delivers the best maintainability and the best overview even for large models is to use an external program which reads the input data from the Excel sheet and writes the output into a new sheet. You can either write this functionality in some high-level numerical language like Matlab, R or Mathematica or use Theta Suite which is designed for this kind of working style. Note that Theta Suite is from Thetaris, where I work. We designed Theta Suite for users who need a speedy execution of maintainable reuseable code for Monte-Carlo simulations. Theta Suite combines an easy to learn language and a professional environment for development and debugging.

pro: Maintainable models, easy to reuse, best performance

contra: Must learn a language

Option 5: Use a pre-compiled User-Defined Function (UDF) in MS Excel

If you choose to implement your Monte-Carlo simulation in a standard programming language, you can combine it with your MS Excel. In an earlier post, I showed which technologies are available for creating MS Excel User-Defined Function for Java, C++, C# and other languages.

pro: Program might be maintainable (depends on programming style), performance might be good (depends of language and style)

contra: Must learn a language, easy to create bugs

Summary

# Easy-to-use Easy-to-impl. (dev.) Easy to under-stand (review) Support of large models Main-tain-ability Required time (dev.) Debug capa-bilities Perfor-mance
1 + ++ – – – – – –
2 + + + +
3 + + +
4 ++ + ++ ++ + ++ ++
5 ++ – – – – + to ++ to ++ to ++

Conclusion

This conclusion is tricky: For creating Monte-Carlo simulations in MS Excel there are several choices from pure MS Excel, MS Excel with VBA and specialized Excel Add-Ins to external programming languages. My experience is that basically two good choices remain for the developer: Using a Monte-Carlo Add-in for MS Excel or using an external Excel workflow.

If the Excel sheet is designed as a one-time project, a specialized Monte-Carlo Add-in suits you best. This way, you can implement your simulation without the need of learning a programming language. But a reviewer might have a hard time understanding your model. Even yourself might have a very hard time understanding your simulation after a few months.

If you need your Excel sheets more often, you want to reuse the sheets or use the Monte-Carlo simulation regularly, your best choice is to get your models out of MS Excel and implement them externally. Using Theta Suite, you find an Excel workflow which allows you the definition of input Excel sheets and the definition of Excel templates for your output. This way, you are required to learn a new programming language, but you earn maintainability and easy re-use of your Monte-Carlo simulations.

11 Comments

Comments RSS
    • Andreas Grau

      Yes, Matlab already has great capabilities. But, instead of caring about your model you end up caring about indices, matrix dimensions, time steps etc. Theta Suite does all this for you.

      But, of cause, if you are already a Matlab-guru, Matlab gives you the power to control much more of the simulation. For those who do not want such control-overhead Theta Suite might be a better choice.

  1. Phil

    Are there any sample models for construction cost estimation and expected contingency allowances?

    • Greg

      The US Army Corps of Engineers does this using Crystal Ball. Haven’t seen it in operation, but I get their estimators in my classes and they report using it successfully.

  2. shivgan joshi

    Yes there are some implementations which are done on Excel which can be done on matlab for all.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s