Take the 2-minute tour ×
Quantitative Finance Stack Exchange is a question and answer site for finance professionals and academics. It's 100% free, no registration required.

I am building a simple stochastic model for learning purposes in excel. I took daily data for the SPY since 1/1/1993. I computed the daily log returns and found that the SPY has had an average daily return of $μ=.034$ and a daily volatility of $σ=1.22$ . So using a stochastic model,

$$dS_t=S_t μ dt+S_t σdW_t,$$

and Ito's derived solution to the SDE,

$$S_t/S_{t-1}= e^{(μ dt+σdW_t)}$$

Since, I computed daily returns and daily volatility and not yearly returns, the scaling factor, $dt$ can be ignored. I used the norminv(rand(),0,1) to generate the wiener process (mean 0, variance of 1). I am actually not sure about this point?? Should I make the the standard deviation time increment? So if I am simulating daily prices, for 10 days out, the variance will $sqrt(1/10)$ for the first day, $sqrt(2/10)$... and so forth.

I am also trying to generate a 2 standard deviation (fan) chart for the forecast, such that 95% of the paths lay within the bands. However, I noticed my bands got extremely big and small, on both sides fairly quickly. I simply calculated the returns using the following formulas:

$$S_t/S_{t-1}= e^{(.034-2*(1.22))}$$ and $$S_t/S_{t-1}= e^{(.034+2*(1.22))}$$

Why are the bands so large? Any ideas? I really appreciate your help.

share|improve this question
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Browse other questions tagged or ask your own question.