The assignment is to run a simulation on excel to figure out the probabilities on obtaining a certain goal:
John and Jane Doe are planning to save money to pay a house for their 6-month-old son, Patrick. They have decided that they would like to have $500,000 saved by the time Patrick is ready for college 17 years from today:
John and Jane are planning to save $20,000 (at the beginning of) each year (ignore taxes). Assume that the return each year varies and is sampled from the same normal distribution (mean rate of return is 4%, standard deviation is also 10%, i.e. one standard deviation away is either a -6% return or an 14% return). (Hence in each trial there are 17 different rates of return, one for each year.) Run a simulation of this investment strategy with at least 1000 trials.
Here's my attempt on the problem (click on the photo to reveal a larger version):
I am not to sure on how to continue from there. Am I missing other components to solve the problem? Is 'n' the number of trials? Any advice would be appreciated!
Best Answer
After you fix the problems in the comments with compound interest rate, you should write a VB script in Excel to do the simulation. In essence, you're going to replace the rand() column in each iteration, then log the final result. Usually with Monte Carlo simulations, you want to bin the result because you are looking at the confidence intervals, i.e. how much to they have at 95% confidence (95% chance they will have at least that much)., 90% ...