Envelope method

See also: Modeling correlation introduction

The envelope method offers a more flexible technique for modeling dependences that is both intuitive and easy to control. It models the logic whereby the value of the independent variable statistically determines the value of the dependent variable.

This problem shows a way of creating a correlation between two random variables.

For example, we may have both next year's interest rate and next year's mortgage rate represented as distributions. The figure below gives an example of two distributions modeling these interest rate and mortgage rate predictions.

Clearly, these two components are strongly positively correlated, i.e. if the interest rate turns out to be at the high end of the distribution, the mortgage rate should show a correspondingly high value. If we neglect to model the inter-dependency between these two components, the joint probabilities of the various combinations of these two parameters will be incorrect.  Impossible combinations will also be generated.  For example a value for the interest rate of 6.5% could occur with a value for the mortgage rate of 5.5%.

We will model the interest rate as an  independent random variable, which is correlated with the random samples for the mortgage rate.

Let's model the interest rate as a PERT distribution with three parameters - the minimum of 3%, the most likely of 5% and the maximum of 8%. Let's assume these values were determined from some previous expert elicitation. Now we ask the experts the following questions: if the interest rate was as low as 3% what could the mortgage rate be? The answers we get are minimum 6%, most likely 8% and maximum 10%. Ditto if the interest rate was 5%, the mortgage rate would be minimum 8%, most likely 10% and maximum 13% and if the interest rate were as high as 8% then the mortgage rate would be minimum 10%, most likely 13% and maximum 16%:

The solution to the problem is provided in the spreadsheet correlating interest and mortgage rates.

First we need to find 3 polynomial functions that run through the interest rate combined with the mortgage rate minimum line then the mortgage rate most likely and the mortgage rate maximum lines:

They give us 3 polynomial equations, which run through the 3 sets of dots. We can now use these equations to calculate for a given value of the interest rate being simulated, what that mortgage rate minimum most likely and maximum are:

The independent variable (interest rate) is in cell C18, the polynomial equations in cells C20, C21 and C22. Now we can finally sample from a PERT distribution constructed from the minimum most likely and maximum of those mortgage rates (cell C24). Simulation will produce the following chart of interest rate vs. mortgage rate:

Read on: Lookup tables

 

ModelRisk

Monte Carlo simulation in Excel. Learn more

Tamara

Adding risk and uncertainty to your project schedule. Learn more

Navigation

FREE MONTE CARLO SIMULATION SOFTWARE

For Microsoft Excel

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

Download ModelRisk Basic now

FREE PROJECT RISK SOFTWARE

For Primavera & Microsoft Project

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

Download Tamara Basic now
-->