excel graphing

[skajdfjksdfd]

hello,
I have several columns of data, all of which are prices... I have calculated the volatilities and the estimated correlations of the prices but I am wondering how I can use this info as input to a simulator using excel.

 

[Sanket Patel]

Can you be a bit more explicit? Better yet, post your excel file.

 

[skajdfjksdfd]

attached is the excel file...

 

[Sanket Patel]

Looks like you have 2 sets of prices and the returns associated with them. Are you looking to simply simulate prices based upon the parameter estimates of the two series? Do you want to simulate the two prices independent or correlated series?

 

[skajdfjksdfd]

yes, simulate prices based upon the parameter estimates of the two series... I had planned on simulating the two prices independent, but correlated series sounds like it might be better

 

[Sanket Patel]

The most basic simulation will be of the form:

(ds = \mu S dt + \sigma S dz)

where (\mu) and (\sigma) are your estimates of drift and volatility, respectively and (dz) is the stochastic processes.
In your case, though, since you have 2 series, you will have 2 processes:

(ds_1 = \mu_1 S_1 dt + \sigma_1 S_1 dz_1) and
(ds_2 = \mu_2 S_2 dt + \sigma_1 S_2 dz_2)

The only real issue is to make sure the two processes are correlated, and that is taken care of by a form of Cholesky decomposition:

(dz_1 = dz_1)
(dz_2 = \rho dz_1 + dz_2\sqrt{1-\rho^{2}})

where (\rho) is the correlation.

Then it's just a matter of plug and chug. You can use the command "NORMSINV(RAND())" to generate standard normal variables. But remember that the volatility is proportional to the square root of time, thus:

( dz = x\sqrt{dt}) ; where x~n(0,1)

Take a look at the sample sheet I posted.