How to generate normal random correlated variables in excel?
How to generate normal random correlated variables in excel?
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 addons that do this?)
But is there a shortcut simple vanilla generation formula if you are only using 2 variables?
Thanks!
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 addons that do this?)
But is there a shortcut simple vanilla generation formula if you are only using 2 variables?
Thanks!
Re: How to generate normal random correlated variables in excel?
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
Then create your X and Y lists as follows (for each 'n' in the length of your list)
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)
Code: Select all
Xn = MeanX + StDevX * (An)
Code: Select all
Yn = MeanY + StDevY * (An * r + Bn(1r^2)^0.5)
Re: How to generate normal random correlated variables in excel?
I forget the exact formula, but there is a valid approach along the lines of what the guy ahead has proposed.
Re: How to generate normal random correlated variables in excel?
Just make a copy of the set. The correlations will be perfect.
Re: How to generate normal random correlated variables in excel?
You might also try the NtRand addin for excel, particularly the NTBINORMDIST and the NTRANDMULTINORM functions.

 Posts: 58
 Joined: Fri Jan 11, 2019 2:26 pm
Re: How to generate normal random correlated variables in excel?
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*(1rho^2)^0.5)
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*(1rho^2)^0.5)
Re: How to generate normal random correlated variables in excel?
This seems to work Capricorn and JSPrag!!! How did you both derive the equations?Capricorn51 wrote: ↑Mon Jul 15, 2019 2:38 pmIf 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*(1rho^2)^0.5)
The "missing piece" was that I wasn't using random standard normal numbers as my seeds.
I also discovered there is a free excel addon call SimTools that includes a "corrand" function plus lots of other neat features. Will also check out the addons you recommended.
Re: How to generate normal random correlated variables in excel?
I had an R project that required something similar. I translated that code snippet, then tortured Google until it provided corroborating evidence.jmk wrote: ↑Tue Jul 16, 2019 10:06 amThis seems to work Capricorn and JSPrag!!! How did you both derive the equations?Capricorn51 wrote: ↑Mon Jul 15, 2019 2:38 pmIf 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*(1rho^2)^0.5)
The "missing piece" was that I wasn't using random standard normal numbers as my seeds.
I also discovered there is a free excel addon call SimTools that includes a "corrand" function plus lots of other neat features. Will also check out the addons you recommended.

 Posts: 58
 Joined: Fri Jan 11, 2019 2:26 pm
Re: How to generate normal random correlated variables in excel?
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
https://www.probabilitycourse.com/chapt ... l_dist.php