0
$\begingroup$

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..

Improve this question
$\endgroup$
5
  • 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
    Commented Sep 22, 2017 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
    Commented Sep 22, 2017 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
    Commented Sep 22, 2017 at 9:36
  • $\begingroup$ That's was the misunderstanding, thank you !! $\endgroup$
    – AlexM
    Commented Sep 22, 2017 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
    Commented Sep 25, 2017 at 7:32

0

Reset to default

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.