Simple markov chain simulation

[abberyfarm]

Hi there,

Would anybody know how to simulate the states of a markov chain in excel given the transition probability matrix and a random number.

Here is an example:

I have random numbers in column A and a transition probability matrix in yellow.

Say the initial state is State A how could simulate what the next 9 states will be?

Thanks

Excel Workbook
	A	B	C	D	E	F	G
2	Random number	*	*	Transition Matrix			*
3	0.72	*	*	State A	State B	State C	*
4	0.75	*	State A	0.2	0.5	0.3	*
5	0.02	*	State B	0.1	0.7	0.2	*
6	0.09	*	State C	0.5	0.4	0.1	*
7	0.63	*	*	*	*	*	*
8	0.80	*	*	*	*	*	*
9	0.35	*	*	*	*	*	*
10	*	*	*	Steps	Simulate states	*	*
11	*	*	*	1	State A (initial state)	*	*
12	*	*	*	2	?	*	*
13	*	*	*	3	?	*	*
14	*	*	*	4	?	*	*
15	*	*	*	5	?	*	*
16	*	*	*	6	?	*	*
17	*	*	*	7	?	*	*
18	*	*	*	8	?	*	*
19	*	*	*	9	?	*	*
20	*	*	*	10	?	*	*
Sheet1

This thread is available here also

http://www.ozgrid.com/forum/showthread.php?t=170671&p=632216#post632216

 

[abberyfarm]

My apologies, I don't know why there are *s in the above post. Here is a cleaner version

[TABLE="width: 498"]
<colgroup><col><col span="6"></colgroup>[TR]
[TD]

Excel Workbook
	A	B	C	D	E	F	G
2	Random number			Transition Matrix
3	0.72			State A	State B	State C
4	0.75		State A	0.2	0.5	0.3
5	0.02		State B	0.1	0.7	0.2
6	0.09		State C	0.5	0.4	0.1
7	0.63
8	0.80
9	0.35
10				Steps	Simulate states
11				1	State A (initial state)
12				2	?
13				3	?
14				4	?
15				5	?
16				6	?
17				7	?
18				8	?
19				9	?
20				10	?
Sheet1

[/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 3"][/TD]
[TD][/TD]
[/TR]
[/TABLE]