In: Statistics and Probability
Jennifer was just born and her parents have decided to save money to pay for her college education. They will invest $15,000 right now, and $10,000 each year on her future birthdays for the next 17 years (i.e. $10,000 when she turns 1, 2, 3, … 16, 17). They are bullish on America and are investing all of this money in the stock market. The principal and returns are allowed to accumulate (i.e. are reinvested) in the equities market and no distribution of funds will be done before Jennifer starts college.
Assume that the returns of the stock market are normally distributed and are independent from year to year with a mean (µ) return of 7% and a standard deviation (σ) of 10%. All investments returns are tax free.
REQUIRED - Jennifer is planning to go to college starting on her 18th birthday; hence it is important for the parents to know how much money will have accumulated in her College Fund at that time (Call this the ACCUMULATION of the College Fund). Construct a Monte-Carlo simulation model in EXCEL or CRYSTAL BALL (EXCEL add-on) to model the problem. Run the simulation for at least 1,000 trials to answer the following questions:
Please use excel and show all the formulas.
Setting up the table in Excel:
Formulas used:
Copy the formulas from row 3 to the rest of the rows. Please note that only formula that is different in row 3 from row 2 is the cumulative money after which rest of the cells below use the same formula from row 3.
The return on market uses a random number generator for probability and uses 0.07 which is the expected return and 0.1 which is the standard deviation of the returns from the stock market.
Principals and returns are invested back with the new money invested again in the next year.
Hence, the number in cell F19 will give the final amount accumulated when Jen turns 18.
For conducting 1000 trials, we will use Excel's data table function.
Setup a table like this for 1000 rows:
Use the final accumulated value cell for the column header. (You put =F19 in cell I1)
Select the entire range of 1000 cells including the header row and Then go to Data --> What-If-Analysis --> Data table --> Leave row input cell blank and enter any empty cell as Column input cell. It will give you 1000 simulations of the value we calculated.
Next, we can calculate the mean and standard deviations of these accumulated values:
MEAN | $ 377,705.58 |
STANDARD DEVIATION | $ 106,547.96 |
Please note that the mean and standard deviation will be changing everytime because the simulation and data table is dynamic, but it will stay around the same range. Don't worry this is expected.
--------------------------------------------------------------------------------------------------------------------------
Probability that the accumulation is greater than given values is:
P(Accumulation> $300,000) | 0.775348077 |
P(Accumulation> $350,000) | 0.620042348 |
P(Accumulation> $400,000) | 0.442200647 |
Formulas: (Where L2 and L3 hold the mean and standard deviations we just calculated above)
--------------------------------------------------------------------------------------------------------------------------
For standard deviation = 0, simply go back to column D where return on market is calculated.
Change the first cell formula to NORMINV(RAND(),0.07,0), copy this formula down to other cells; all values and results will update automatically.
--------------------------------------------------------------------------------------------------------------------------
Yes, we can calculate confidence interval of accumulation.
We know the mean, we know the standard deviation, the z-value of 95% CI is 1.96
Using those we can calculate the 95% CI.
n | 1000 |
z | 1.96 |
95 % CI: Lower limit | $ 379,224.99 |
95% CI: Upper limit | $ 392,600.87 |
Formula:
Now, we know the Confidence interval as well. Please note that these results will also change as mean and standard deviation change, which is also expected.
If you need to report one result for all the questions, you can take any particular run but make sure that CI is reported for the same result as the mean and standard deviation i.e. CI should not be calculated using different mean and standard deviation.
--------------------------------------------------------------------------------------------------------------------------
Hope I could explain well. Kindly comment if any additional clarifications are required. Thanks :)