I'm trying to simulate $N$ correlated assets in Excel in order to estimate a basket option price.

For 2 assets, I correlated the two random variables $X_1$ and $X_2$ and then simulate the BlackScholes price increments. At the end, the correlation computed with Excel Function for the two stock prices is completely different.

So how do you correlate two BlackScholes stock prices with a correlation $\rho$ ?

I'd like to complete this step correctly before extending to $N$ assets with Cholesky decomposition..

  • 1
    $\begingroup$ I don't really see what your question is. As you said, for each step you generate to independent $\mathcal{N}(0, 1)$ normals, e.g. $X_1$ and $X_2$ and then obtain correlated normals $Y_1 = X_1$ and $Y_2 = \rho X_1 + \sqrt{1 - \rho^2} X_2$. Or is the last equation what you were missing? $\endgroup$ – LocalVolatility Sep 22 '17 at 9:07
  • $\begingroup$ I'm currently generating $Y_1$ and $Y_2$ with the formula you indicate. But for example if I take $\rho = 0.2$, the two correlated $Y_1$ and $Y_2$ roughly have 0.2 correlation, however the correlation between my two stock prices $S_1$ and $S_2$ is completely random (0.2 , 0, 0.8 etc...) $\endgroup$ – AlexM Sep 22 '17 at 9:32
  • 3
    $\begingroup$ I don't know if that is where your misunderstanding is but you are correlating log returns and not prices. When you follow the above approach and then compute a sufficiently large sample of log returns, then you should find that the sample correlation is roughly $\rho$. $\endgroup$ – LocalVolatility Sep 22 '17 at 9:36
  • $\begingroup$ That's was the misunderstanding, thank you !! $\endgroup$ – AlexM Sep 22 '17 at 9:57
  • $\begingroup$ Can we also check that you are simulating many paths? If you're just generating one path, the the correlation of that is also a random number, which should have a mean matching that of your input. $\endgroup$ – will Sep 25 '17 at 7:32

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.