How do I create a Markov Chain in Excel to simulate random correlated stock prices?

No, this isn't a homework assignment. I model leveraged investment strategies and want to find out the effect of diversification across several funds based upon different indexes (mid-cap, foreign, sectors, etc). These indexes are correlated, although the correlation changes over time.



I have used historical data and multiple random stock price histories, but need to better understand correlation to have a higher degree of confidence in the results. I have not been able to create multiple random correlated stock price histories yet.



I will likely write this information up into an article, so the sources need to be authoritative.How do I create a Markov Chain in Excel to simulate random correlated stock prices?
http://www.me.utexas.edu/~jensen/ORMM/fr



Grab stocanal.xla which is an excel addon. This allows you to create markov, chains or markov process models. It's a pretty decent add on. This will allow you to actually demonstrate the process which I would think you might find valuable. Once you download it and open excel look for the toolbar OR_MM and that dropdown shows the different stochastic analysis add-ons.How do I create a Markov Chain in Excel to simulate random correlated stock prices?
I think it would me more usefull for you if some nice person actually showed you the simple formula in excel, rather than using someone else's addinn (which you have no idea how it works or if it works correctly)

Report Abuse

How do I create a Markov Chain in Excel to simulate random correlated stock prices?
I am not 100% sure but this may be beyond the capabilities of Excel without an add-in like Crystal Ball or @RISK. At the very least, the add-ins should make the analysis you are doing trivial vs. a massive exercise in programming.How do I create a Markov Chain in Excel to simulate random correlated stock prices?
Use the link below for an add-in. It's the one called stochmod.xla.