Constructing a Bayesian inference posterior distribution in Excel

It is a simple matter to go through the steps of estimating a single parameter using the Bayesian principles using Excel, providing the likelihood function is not too complicated.

ModelRisk offers functions that calculate probabilities for all its univariate distributions that make the task a great deal easier.

The method consists of the following steps:

  1. Determine the parameter to be estimated, and write a column of values to test for this paramete"

  2. In the next column calculate the prior density. For an uninformed prior this can often be simply a list of 1's, but may also be a function of the tested value. It might seem a pointless step to write a column of 1's when you have a flat prior, but we advise it anyway as it reinforces good habits;

  3. In the third column calculate the probability of observing the data (the likely function) given the value of the parameter being tested in that row. If the likelihood function is complicated, try splitting it up into columns of partial calculations, which will also help you check you've got it right. Event trees are an excellent way to help you construct the probability of the pathways;

  4. In the fourth column multiply the second and third column values for the same row to get the non-normalized posterio"

  5. Use either ModelRisk's VoseDiscrete distribution (for a discrete variable) or VoseRelative (for a continuous variable) to construct a normalized posterior distribution from the first and fourth columns (since both these functions automatically normalize the distribution).

We very strongly recommend that you use an x-y scatter plot of the tested values against the un-normalized posterior column to adjust the range of values you have tested so that the posterior distribution is not shortened and to have a sufficient number of tested values within the highest confidence range of the posterior to give good detail. Plotting the prior and likelihood with the posterior, 'normalized' so that they can be graphed together (we make them all have the same mean for this purpose) helps you understand and explain the relative contributions of the prior and likelihood.

A plot of the prior, likelihood and posterior is also very helpful to validate the model. For example, stressing the parameters of the likelihood function should produce a change of emphasis that you can predict. It also gives you a tool to analyse and explain the effect of accumulating extra data.

If the parameter is continuous (so you are using the VoseRelative distribution) you can add as many values to test as you like as long as they are in ascending order, and they do not have to be equally spaced. If the parameter is discrete but the posterior covers a large number of possible values, you can test values at larger intervals than 1 and then use the VoseRelative function imbedded in a ROUND(..,0) function to regain the discrete nature of the parameter. If you use the VoseDiscrete function, then every possible value must appear in the column of tested values, or values at equal increments if there are many, but you run the risk of an inaccurate analysis.

We provide many examples of the use of the construction method:

Identifying a weighted coin

A simple Bayesian inference example using construction

Tigers in the jungle

Simple construction model showing the interaction between likelihood functions and informed priors

Gender of a random sample of people

A simple construction model illustrating the importance of the prior distribution

Micro-fractures on turbine blades

A model to show how to incorporate hyperparameters by simulation, as well as offering both simulation and construction approaches to determining the posterior distribution

Bayesian estimation of a components mean time to failure MTTF

A simple construction example that shows how we use data that describe being above or below a threshold, instead of exact observations

See Also



Monte Carlo simulation in Excel. Learn more

Spreadsheet risk analysis modeling


Adding risk and uncertainty to your project schedule. Learn more

Project risk analysis


Enterprise Risk Management software (ERM)

Learn more about our enterprise risk analysis management software tool, Pelican

Enterprise risk management software introduction


For Microsoft Excel

Download your free copy of ModelRisk Basic today. Professional quality risk modeling software and no catches

Download ModelRisk Basic now


For Primavera & Microsoft Project

Download your free copy of Tamara Basic today. Professional quality project risk software and no catches.

Download Tamara Basic now