Generating Correlated Random Values in Excel

[Randy]

Hi Mike,

Thanks so much for your advice. I think that I have it figured out,
but can you confirm this for me? I can't afford to screw this up.

If mean(inflation) = .031
stdev(inflation) = .047
mean(stock return) = .106
stdev(stock return) = .204

Column Headers:
A B C D
1 Z1 Z2 Inflation Stock Return

Data Generation Formulas

A2 =NORMINV(RAND(),0,1)
B2 =NORMINV(RAND(),0,1)
C2 =0.031+0.047*A2
D2 =0.106+0.204*(A2*rho+B2*(1-rho^2)^0.5)

Are these formulas correct to generate the random data? Particularly
cell D2. Does this give me a complete value that takes into
consideration both the correlated part of the value and the
uncorrelated part? It looks like it does. I copied this down many
rows and ran a histogram and it looks good to me, but I just wanted to
confirm before I ran with it.

Thank you again, Mike.

Randy Eastland

+++++++++++++++++++++++++

Randy Eastland -

I don't know where data is available, but you can get correlated random
values as follows:

If Z1 and Z2 are each independent standard normal random variables,
i.e., each is NORMINV(RAND(),0,1), then to get X and Y with correlation
rho, use

X = MeanX + StDevX*Z1

Y = MeanY + StDevY*(Z1*rho + Z2*(1-rho^2)^0.5)

Alternatively, search google groups for "excel correlated normal" or
similar keywords, without the quotes.

Or, download risk230e.xla from the "Decision Modeling Using Excel" page
of my university web site, and use its RandBiNormal function, which is
described in RiskSim's built-in Help.

- Mike Middleton

+++++++++++++++++++++++++

At 14:41 2005-01-05, you wrote:

Hello Mike,

A few weeks ago, you responded to a post that I had placed on an Excel
VBA message board when I was struggling with the ATPVBAEN.XLA!Random
function. Your advice was excellent and has help me considerably. I
wondered it I could ask you another question.

Actually, 2 questions:

1. I'm looking for correlation coefficients between the general rate
of inflation, overall stock market returns, and overall bond market
returns. Do you know of a resource where I might find these values?

2. In building my table of randomly generated inflation rates, stock
market returns, and bond market returns, I am currently using the
formula that you suggested on the message board
(=NORMINV(RAND(),Mean,StDev)). This formula works excellently, but it
does not consider the fact that these values might be correlated (thus
the first question). Assuming that I can find r-squared values for a)
inflation with stock returns, and b) inflation with bond
returns, can you offer any advice as to how I might alter this formula
to take correlation into consideration?

Boiled down, my table is basically three columns. A = inflation rate,
B = stock market returns, and C = bond market returns. My thought is
to keep your formula intact for inflation, letting this value be
randomly generated around a mean and standard deviation. B and C then
need to take into consideration that there is at least some correlation
with the inflation rate. My knowledge of stats began and pretty much
ended back in college. I have poured through some stats books that I
was able to gather, but I can't
figure out how to do this reliably. Can you help?

Thank you very much, Mike. I'm sorry to bother you. If this is too
much to ask, I understand.

Randy Eastland

 

[Michael R Middleton]

Randy -

Two things to check:

(1) Your formulas seem OK. But you'll need to either (a) replace "rho" with
a numerical value (between -1 and +1) or a reference to a cell containing a
value or (b) define a name "rho" similarly.

(2) A histogram is useful for checking each variable separately. But you
should also use an XY (Scatter) chart to check the results for a hundred or
so pairs of Inflation and Stock Return.

- Mike

www.mikemiddleton.com

++++++++++++++++++++++++++

 

[Randy]

Thanks, Mike. I appreciate all of your help with this.

Randy