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.

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

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.