Generating Correlated Random Values in Excel

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

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" <[email protected]> wrote in message
news:[email protected]...
> 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
>

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

Randy

Michael R Middleton wrote:
> 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" <[email protected]> wrote in message
> news:[email protected]...
> > 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
> >