Jennifer was just born and her
Jennifer was just born and her parents have decided to savemoney to pay for her college education. They willinvest $15,000 right now, and $10,000 each year on her futurebirthdays for the next 17 years (i.e. $10,000 when she turns 1, 2,3, … 16, 17). They are bullish on America and areinvesting all of this money in the stock market. The principal andreturns are allowed to accumulate (i.e. are reinvested) in theequities market and no distribution of funds will be done beforeJennifer starts college.
Assume that the returns of the stock market are normallydistributed and are independent from year to year with a mean (µ)return of 7% and a standard deviation (σ) of 10%. All investmentsreturns are tax free.
REQUIRED – Jennifer is planning to go tocollege starting on her 18th birthday; hence it isimportant for the parents to know how much money will haveaccumulated in her College Fund at that time (Call this theACCUMULATION of the College Fund). Construct a Monte-Carlosimulation model in EXCEL or CRYSTAL BALL (EXCEL add-on) to modelthe problem. Run the simulation for at least 1,000 trials to answerthe following questions:
-
- What is the mean and the standard deviation of theACCUMULATION?
-
- What is the probability that the ACCUMULATION is over $300,000?over $350,000? over $400,000?
-
- What is the ACCUMULATION if the standard deviations is zero.(Fixed return)
-
- Can you compute a 95% confidence interval estimate ofACCUMULATION? If so, what is it? If not, why not?
-
- Can this problem be solved without using simulation? If so,how?
Please use excel and show all the formulas.
Answer:
Setting up the table in Excel:
Formulas used:
Copy the formulas from row 3 to the rest of the rows. Pleasenote that only formula that is different in row 3 from row 2 is thecumulative money after which rest of the cells below use the sameformula from row 3.
The return on market uses a random number generator forprobability and uses 0.07 which is the expected return and 0.1which is the standard deviation of the returns from the stockmarket.
Principals and returns are invested back with the new moneyinvested again in the next year.
Hence, the number in cell F19 will give the final amountaccumulated when Jen turns 18.
For conducting 1000 trials, we will use Excel’s data tablefunction.
Setup a table like this for 1000 rows:
Use the final accumulated value cell for the column header. (Youput =F19 in cell I1)
Select the entire range of 1000 cells including the header rowand Then go to Data –> What-If-Analysis –> Data table–> Leave row input cell blank and enter any empty cell asColumn input cell. It will give you 1000 simulations of the valuewe calculated.
Next, we can calculate the mean and standard deviations of theseaccumulated values:
| MEAN | $ 377,705.58 |
| STANDARD DEVIATION | $106,547.96 |
Please note that the mean and standard deviation will bechanging everytime because the simulation and data table isdynamic, but it will stay around the same range. Don’t worry thisis expected.
————————————————————————————————————————–
Probability that the accumulation is greater than given valuesis:
| 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 deviationswe just calculated above)
————————————————————————————————————————–
For standard deviation = 0, simply go back to column D wherereturn on market is calculated.
Change the first cell formula to NORMINV(RAND(),0.07,0), copythis formula down to other cells; all values and results willupdate automatically.
————————————————————————————————————————–
Yes, we can calculate confidence interval of accumulation.
We know the mean, we know the standard deviation, the z-value of95% 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 thatthese results will also change as mean and standard deviationchange, 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 reportedfor the same result as the mean and standard deviation i.e. CIshould not be calculated using different mean and standarddeviation.
————————————————————————————————————————–
Hope I could explain well. Kindly comment if any additionalclarifications are required. Thanks 🙂
"Our Prices Start at $11.99. As Our First Client, Use Coupon Code GET15 to claim 15% Discount This Month!!"
