I'm trying to estimate performance of an investment account over 20 years. The question is, have I set up the Monte Carlo simulation correctly? I've used Excel. I've assumed 8% average return and 13% volatility and a starting account size of $100,000.
I generate a random return using the function
norminv(rand(),mean,StDev). I set the mean parameter to \$100,000 * 0.08, and the standard deviation parameter to \$100,000 * 0.13. That results in a random dollar gain or loss that I add to my starting account size. That generates my simulated return for the first year.
On the next row I do exactly the same except I base the randomly generated return on the result in the row above, not on the starting value of \$100,000.
I do that 18 more times in next 18 rows. That gives me one simulation of 20 years of returns.
I then do the same in the 99 adjacent columns. That gives me 100 simulations of 20 years of returns.
Did I do this correctly?
I take the median value of each row as the "representative value" for each year. I calculate the 97.5, and 2.5 percentile for each row to give me a 95% prediction interval. Is that correct?