How to run simple Monte-Carlo simulations using Excel-VBA

Excel-VBA script for running simple Monte-Carlo simulations in Excel (no add-ins required).

Before buying Oracle Crystal Ball it might be a good idea to try doing the same simulation using vanilla Excel tools.

Important assumption - all variables have a normal distribution.

Download File - Simple-Monte-Carlo.xlsm

The code:

Option Explicit
Sub GetDistribution()

Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False

Dim Values(6) As Double
Dim RandNPV As Double
Dim NumSim, row, col, i As Long
NumSim = ThisWorkbook.Sheets(1).Cells(15, 3).Value
'Run Simulations

For i = 1 To NumSim
    'Read values
    For col = 1 To 6
        Values(col) = ThisWorkbook.Sheets("Sheet1").Cells(8, col + 2).Value
    Next col
    RandNPV = ThisWorkbook.Sheets("Sheet1").Cells(11, 3).Value

    'Record Results
    For col = 1 To 6
        ThisWorkbook.Sheets("Sheet2").Cells(i + 1, col + 1).Value = Values(col)
    Next col
    ThisWorkbook.Sheets("Sheet2").Cells(i + 1, 1).Value = RandNPV
Next i

Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
End Sub