Monte Carlo simulation of investment account

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?

• What is the logic on which you based your function? – Joel W. Aug 4 '14 at 11:48
• Not sure I understand the question. I just want to know if I set up the monte carlo simulation correctly. If the question is, what do you hope to learn from the simulation, the answer is that I think the median outcome of the 100 simulations is my "best guesstimate" of what the account will be worth in 20 years (assuming assumptions prove to be correct), and the 95% upper and lower bounds will give me my best guesstimate of the lowest and highest values of that account in 20 years. – user53290 Aug 4 '14 at 14:43