Tell me more ×
Cross Validated is a question and answer site for statisticians, data analysts, data miners and data visualization experts. It's 100% free, no registration required.
up vote 1 down vote favorite
1

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):

Excel Spreadsheet of Simulation

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!

share|improve this question
I think n is the number of trials which represents the number of times you generate the results of the investment plan. Why do you need simulation? Is it just to illustrate the variability? If there is no compounding the rates Ri are iid normal each year and the interest earned is Ii=20000 Ri so they are iid normal also and the sum of iid normals is also normal with known mean and variance. So you have the distribution for the total interest added and the investment of 20000k + total interest also has a known normal distribution also where k is the number years of investing. – Michael Chernick May 13 '12 at 13:11
The assignment requires that a simulation is to be done through excel. I changed the "Return" to just "Savings*Raw Rate of Return" as you recommended. I also added the "Normal Distribution" where it is "Savings*Years+Interest Return". Now is the question to run the trials of 17years is as 1 set,and is to be reproduced 1000 more times? – user22910 May 13 '12 at 13:35
2  
Yes, repeat it 999 more times. It would make better sense to align the rows, so you should end up with 17x4000 table. (You could also hit F9 1000 times, but then you won't be able to summarize the results.) You also don't seem to get the compound interest: the rate should apply to the amount in the end of the previous year, not to the amount Jane and John are investing each year. So you should have 20000 on January 1, year 1, 20000*(1+return(1)) on December 31, year 1, 20000*(1+return(1)) + 20000 on January 1, year 2, (20000*(1+return(1)) + 20000)*(1+return(2)) on December 31, year 2, etc. – StasK May 14 '12 at 2:04

Know someone who can answer? Share a link to this question via email, Google+, Twitter, or Facebook.

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.