The following question was posed recently on the LinkedIn Monte Carlo Simulation, Excel and Model Risk discussion. “I can’t afford £845 for @Risk. Can anyone suggest a good but much cheaper software package for doing Monte Carlo simulations?” It’s a reasonable question, with a simple answer.

@Risk, Crystal Ball and the other favourites for Monte Carlo simulation are large packages with large price tags, and often I find I don’t have the control I would like over the presentation of my modelling results. So on some occasions I have adopted a do-it-yourself, but effective, approach. It’s not that hard. Here’s how...

The RAND() function in Excel is a serviceable uniform(0,1) random number generator, from which you can easily create random numbers from any other distribution you need. Sprinkle the RAND() function around your model wherever needed.

Put all the outputs you want to track into a single column, and create a table to store your results (I have assumed one row per output scenario). Give the output column and the results table range names and use the following small Visual Basic routine.

Const R_OutputRange = "OutputRange"
Const R_ResultsTable = "ResultsTable"
 
Sub RunMonteCarlo()
  Dim OutputRange As Range
  Dim ResultsTable As Range
  Dim ResultsRow As Range
 
  ' initialise range pointers
  Set OutputRange = Range(R_OutputRange)
  Set ResultsTable = Range(R_ResultsTable)
 
  ' for each cell in column of Monte-Carlo results
  For Each ResultsRow In ResultsTable.Columns(1).Rows
    ' recalculate model
    Calculate
    ' copy output range
    OutputRange.Copy
    ' paste values to results table
    ' (transpose vertical output range to horizontal row)
    ResultsRow.PasteSpecial Paste:=xlPasteValues, Transpose:=True
  Next
End Sub

Obviously, substitute your own range names in the first two lines of code, and if your outputs are arranged in a row, you don’t need the transpose operation in the PasteSpecial command.

You will get a table filled with random scenarios, with each output variable in its own column. What you do with the data (calculating statistics, sorting and graphing) is up to you!

If your budget is close to zero, I think this is a pretty reasonable solution.