Welcome

RiskAMP Help

Welcome to the RiskAMP online help system. All the Add-in functions, including simulation analysis functions and random distribution functions, are described in this manual.

If this is your first time using the RiskAMP Add-in, check the Quick Start guide for a simple example. Important concepts for using the add-in are described in the sections on Reference Cells, Running a Monte Carlo simulation, and using the Histogram and Charts Wizard.

You can also search the help system using the tabs to the left.


Quick Start

How to create and run a Monte Carlo simulation in just a few steps:

1. In a new spreadsheet, insert a random value.

In a new spreadsheet, select any empty cell. Enter the formula

=NormalValue(100,10)

This will insert a random variable using the normal distribution, with a mean of 100 and a standard deviation of 10. This is the data the simulation will model.

2. Run a Monte Carlo simulation.

Open the Monte Carlo menu and select "Run Monte Carlo Simulation...". This will open the simulation dialog, which controls the simulation. Click "Start" to start the simulation. You'll see the random value you entered changing for each iteration of the simulation.

3. Use the Wizard to plot out the simulation data.

Open the Monte Carlo menu and select "Histograms and Charts Wizard". This will open the Wizard dialog. From the first page of the Wizard, click Next to start.

On the second page, "Select a source cell", use your mouse to select the cell you used to enter the random number in Step 1. The cell should be selected in Excel, and the address should appear in the Wizard dialog. Click Next to continue.

On the third page, "Select a target range", click the radio-button marked "Create a new Simulation Results Sheet". Then click Next to continue.

You are now on the last page of the Wizard; click Finish to exit. After you click the Finish button, the Wizard will create a new worksheet containing statistical data and tables describing your simulation.

4. Re-run and modify the simulation.

Once your simulation tables are created, you can re-run a simulation at any time using the Monte Carlo menu, and your results tables and charts will automatically update. Or, you can modify the cell that you modeled to display different data, then run a new simulation.

First, try running a new simulation. Open the Monte Carlo menu and select "Run Monte Carlo Simulation..." to open the simulation dialog. Click Start to run the simulation. You should see your charts and tables update as the simulation runs.

Next, try changing the data in your reference cell. Go back to your first worksheet, and select the cell where you entered the random data function. Change this cell (make sure it's the same cell) to read

=TriangularValue(0,10,100)

Now click back to the spreadsheet containing the results table, and run a new simulation from the Monte Carlo menu. You should see very different results in your data tables.

That's all there is to creating and running a Monte Carlo simulation. After you've tried out the simulation, you should review the documentation on the various simulation functions and the probability distributions available to better model your data.


Running a Simulation

To run a Monte Carlo simulation, select Monte Carlo -> Run Simulation from the Excel menu. This will launch the simulation dialog box. Enter the number of iterations to run, or leave the existing value. To begin the simulation, click 'Start'.

You can cancel the simulation at any time while its running. The progress bar should give you an idea of how long the simulation will take to complete.

The Allow Screen Updates checkbox toggles whether calculations are updated to the screen. Hiding screen updates will speed up the simulation significantly, but you may want to see updates to ensure you're getting an appropriate result. You can check or un-check this box at any time during the simulation.

The dialog box will close automatically when the simulation completes. You can then review or analyze the data generated in the simulation, using the simulation functions.


Using Reference Cells

In any simulation function, the Reference Cell is the cell being tested. Depending on your spreadsheet, this might be a random variable, or it could be the result of a formula that itself depends on one or more random variables. Any cell in your spreadsheet can be a reference cell. Generally the value of this cell will change at each iteration of a Monte Carlo simulation, and the functions in this section can be used to test the parameters of the simulation.

To use any cell as a Reference Cell, simply refer to it in a simulation function. You must do this before you run your simulation, however, so the software is able to track the values over each iteration of the simulation.

After a simulation has run, the value of all Reference Cells are retained for each iteration of the simulation. At this point, you can test the parameters of the Reference Cell using the simulation functions described below.


SimulationFunctions

Simulation functions are used to analyze the results of a probability simulation, after the simulation has run. In general, simulation functions take one or more Reference Cells as parameters (in addition to any additional parameters); see the section on Reference Cells for more information on the concept. Simulation functions can return both statistical values (i.e. analyses of the complete simulation) and discrete results (i.e. results from a particular iteration of the simulation).


Using Latin Hypercube Sampling (LHS)

Latin Hypercube sampling (LHS) is a method of sampling from random distributions that seeks to spread samples evenly (according to the distribution probability) over all iterations of the simulation. LHS can be used to reduce the number of iterations in a Monte Carlo simulation, while returning a representative sample of values.

LHS is performed by dividing the probability distribution into equal probability segments (the number of segments is determined by the number of trials). Over the simulation, the segments are randomly re-arranged, and at each iteration a value is randomly sampled from within one of the segments. Each segment is only sampled once.

To use Latin Hypercube sampling in the RiskAMP add-in, select "Advanced Properties" from the menu and check the box to activate LHS.

Please note:

Latin Hypercube sampling is not implemented for all random distributions. This setting will apply LHS to the following distributions:

Normal (Gaussian)

Lognormal (both lognormal and lognormal gross return functions)

Beta

Beta-PERT

Triangular

Uniform

Distributions not listed here will not be affected by the LHS setting.


Reseeding the Random Number Generator

To reseed the random number generator, select "Advanced Properties" from the menu and select the "Random Number Generator" tab. On that page, enter a new seed in the text box and press "Apply". You can also enter "0", which will use a seed based on the current system time.

This function is most useful if you want to create reproducible simulation results. By setting the seed to a particular value, you can ensure consistent results from a simulation run with the same parameters.


Using the Histogram and Charts Wizard

The Histogram and Charts Wizard provides a quick way to create data tables and charts based on your simulation results.

You can run the Wizard at any time from the Monte Carlo menu, and you can use it to plot any data generated in a simulation - a random variable, your own data function, or any combination of inputs.

To start the Wizard, select "Histogram and Charts Wizard" from the Monte Carlo menu. This will open the Wizard. The first page contains some introductory text.

Click the Next button to start.

Select a Source Cell

The first step in using the Wizard is selecting a source data cell. This cell contains the data you are analyzing in your simulation. It might contain a simple probability function, or it might be the output of a complex function.

Select the cell with your mouse in the Excel spreadsheet, and the cell location should appear in the dialog box.

Only a single source cell can be used for plotting simulation charts. If you select a range of cells, the top-left cell will be used as the source cell.

Once the correct cell is selected, click Next to continue.

Select a Target Range

The next step is selecting a target range. There are two basic options here: you can select a target range, somewhere in your workbook, that will contain the data table; or, in the alternative, you can create a new worksheet that contains several results tables.

Creating a new worksheet is the simplest way to get results data from your simulation. The new worksheet will contain statistical data such as the mean, median, deviation, and so on, as well as data tables and charts.

Selecting a target range allows you to include the results of your simulation in an existing workbook, and gives you more control over the layout and format of results.

If you want to select a target range for your data, highlight the range of cells with your mouse. Please note: any existing data in the target range will be erased. You can select a horizontal or vertical range. If your range is one row (or one column) deep, the data table will include values over the simulation. You can also select two rows (or two columns) to include labels for the table data.

If you choose to create a new spreadsheet, there are no more Wizard options, and you can skip to the end. If you have selected a target range, your next step will be to select the type of data you want to display.

Click Next to continue.

Select Results Data

If you selected a target range, you must now choose the type of data results you want to include in that range. Each results type displays a different aspect of your simulation. Remember that you can run the Wizard multiple times to include different data tables.

Histogram Table

A histogram displays the frequency of occurence of particular values over the simulation. The histogram arranges values into "bins", which are regularly spaced between the minimum and maximum values of the source cell in the simulation.

Histograms can easily show you the most-commonly and least-commonly occuring values, as well as the relative likelihood of a reaching a particular value.

Percentile Results

A percentile table uses the SIMULATIONPERCENTILE function to create a table of percentiles (probability of occurence) and corresponding values of the source cell during the simulation.

This data table can show you the value the source cell will reach with any given probability.

Interval Results

An interval table displays the probability of the source cell reaching a particular value during the simulation. This data is similar to a histogram, but uses percentiles (likelihood of occurence) rather than absolute counts to display the results data.

The interval table can be used to determine the likelihood of reaching a particular value. You can include similar interval analyses in your spreadsheet using the SIMULATIONINTERVAL function, which returns the likelihood of the source cell falling within any given interval.

The checkbox marked "Include Column (Row) Titles" allows you to include column (or row) headers for your data tables. If you check this box, the first row (or column) will include titles.

Click Next to continue.

Run a Monte Carlo Simulation

This screen will only appear if there is no simulation data for your source cell: if you like, you can skip this step.

Click the button to run a simulation. This will hide the Wizard and open the simulation dialog.

When the simulation dialog is visible, run a Monte Carlo simulation by clicking the Start button. For more information, see the section on Running a Simulation, above.

You can run a simulation at any time, and your results data will be updated automatically.

Even if you skip the simulation at this step, you can create a results table and chart for use the next time you run a simulation.

Click Next to continue.

The Wizard is Complete

That's it: the Wizard is now complete. Your data table should be populated within your spreadsheet. If you want to create a chart of the data, make sure the box is checked.

Click Finish to close the Wizard.

Remember that the Wizard is the easiest way to get simulation results into your spreadsheet, but it's not the only way. You can use the simulation functions to include statistics from a simulation or generate your own data tables in any worksheet.


Locking and Unlocking Simulation Functions

The RiskAMP Add-in uses custom Excel functions to generate random numbers and, more importantly, to process and analyze simulation results. One drawback of this approach is it makes it difficult to share spreadsheets, since other users might not have the Add-in installed.

To make a spreadsheet shareable, you can "lock" the RiskAMP simulation and random number functions. In the spreadsheet, all RiskAMP functions will be replaced by their calculated value. You can then save the spreadsheet, and users without the Add-in will be able to view it like any other spreadsheet. When you want to recalculate or re-run a Monte Carlo simulation, you can "unlock" the simulation functions in the spreadsheet, and the original functions will be put back in place.

To lock or unlock simulation functions, select the appropriate entry from the Monte Carlo menu. Locked simulation functions are preserved when a document is saved, so they can be unlocked later.

If you lock simulation functions in a spreadsheet which already contains some locked function data, the original locked functions will be overwritten and will not be recoverable. A warning message will pop up before you overwrite previously saved data.


Installing the Add-In

The Installer executable should install the Add-In so that it's recognized by Excel. If not, or if you've removed the Add-In and want to reinstall it, follow the directions below.

With Excel running, select Tools->Add-Ins from the menu bar. (Add-Ins may by hidden in the Tools menu in Windows 2000 and XP if you don't use it often. To see it, click on the chevron at the bottom of the menu).

This will open the Add-Ins Dialog Box. Click the "Browse" button and from the File Dialog, browse to the installation directory for the RiskAMP Monte Carlo Add-In. You can set the installation directory when you run the installer, but the default is C:\Program Files\RiskAMP\Monte Carlo\.

In that directory, you should see the Add-In file called RiskAMP.xll. Select this file, and it will be added to the Add-Ins list.

Before closing the Add-Ins Dialog Box, make sure the checkbox for the RiskAMP Monte Carlo Add-In is checked. Then click OK.


Removing the Add-In

Running the uninstaller should remove the Add-In from Excel. The uninstaller is available from the Start menu, under Programs -> RiskAMP. If that doesn’t work, or if you want to disable the add-in without uninstalling it, follow the directions below.

With Excel running, select Tools->Add-Ins from the menu bar. (Add-Ins may by hidden in the Tools menu in Windows 2000 and XP if you don't use it often. To see it, click on the chevron at the bottom of the menu).

This will open the Add-Ins Dialog Box. You should see the RiskAMP Monte Carlo Add-In in the list, with a check next to it. Click to clear the checkbox, and the Add-In will be disabled in Excel. Then click OK. You can reactivate the Add-In later by checking the box.


Uninstalling the Add-In

To uninstall the add-in, use Add/Remove programs from the Control Panel, or run the program Uninstall.exe in the installation directory. If you created links in the Start Menu when you installed the program, you can run the uninstaller directly from the Start Menu.

Otherwise, use Windows Explorer to navigate to the installation directory, and run Uninstall.exe from there.


SimulationPercentile

SimulationPercentile ( ReferenceCell, Percentile )

This function returns the value of the reference cell corresponding to the given percentile over all iterations of the simulation. A percentile value of 50% will return the mean of the Reference Cell; 0% will return the minimum value of the Reference Cell; and 100% will return the maximum value of the Reference Cell.

This function is used to test the expected value of a function corresponding to various probabilities. This function can be used as the inverse of the SimulationInterval function.


SimulationInterval

SimulationInterval ( ReferenceCell, MinimumValue, MaximumValue )

This function returns the probability that the value of the reference cell will fall between the given Minimum Value and Maximum Value. The Minimum and Maximum Values are optional, and if omitted they will be set to the absolute minimum and maximum value of the Reference Cell over the simulation.

This function is used to test the likelihood that the Reference Cell will reach, or will be below, certain fixed values. This function can be used as the inverse of the SimulationPercentile function; for example, if the Maximum Value is set to the mean of the Reference Cell, the result should be 50%.


SimulationValue

SimulationValue ( ReferenceCell, ResultIndex )

This function returns the exact value of the reference cell at a given iteration during the simulation.


SimulationAverage

SimulationAverage ( Reference Cell )

This function returns the mean (average) value of the reference cell during the simulation.

This function is identical to the SimulationMean function.


SimulationMean

SimulationMean ( Reference Cell )

This function returns the mean (average) value of the reference cell during the simulation.

This function is identical to the SimulationAverage function.


SimulationMedian

SimulationMedian ( Reference Cell )

This function returns the median value of the reference cell during the simulation.

To see all values of the Reference Cell, use SimulationValue.


SimulationMin

SimulationMin ( Reference Cell )

This function returns the minimum value of the reference cell during the simulation.

To see all values of the Reference Cell, use SimulationValue.


SimulationMax

SimulationMax ( Reference Cell )

This function returns the maximum value of the reference cell during the simulation.

To see all values of the Reference Cell, use SimulationValue.


SimulationMode

SimulationMode ( Reference Cell )

This function returns the mode (most likely) value of the reference cell during the simulation. This function does not return a true mode, but a synthetic mode derived by placing results into "buckets" (much like creating a histogram).

To see all values of the Reference Cell, use SimulationValue.


SimulationTrials

SimulationTrials ( )

This function returns a count of the number of iterations in the last-run simulation.


SimulationTime

SimulationTime ( )

This function returns the processing time for the most recently run simulation, in seconds.


SimulationVariance

SimulationVariance ( ReferenceCell, [Unbiased] )

This function returns the variance of the reference cell during the simulation from the mean value over all iterations.

Leave the second parameter blank, or set to FALSE, to find the so-called "unbiased" or N-1 variance. Set this parameter to TRUE to find the "biased" variance.


SimulationStandardDeviation

SimulationStandardDeviation ( Reference Cell, [Unbiased] )

This function returns the standard deviation of the reference cell during the simulation from the mean value over all iterations.

Leave the second parameter blank, or set to FALSE, to find the so-called "unbiased" or N-1 standard deviation. Set this parameter to TRUE to find the "biased" variance.


SimulationCorrelation

SimulationCorrelation ( ReferenceCell1, ReferenceCell2 )

This function returns the correlation of reference cell 1 and reference cell 2 during the simulation over all iterations.


SimulationCovariance

SimulationCovariance ( ReferenceCell1, ReferenceCell2 )

This function returns the covariance of reference cell 1 and reference cell 2 during the simulation over all iterations.


SimulationSkewness

SimulationSkewness ( ReferenceCell )

This function returns the skewness of the reference cell during the simulation from the mean value over all iterations.

Skewness refers to the degree of asymmetry of a distribution. The normal distribution should be perfectly symmetric, with a skewness value of 0.


SimulationKurtosis

SimulationKurtosis ( ReferenceCell )

This function returns the Kurtosis of the reference cell during the simulation from the mean value over all iterations.

Kurtosis is the degree of peakedness of a distribution, relative to a normal distribution. Perfectly normally-distributed data will have kurtosis of 3.


SimulationHistogramBin

SimulationHistogramBin ( ReferenceCell, NumberOfBins, BinIndex, UseRoundedBins )

This function is used to help create histogram tables. It's a shortcut for using intervals to sort result values from the reference cell into regular "bins", for creating a histogram.

This function is ordinarily used through the Histogram Wizard, accessed from the Menu. You can use it directly to create your own histogram tables. The Number of Bins refers to the total number of bins to use, spread evenly over the range of the simulation values. The Bin Index is the bin to count for this cell. Ordinarily, a histogram will display each bin in order.


SimulationHistogramBinLabel

SimulationHistogramBinLabel ( ReferenceCell, NumberOfBins, BinIndex, UseRoundedBins )

This function is used to help create histogram tables. See the entry for SimulationHistogramBin for more information.

This function is ordinarily used through the Histogram Wizard, accessed from the Menu. You can use it directly to create your own histogram tables. The Number of Bins refers to the total number of bins to use, spread evenly over the range of the simulation values. The Bin Index is the bin to count for this cell. Ordinarily, a histogram will display each bin (with the appropriate label) in order.


SortedSimulationIndex

SortedSimulationIndex ( Reference Cell, Value )

The SortedSimulationIndex function is used to find values from the reference cell in sorted order.

The function returns an index value which can be passed to the SimulationValue function to retrieve the actual value. The Value parameter corresponds to the index desired, between 1 and the total number of trials from the simulation.


SimulationStandardError

SimulationStandardError ( Reference Cell )

This function returns the standard error of the reference cell during the simulation from the mean value over all iterations.


SimulationSlope

SimulationSlope ( Y, X )

Y | The reference cell of Dependent values for the regression
X | The reference cell of Independent values for the regression

Returns the slope (m) from a linear regression.

The linear regression equation has the form

y = mx + b

This function solves for the slope (m) based on the values of the reference cells X and Y over all iterations of the simulation. See the SimulationIntercept function to solve for the intercept (b).


SimulationIntercept

SimulationIntercept ( Y, X )

Y | The reference cell of Dependent values for the regression
X | The reference cell of Independent values for the regression

Returns the intercept (b) from a linear regression.

The linear regression equation has the form

y = mx + b

This function solves for the intercept (b) based on the values of the reference cells X and Y over all iterations of the simulation. See the SimulationSlope function to solve for the slope (m).


SimulationRSquared

SimulationRSquared ( Y, X )

Y | The reference cell of Dependent values for the regression
X | The reference cell of Independent values for the regression

Returns the R-Squared value from a linear regression.


SimulationGeometricMean

SimulationGeometricMean ( Reference Cell )

This function returns the geometric mean of the reference cell during the simulation.


SimulationArithmeticMean

SimulationArithmeticMean ( Reference Cell )

This function returns the arithmetic mean (average) value of the reference cell during the simulation.

This function is identical to the SimulationAverage function.


ArcsineValue

ArcsineValue ( Min, Max )

Min | Minimum value of the returned random variable
Max | Maximum value of the returned random variable

Returns a random value sample from the Arcsine distribution.


BernoulliValue

BernoulliValue ( Probability )

Probability

The probability of a TRUE result, expressed as a percentage

This function returns TRUE with the given probability, or FALSE.

This function can be used to simulate choices or decision branches within an application when the probability of a choice can be estimated.

This function is identical to the ProbabilityValue function.


BetaValue

BetaValue ( Shape 1, Shape 2 )

Shape 1 | First shape parameter > 0
Shape 2 | Second shape parameter > 0

Returns a random value sample from the Beta distribution.


BinomialValue

BinomialValue ( Probability, Trials )

Probability | Probability of event, 0 <= p <= 1
Trials | Number of independent trials > 0

Returns a random value sample from the Binomial distribution.


CauchyValue

CauchyValue ( Location, Scale )

Location | Location parameter
Scale | Scale parameter

Returns a random value sample from the Cauchy (Lorentz) distribution.


ChiSquareValue

ChiSquareValue ( dF )

dF

Degrees of freedom >= 1

Retrurns a random value sampled from the Chi Square distribution.


ChoiceValue

ChoiceValue ( Range of Choices )

Range of Choices

A range of cells sampled by the distribution

This function returns one cell from the passed range, at random, based on a uniform random distribution.

The range of choices can include any valid Excel cell value, including numbers or strings.


CorrelatedNormalValue

CorrelatedNormalValue ( mu, sigma, CorrelatedValue, Coefficient, [CorrelatedValue Mean], [CorrelatedValue StdDev] )

mu | The mean of the normal distribution
sigma | The standard deviation of the normal distribution
Correlated Value | The correlated value (a reference to another Excel cell)
Coefficient | The coefficient of correlation (rho) between this cell and the correlated value
CV Mean | [OPTIONAL] The mean of the distribution of the correlated value, if different from the mean of the distribution of this cell
CV Std.Dev. | [OPTIONAL] The standard deviation of the distribution of the correlated value, if different from the mean of the distribution of this cell

The CorrelatedNormalValue function samples random values from a normal distribution, with a specified correlation to another randomly distributed variable.

This is a bivariate distribution; for a true multivariate distribution, allowing cross-correlation across several values, see the MultiNormalValue function.

This function should be used in conjunction with another, normally distributed value. In the simplest case the two values use the same distribution parameters (mean and standard deviation); in the more complex case, the two values use different parameters.

To use this function, first create a cell with a normally distributed value, e.g. in cell A1 enter

=NormalValue( 100, 10 )

to create a value with a mean of 100 and a standard deviation of 10.

In cell A2, create the correlated value with the same parameters and using a coefficient of correlation, in this case 0.5:

=CorrelatedNormalValue( 100, 10, 0.5, A1 )

The value in cell A2 will be randomly distributed, but over a simulation will be correlated with the random value in A1 with the given coefficient of correlation (to test, create a cell with the SimulationCorrelation function and run a simulation).

In the more complex case, the two values have different distribution parameters (mean and standard deviation). In this case, the second cell must include the parameters of the first cell in order to derive an appropriately correlated value.

For this case, in the second cell A2 create a cell with a normally distributed value, coefficient of correlation, reference to the correlated cell and additional parameters:

=CorrelatedNormalValue( 200, 20, 0.5, A1, 100, 10 )

In this case the second cell will contain a random value from the normal distribution with a mean of 200 and a standard deviation of 20, where the correlated value (cell A1) has a mean of 100 and a standard deviation of 10. In a simulation, the values will be correlated with a coefficient of 0.5.


CosineValue

CosineValue ( Min, Max )

Min | Minimum value of the returned random variable
Max | Maximum value of the returned random variable

Returns a random value sample from the Cosine distribution.


DoubleLogValue

DoubleLogValue ( Min, Max )

Min | Minimum value of the returned random variable
Max | Maximum value of the returned random variable

Returns a random value sample from the Double Log distribution.


ErlangValue

ErlangValue (Scale, Shape )

Scale | Scale parameter > 0
Shape | Shape parameter, any positive integer

Returns a random value sample from the Erlang distribution.


ExponentialValue

ExponentialValue ( Location, Shape )

Location | Location parameter, any real number
Shape | Scale parameter, must be > 0

Returns a randomly-sampled value from the exponential distribution with the given parameters.


ExtremeValue

ExtremeValue ( Location, Scale )

Location | Location parameter, any real number
Scale | Scale parameter > 0

Returns a random value sample from the Extreme distribution.


FRatioValue

FRatioValue ( dF 1, dF 2 )

dF 1 | Shape parameter 1 (degrees of freedom)
dF 2 | Shape parameter 2 (degrees of freedom)

Returns a random value sample from the F Ratio distribution.


GammaValue

GammaValue ( Location, Scale, Shape )

Location | Location parameter
Scale | Scale parameter > 0
Shape | Shape parameter > 0

Returns a random value sample from the Gamma distribution.


GeometricValue

GeometricValue ( Probability )

Probability

Probability of event happening 0 < p < 1

Returns a random value sample from the Geometric distribution.


HyperExponentialValue

HyperExponentialValue (Prob. of A, Location, Shape A, Shape B )

Prob. of A

Location

Shape A

Shape B

Returns a random value sampled from the Hyperexponential distribution.


HyperGeometricValue

HyperGeometricValue ( n, N, K )

n | Number of trials
N | Population size
K | Successes contained in the population

Returns a random value sample from the Hypergeometric distribution.


LaplaceValue

LaplaceValue ( Location, Scale )

Location | Location parameter, any real number
Scale | Scale parameter > 0

Retrurns a random value sampled from the Laplace (Double Exponential) distribution.


LogarithmicValue

LogarithmicValue ( Min, Max )

Min | Minimum value of the returned random variable
Max | Maximum value of the returned random variable


LogisticValue

LogisticValue ( Location, Scale )

Location | Location parameter, any real number
Scale | Scale parameter > 0

Returns a random value sample from the Logistic distribution.


LognormalValue

LognormalValue ( Mu, Sigma )

Mu | the mean of the underlying normal distribution
Sigma | the standard deviation of the underlying normal distribution

This function returns random values according to the lognormal, which is the exponent of the normal distribution described by the Mean and Standard Deviation provided.

The lognormal distribution is often used to describe values that have unlimited range in one direction but a fixed range in the opposite direction, such as securities prices.

Do not use this function for modeling lognormally-distributed financial returns (such as financial return projections which take compounding into account). For that type of analysis, use the LognormalGrossReturnValue function.


LognormalGrossReturnValue

LognormalGrossReturnValue ( Mu, Sigma )

Mu | the mean of the lognormal distribution
Sigma | the standard deviation of the lognormal distribution

This function uses the lognormal distribution to model financial returns, such as return projections which take compounding into account.

Do not use this function for a general lognormal distribution. For that type of analysis, see the LognormalValue function.


MultiNormalValue

MultiNormalValue ( RangeOfValues, CorrelationMatrix, [Mean], [StandardDeviation] )

Range of Values | A range of output values (see below)
Correlation Matrix | The cross-correlation of the several values (see below)
mu | [OPTIONAL] the mean of the normal distribution for this cell
sigma | [OPTIONAL] the standard deviation of the normal distribution for this cell

The MultiNormalValue function samples random values from a multivariate normal distribution, with a correlation matrix describing the cross-correlation of each of a set of samples.

This is a multivariate distribution; for a simpler bivariate distribution, providing correlation between two values, see the CorrelatedNormalValue function.

Using this function can be somewhat confusing at first; the simplest way to understand it is to view an example spreadsheet using the function, then read this documentation. If you installed the sample files, open the file "MultivariateExample.xls" from the installation directory or the Start Menu -> RiskAMP folder.

The RangeOfValues parameter is a range of output values, which (1) must be the same size as the correlation matrix, below; and (2) must contain the calling cell - that is, the cell containing the function must be within this range. If either of these conditions is not met, the function will return an error.

The CorrelationMatrix must be the same size as the range of values, in two dimensions (i.e. a square with each side the same length as the range of values). Only the lower-left side of this matrix will be used; the top-right can be omitted.

The CorrelationMatrix must be Positive Definite, or the function will fail.

The Mean and StandardDeviation parameters are optional. If provided, the generated value will be sampled from a distribution with those paramters. If omitted, the generated value will be sampled from a normal distribution with a mean of zero (0) and a standard deviation of one (1).


NegativeBinomialValue

NegativeBinomialValue ( Probability, Successes )

Probability | Probability of event, 0 <= p <= 1
Successes | Number of successes >= 1

Returns a random value sample from the Negative Binomial distribution.


NormalValue

NormalValue ( Mu, Sigma )

Mu | the mean of the normal distribution
Sigma | the standard deviation of the normal distribution

This function returns random values according to the normal (Gaussian) distribution described by the Mean and Standard Deviation provided.


PascalValue

PascalValue ( Probability, Successes )

Probability | Probability of event, 0 <= p <= 1
Successes | Number of successes >= 1

Returns a random value sample from the Pascal distribution.


PERTValue

PERTValue ( Minimum, Most Likely, Maximum, [Weight] )

Minimum | The minimum value (inclusive)
Most Likely | The most likely value of the distribution
Maximum | The maximum value (inclusive)
Weight | [OPTIONAL] is the weight (lambda) which controls the peakedness of the distribution.

This function returns random values according to a betaPERT distribution described by the three bounding points. The betaPERT distribution is similar to the Triangular distribution, but describes tails to the extremes (minimum and maximum) rather than straight lines.

The betaPERT distribution can be used to model expert opinion, in which the Most Likely value has more credence than the minimum and maximum values.

The optional Weight parameter can be used to increase or decrease the impact of the Most Likely value; the default weight for the betaPERT distribution is 4.


PoissonValue

PoissonValue ( mu )

mu

Mean of the Poisson distribution

Retrurns a random value sampled from the Poisson distribution.


ProbabilityValue

ProbabilityValue ( Probability )

Probability

The probability of a TRUE result, expressed as a percentage

This function returns TRUE with the given probability, or FALSE.

This function can be used to simulate choices or decision branches within an application when the probability of a choice can be estimated.

This function is identical to the BernoulliValue function.


ParabolicValue

ParabolicValue ( Min, Max )

Min | Minimum value of the returned random variable
Max | Maximum value of the returned random variable

Returns a random value sample from the Parabolic distribution.


ParetoValue

ParetoValue ( Shape )

Shape

Shape parameter > 0

Returns a random value sample from the Pareto distribution.


Pearson5Value

Pearson5Value ( Scale, Shape )

Scale | Scale parameter > 0
Shape | Shape parameter > 0

Returns a random value sample from Pearson’s Type 5 (Inverted Gamma) distribution.


Pearson6Value

Pearson6Value ( Scale, Shape 1, Shape 2 )

Scale | Scale parameter > 0
Shape 1 | Shape parameter > 0
Shape 2 | Second shape parameter > 0

Returns a random value sample from Pearson’s Type 6 distribution.


PowerValue

PowerValue ( Shape )

Shape

Shape parameter > 0

Returns a random value sample from the Power distribution.


RayleighValue

RayleighValue ( Location, Scale )

Location | Location parameter, any real number
Scale | Scale parameter > 0

Returns a random value sample from the Rayleigh distribution.


SequentialValue

SequentialValue ( Lower Bound, Upper Bound )

Lower Bound | The minimum value (inclusive)
Upper Bound | The maximum value (inclusive)

This function returns sequential values between the minimum and maximum values, evenly spaced over all iterations of a simulation.


ScaledLognormalValue

ScaledLognormalValue ( Mu, Sigma )

Mu | the mean of the lognormal distribution
Sigma | the standard deviation of the lognormal distribution

This function samples from a scaled lognormal distribution. The lognormal distribution will have the mean and standard deviation given by the function parameters.

Do not use this function for a general lognormal distribution. For that type of analysis, see the LognormalValue function.


StudentTValue

StudentTValue ( Deg. Freedom )

Deg. Freedom

Degrees of freedom

Returns a random value sample from the Student’s t distribution.


TriangularValue

TriangularValue ( Minimum, MostLikely, Maximum )

Minimum | The minimum value (inclusive)
Most Likely | The most likely value (mode) of the distribution
Maximum | The maximum value (inclusive)

This function returns random values according to a triangular distribution described by the three bounding points. A value for the Mode (Most Likely value) that lies half-way between the Upper and Lower bounds will result in an equilateral triangle.

The triangular distribution can be used in functions requiring distribution across a known range with a particular skew; for example, calculating expected time when given an expected time T(m), an optimistic time T(o) and a pessimistic time T(p). The triangular value would describe a random time between T(p) and T(o), skewed by the relative placement of T(m).

The triangular distribution can be used to model expert data; see also the beta-PERT distribution, which provides a similar function.


UniformRangeSample

UniformRangeSample ( Range of Choices )

Range of Choices

A range of cells sampled by the distribution

The UniformRangeSample function is similar to ChoiceValue, but can be used to create a set of non-repeating output values.

For any given range, each call to UniformRangeSample will return a different, randomly selected value from the source range. During a simulation, at every iteration the values from the source range will be redistributed among cells calling the UniformRangeSample function.

For a given source range, if the number of cells calling UniformRangeSample is greater than the number of cells in the source range, some repetition will occur.


UniformValue

UniformValue ( Minimum, Maximum )

Minimum | The minimum value (inclusive)
Maximum | The maximum value (inclusive)

This function returns random values uniformly distributed between the minimum and maximum (inclusive).

The uniform function can be used to generate random values when the bounds are known, but not the actual distribution.


WeightedChoiceValue

WeightedChoiceValue ( Range of Choices, Range of Weights )

Range of Choices | A range of cells sampled by the distribution
Range of Weights | A range of weights, used to weigh sampling from the range of choices

This function returns one cell from the passed range, at random, based on a random distribution. Cells will be sampled randomly, weighted by the values in the range of weights.

The range of choices can include any valid Excel cell value, including numbers or strings. The range of weights should be the same shape (same number of rows and columns) as the range of choices. The range of weights can contain any absolute values. Weights are proportional to the sum of all values in the range of weights (i.e. they do not need to be percentage values).


WeibullValue

WeibullValue ( Location, Scale, Shape )

Location | Location parameter, any real number
Scale | Scale parameter > 0
Shape | Shape parameter > 0

Returns a random value sample from the Weibull distribution.