An example of a Monte Carlo simulation risk analysis model for interest and mortgage rates

Technical difficulty: 2

Techniques used: Monte Carlo simulation in Excel

ModelRisk functions used: VosePert

Model description

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

For example, we may have both next year's interest rate and next year's mortgage rate represented as distributions. Clearly, these two variables are strongly positively correlated, i.e. if the interest rate turns out to be high, the mortgage rate should also be high, and vice versa. If we neglect to model the inter-dependency between these two variables, 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%.

In this example model, the interest rate is modeled as an independent random variable, and the distribution of the mortgage rate depends on the value of the interest rate.

The interest rate is modeled 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 first step is 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 C20. The polynomial equations are cells C22:C24. Now we can finally sample from a PERT distribution constructed from the minimum most likely and maximum of those mortgage rates (cell C26). Simulation will produce the following chart of interest rate vs. mortgage rate: