How to generate normal random correlated variables in excel?

Discuss all general (i.e. non-personal) investing questions and issues, investing news, and theory.
Post Reply
Topic Author
jmk
Posts: 482
Joined: Tue Nov 01, 2011 7:48 pm

How to generate normal random correlated variables in excel?

Post by jmk » Mon Jul 15, 2019 12:12 pm

I'm trying to simulate normal distribution returns for two assets with given means, sds and correlation. I know the variances and the covariance between them. Generating normal random numbers is easy in excel, with the Norm.inv(rand(),mean,sd) formula, and if there is no correlation between the two, this is not a problem.

How do you generate the random numbers for the second asset so it maintains its mean and sd but but also its given correlation r with the first variable?

I realize with multiple variables you'd have to use a macro for Cholesky decomposition/factoring on the covariance matrix. (Is there any free add-ons that do this?)

But is there a shortcut simple vanilla generation formula if you are only using 2 variables?

Thanks!

jsprag
Posts: 89
Joined: Sun Nov 26, 2017 11:25 am

Re: How to generate normal random correlated variables in excel?

Post by jsprag » Mon Jul 15, 2019 2:18 pm

If I understand your question correctly, then I think this might work...

Given:
MeanX, StDevX are mean and standard deviation for simulated asset X
MeanY, StDevY are mean and standard deviation for simulated asset Y
r = assumed correlation between X, Y

First, generate two intermediate lists, A and B, of desired length of random numbers using just

Code: Select all

norm.inv(rand(),0,1)
Then create your X and Y lists as follows (for each 'n' in the length of your list)

Code: Select all

Xn = MeanX + StDevX * (An)

Code: Select all

Yn = MeanY + StDevY * (An * r + Bn(1-r^2)^0.5)

ohai
Posts: 921
Joined: Wed Dec 27, 2017 2:10 pm

Re: How to generate normal random correlated variables in excel?

Post by ohai » Mon Jul 15, 2019 2:23 pm

I forget the exact formula, but there is a valid approach along the lines of what the guy ahead has proposed.

Ping Pong
Posts: 473
Joined: Fri Feb 20, 2009 12:51 pm

Re: How to generate normal random correlated variables in excel?

Post by Ping Pong » Mon Jul 15, 2019 2:32 pm

Just make a copy of the set. The correlations will be perfect.

jsprag
Posts: 89
Joined: Sun Nov 26, 2017 11:25 am

Re: How to generate normal random correlated variables in excel?

Post by jsprag » Mon Jul 15, 2019 2:32 pm

You might also try the NtRand add-in for excel, particularly the NTBINORMDIST and the NTRANDMULTINORM functions.

Capricorn51
Posts: 58
Joined: Fri Jan 11, 2019 2:26 pm

Re: How to generate normal random correlated variables in excel?

Post by Capricorn51 » Mon Jul 15, 2019 2:38 pm

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)

Topic Author
jmk
Posts: 482
Joined: Tue Nov 01, 2011 7:48 pm

Re: How to generate normal random correlated variables in excel?

Post by jmk » Tue Jul 16, 2019 10:06 am

Capricorn51 wrote:
Mon Jul 15, 2019 2:38 pm
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)
This seems to work Capricorn and JSPrag!!! How did you both derive the equations?
The "missing piece" was that I wasn't using random standard normal numbers as my seeds.

I also discovered there is a free excel add-on call SimTools that includes a "corrand" function plus lots of other neat features. Will also check out the add-ons you recommended.

jsprag
Posts: 89
Joined: Sun Nov 26, 2017 11:25 am

Re: How to generate normal random correlated variables in excel?

Post by jsprag » Tue Jul 16, 2019 2:49 pm

jmk wrote:
Tue Jul 16, 2019 10:06 am
Capricorn51 wrote:
Mon Jul 15, 2019 2:38 pm
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)
This seems to work Capricorn and JSPrag!!! How did you both derive the equations?
The "missing piece" was that I wasn't using random standard normal numbers as my seeds.

I also discovered there is a free excel add-on call SimTools that includes a "corrand" function plus lots of other neat features. Will also check out the add-ons you recommended.
I had an R project that required something similar. I translated that code snippet, then tortured Google until it provided corroborating evidence.

Capricorn51
Posts: 58
Joined: Fri Jan 11, 2019 2:26 pm

Re: How to generate normal random correlated variables in excel?

Post by Capricorn51 » Tue Jul 16, 2019 3:07 pm

Here is a textbook proof. Not sure whom the credit for the original algorithm is due to:

https://www.probabilitycourse.com/chapt ... l_dist.php

Post Reply