Microsoft Excel, a staple in the toolkit of professionals across a myriad of industries, is often perceived as a simple spreadsheet program, ideal for organizing data and performing basic calculations. However, Excel is far more versatile than it appears at first glance, boasting powerful functionalities that can be harnessed to execute complex tasks such as running Monte Carlo simulations.

Monte Carlo simulation is a computational technique used to understand the impact of risk and uncertainty in financial, project management, cost, and other forecasting models. It might sound intimidating, but in essence, it's simply a method for exploring the likelihood of different outcomes. With Excel, this process becomes more accessible, enabling us to comprehend, model, and predict scenarios that involve a level of risk or uncertainty.

Let's dive deeper into how Monte Carlo simulations work in Excel:

Random Number Generation

At the core of Monte Carlo simulations are random numbers. Excel provides us with functions like RAND() and RANDBETWEEN() that generate random numbers for our simulations. These functions can simulate the uncertainty of variables in our model, such as fluctuations in interest rates, stock prices, or manufacturing costs.

Model Construction

Building your model is the next step. This involves defining a mathematical relationship between your variables. For instance, if you're forecasting sales, your model might include variables like advertising spend, price, and sales volume, each associated with a certain degree of uncertainty. Excel's formula functionality can be utilized to create these relationships.

Simulation Execution

Once the model is established, you can run the simulation. This is done by recalculating the spreadsheet, which generates new random values for each of your variables. Each recalculation represents a single iteration of your model, and each iteration provides a different potential outcome. You can achieve this in Excel either by using iterative calculations or by creating a loop using VBA (Visual Basic for Applications) programming.

Result Analysis

After running several thousand iterations (the more, the better), you can analyze the results. This can involve looking at histograms, summary statistics, or other visualizations of your output data. Excel's data analysis and visualization tools, such as pivot tables and charts, can be very helpful in this stage.

Sample Excel Model

The Excel file we are using as a sample serves as an insurance model, where it determines the life insurance premium using the personal details of a client. It takes into account various parameters, including age, gender, and policy amount, among other factors, as its inputs.

Download Workbook

As previously stated, for a Monte Carlo simulation to be performed, the model must be executed numerous times with randomly generated input values. Naturally, for effective analysis using tools like a histogram chart, it's crucial to store both the input and output values.

In this Monte Carlo simulation, we've designated the policy amount, also referred to as the face amount, as the random variable. In the following screenshot, you can see how different Face Amount values generate different Total Premium numbers.

Running multiple instances of a workbook and storing results requires a VBA code like the following:

Sub RunSimulation()
    ' Variables
    Dim simulationTable As Range
    Set simulationTable = Worksheets("Term Life").Range("s_SimulationTable")
    Dim n As Long
    n = simulationTable.Rows.Count
    Dim resultsArray() As Double
    ReDim resultsArray(n, 2)
    Dim randomFaceAmt As Double
    ' Turn off screen update and calculations to improve perfomance
    Application.Calculation = xlCalculationManual
    Application.ScreenUpdating = False
    ' Initially Calculate model to get a new random face amount
    ' Calculate and write results to simulation range (o_simulationTable) n times
    For i = 0 To n - 1
        randomFaceAmt = Range("s_RandomFaceAmount")
        Range("i_FaceAmount") = randomFaceAmt
        ' Store simulation values in an array
        resultsArray(i, 0) = randomFaceAmt
        resultsArray(i, 1) = Range("o_TotalPremium")
    Next i
    ' Send simulation values to the range
    simulationTable.Value = resultsArray
    ' Turn on screen update and calculations
    Application.Calculation = xlCalculationAutomatic
    Application.ScreenUpdating = True
End Sub

The underlying code operates by conducting a simulation that repeats for 'n' times, corresponding to the number of rows within the simulation table range. During each cycle, it generates a random face amount, performs computations based on this amount, and records both the face amount and the total premium in an array. Upon completion, this array is then allocated to a range within the worksheet, facilitating subsequent analysis or visualization.

How to handle simulations in SpreadsheetWeb

SpreadsheetWeb emulates the multi-run capability of VBA through its Custom Actions module. A Custom Action consists of a set of precompiled C# code that can be executed in conjunction with various SpreadsheetWeb events, such as calculating, saving, or printing.

Leveraging Custom Actions, you can construct and execute intricate calculation processes, establish actions that can export or import data from external applications or databases, and utilize APIs, including SpreadsheetWeb's proprietary ones.

Creating a custom action

Here is a sequential list of steps required to create a custom action.

  1. You need to generate a DLL file. Thus, start by creating a new C# Class Library in Visual Studio or your preferred IDE.
  2. Add references to requisite SpreadsheetWeb Custom Action and SpreadsheetWEB API assemblies:
    • Custom Action
      • Pagos.Spreadsheet.Domain
      • Pagos.Foundation.system
      • Pagos.Designer.Domain.Interfaces
    • SpreadsheetWeb Hub API
      • Pagos.Hub.Sdk
      • Pagos.Foundation.System
  3. The available custom action hooks within the Designer are exposed in the external interfaces. By creating a class that implements one or more of these interfaces, you can trigger execution of custom code at the following target events.
    Most common ones are BeforeCalculate and AfterCalculate which allows you to perform your actions before or after the calculation request has been sent to the calculation engine.

    You can check dedicated help pages to learn about remaining events: Custom Actions in Designer
  4. In our example, we have a separate method to generate random normal distributed numbers to avoid additional API calls to get values from Excel.
  5. The rest of the code uses BeforeCalculation event to set the generated face amount before the calculation and make the model be calculated with it.
  6. Beware that all named ranges in your code should be in the User Interface of your application. You can hide them if you do not want them exposed to the end-users.
    using Pagos.Designer.Definitions.Enums;
    using Pagos.Designer.Domain.Interfaces.Calculations;
    using Pagos.Designer.Domain.Interfaces.CustomHooks;
    using Pagos.Foundation.System;
    using Pagos.Foundation.System.Request;
    using Pagos.Spreadsheet.Domain.Models;
    using Pagos.Spreadsheet.Domain.Requests;
    using Pagos.Spreadsheet.Interfaces.Models;
    using Pagos.Hub.Sdk.Adapter;
    using ValueType = Pagos.Spreadsheet.Interfaces.Definitions.ValueType;
    public class CustomAction : IBeforeCalculation
        public ResponseBase<ResponseAction> BeforeCalculation(INamedRangesCache namedRangesCache)
            #region parameters
            var inputRanges = new List<string>
            var faceAmountName = "i_FaceAmount";
            var totalPremiumName = "o_TotalPremium";
            var minFaceAmount = 15000d;
            var maxFaceAmount = 250000d;
            var diff = maxFaceAmount - minFaceAmount;
            var rand = new Random();
            var simulationTable = namedRangesCache.GetNamedRange("s_SimulationTable");
            var n = simulationTable.Value.Length; // simulation count
            #region inputs
            // set selected values
            var inputsBatch = new List<List<RangeReference>>();
            // generate random face amounts with GenerateNumbers method
            for (int i = 0; i < n; i++)
                var inputsSingle = GenerateInputsWithDefaults(inputRanges, namedRangesCache);
                    new RangeReference
                        Reference = faceAmountName,
                        Value = new[] { new[] { new CellValue {
                            Type = ValueType.Auto,
                            Value = GenerateNumbers(rand, diff / 2, diff / 6).ToString() }
                        } }
            #region adapter
            // create an adapter for SpreadsheetWeb API connection to your server
            var adapter = new HubApiAdapter(
                UseInstanceCache = false
            #region request
            // create a request for SpreadsheetWeb API connection
            var request = new ApiRequest<ApplicationMultipleCalculationRequest>
                Request = new ApplicationMultipleCalculationRequest
                    WorkspaceId = Guid.Parse("b2d55c54-e2be-4b89-9c51-ca582606f584"),
                    ApplicationId = Guid.Parse("f060dfa7-8128-4ea2-9c9e-1580a50272df")
            request.Request.Inputs = new Dictionary<int, IUnifiedCalculationInput>();
            for (int i = 0; i < n; i++)
                    i, new UnifiedCalculationInput
                        Calculation = new CalculationInput
                            Inputs = new List<IRangeReference>(inputsBatch[i].ToArray()),
                            Outputs = new List<string> { faceAmountName, totalPremiumName }
            request.Request.RecordInfos = new Dictionary<int, IRecordInformation>();
                #region response
                // establish API connection and get responses
                var response = adapter.Calculations.CalculateMultiple(request).Result;
                if (response.IsError && response.Messages.Count > 0)
                    File.AppendAllText("c://Exception.txt", "\r\nResponse Error:\t" +response.Messages[0].Message);
                    for (int i = 0; i < n; i++)
                        simulationTable.Value[i][0].Value = response.Response.Outputs[i].Calculation.Outputs[0].Value[0][0].Value;
                        simulationTable.Value[i][1].Value = response.Response.Outputs[i].Calculation.Outputs[1].Value[0][0].Value;
                // update modified named ranges
            catch (Exception ex)
                File.AppendAllText("c://Exception.txt", string.Format("\r\nSomething bad happened!\r\nException Message:{0}", ex));
            return new ResponseBase<ResponseAction>
                Response = ResponseAction.Continue
        static List<RangeReference> GenerateInputsWithDefaults(List<string> namedRanges, INamedRangesCache namedRangesCache)
            var inputs = new List<RangeReference>();
            foreach (var range in namedRanges)
                    new RangeReference
                        Reference = range,
                        Value = new[]
                                new CellValue
                                    Type = ValueType.Auto,
                                    Value = namedRangesCache.GetNamedRange(range).Value[0][0].Value 
            return inputs;
        static double GenerateNumbers(Random rand, double mean, double stdDev)
            double u1 = 1.0 - rand.NextDouble(); //uniform(0,1] random doubles
            double u2 = 1.0 - rand.NextDouble();
            double randStdNormal = Math.Sqrt(-2.0 * Math.Log(u1)) * Math.Sin(2.0 * Math.PI * u2); //random normal(0,1)
            double randNormal = mean + stdDev * randStdNormal; //random normal(mean,stdDev^2)
            return randNormal;
  7. Once you are set, build the DLL file.

Adding custom action to a SpreadsheetWeb application

The steps outlined below demonstrate how to incorporate a custom action into a SpreadsheetWeb application.

  1. Open your application in Designer mode.
  2. Proceed to Custom Action module.
  3. In the next screen, click Add Custom Action. After giving your custom action a name, select or drag-and-drop the *.dll compiled assembly file into the Custom Action File Once the file upload is completed, press Create Custom Action.
  4. After creating, you can assign the custom action into a button. Edit a button and check Events box.
  5. Do not forget to add a chart and select the histogram named range to display the simulation results in your app.
  6. Once you’re done, remember to press the Submit button to save your changes. You can now click on Preview in the dial menu or the right-hand properties menu to see a preview of the application.

Excel's computational power combined with its flexibility makes it a valuable tool for running Monte Carlo simulations. By following the steps outlined in this article, you can harness the capabilities of Excel to model and analyze complex systems effectively. Whether you are working in finance, engineering, project management, or any other field that requires probabilistic analysis, Monte Carlo simulations in Excel can provide valuable insights for decision-making and risk assessment.