Markov Chain Simulation Model in Excel | Vose Software

ModelRisk example · General techniques

Markov Chains: Marital Status in 25 Years

Where will a group of 100 people be in 25 years? A Markov chain, simulated two ways.

  • Complexity: ★★★★★
  • Free Excel workbook (GU-17)
  • Needs ModelRisk: free trial

From the model’s saved run

Year 25 by state

The exact expected numbers and the two methods' simulated means; percentiles from VoseMarkovSample.

StateExpectedMean, Markov sampleMean, multinomial10th pct90th pct
Single0.70.70.7–2
Married44.744.844.63851
Separated8.28.28.2512
Divorced46.346.246.44053
Married in year 25 — both methods
Mean number in each state, year by year

The business question

A demographer follows a group of 100 adults: 43 single, 29 married, 11 separated and 17 divorced. Each year a person may move between these states. The chances depend only on their current state (a Markov chain). A married person, for example, stays married with 88% probability, separates with 8% and divorces with 4%. Over 25 years these small yearly moves add up.

The model simulates the group two ways (in one step with VoseMarkovSample, and year by year with VoseMultinomial) and computes the exact expected numbers with VoseMarkovMatrix.

Who it is for
Anyone modelling a population that moves between states: customers between segments, patients between health states, loans between ratings, machines between conditions.
Main outputs
The number in each state after 25 years: expected, and at P10 / P90 · the same from two simulation methods · the mean number in each state year by year · the 25-year transition matrix.

How the model works

Four sheets after this one, read from left to right. Each one feeds the next.

  1. Inputs sheet

    How many of the 100 people are in each state today.

  2. Chain sheet

    The one-year transition matrix (rows: the state a person was in; columns: the state they move to). VoseMarkovMatrix raises it to 25 years, which gives the expected numbers; VoseMarkovSample draws the numbers in year 25 directly.

  3. Years sheet

    The same with the multinomial method: each year VoseMultinomial splits each state's people across the four states, and the totals are next year's numbers. The outputs and checks are at the end.

  4. Results sheet

    Expected, simulated and percentile numbers in each state in year 25 from both methods, and the mean numbers year by year.

ModelRisk techniques

  • One-year transition matrix
  • 25-year matrix VoseMarkovMatrix
  • Population in one step VoseMarkovSample
  • Year-by-year moves VoseMultinomial
  • Output ranges and arrays

Find similar models

Product

The results in detail

How to read these results

  • After 25 years almost nobody is single: the matrix never moves anyone back to single, and 15% leave it each year. The rest settle at about 45 married, 8 separated and 46 divorced.
  • The 25-year matrix's rows are nearly equal (Chain sheet): where a person ends up hardly depends on where they started. That is the chain's long-run state, which these numbers approach.
  • The two methods give the same answer: VoseMarkovSample does in one step what the year-by-year multinomial table does in 25. Both means match the exact expectation from VoseMarkovMatrix.
  • Even with the probabilities known, chance alone spreads the outcome: the number married in year 25 ranges from about 38 to 51 (10th to 90th percentile) in a group of 100.

See it move. Open the workbook, change an input on the Inputs sheet and run the simulation: every table and chart on this page updates in Excel.

Inputs and assumptions

Every assumption in the model, with its uncertainty and where it comes from. The figures are illustrative.

The group today 4 inputs

How many of the 100 people are in each state.

AssumptionUnitValue
Single todayThe examples' group: 43 + 29 + 11 + 17 = 100 peoplepeople43
Married todaypeople29
Separated todaypeople11
Divorced todaypeople17

Units and conventions

Transition matrix
Row: this year's state. Column: next year's. Each row adds up to 100%.
Year 0
Today.

Key assumptions and limitations

Markov
Next year's state depends only on this year's, not on how a person got there.
Fixed group
Nobody joins or leaves the group; the yearly probabilities do not change.
Data
The numbers and the matrix are those of ModelRisk's earlier Marital Status in 25 Years and Markov Chain Multinomial Method examples.

How to use it

  1. Set today's numbers on the Inputs sheet and the transition matrix on the Chain sheet (each row must add up to 100%).
  2. Run a simulation from the ModelRisk ribbon (10,000 samples with a fixed seed are set).
  3. Use VoseMarkovSample for the end state. Keep the year-by-year table (Years sheet) when you need the path, or when the probabilities change from year to year.

ModelRisk functions used

VoseMarkovMatrix
The 25-year transition matrix: the one-year matrix to the power 25.
VoseMarkovSample
The numbers in each state after 25 years, drawn in one step.
VoseMultinomial
Each year, how a state's people split across the states they move to.
VoseOutput
The numbers in year 25 from both methods.
VoseSimMean, VoseSimPercentile
Statistics of the simulation and the year-by-year means.

Further reading