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

1. What is the mean and the standard deviation of theACCUMULATION?
1. What is the probability that the ACCUMULATION is over \$300,000?over \$350,000? over \$400,000?
1. What is the ACCUMULATION if the standard deviations is zero.(Fixed return)
1. Can you compute a 95% confidence interval estimate ofACCUMULATION?   If so, what is it? If not, why not?
1. Can this problem be solved without using simulation? If so,how?

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. 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.775348 P(Accumulation> \$350,000) 0.620042 P(Accumulation> \$400,000) 0.442201

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!!"
Calculate the price
Pages (550 words)
\$0.00
*Price with a welcome 15% discount applied.
Pro tip: If you want to save more money and pay the lowest price, you need to set a more extended deadline.
We know how difficult it is to be a student these days. That's why our prices are one of the most affordable on the market, and there are no hidden fees.

Instead, we offer bonuses, discounts, and free services to make your experience outstanding.
How it works
Receive a 100% original paper that will pass Turnitin from a top essay writing service
step 1
Fill out the order form and provide paper details. You can even attach screenshots or add additional instructions later. If something is not clear or missing, the writer will contact you for clarification.
Pro service tips
How to get the most out of your experience with brilliantassignmenthelp.com
One writer throughout the entire course
If you like the writer, you can hire them again. Just copy & paste their ID on the order form ("Preferred Writer's ID" field). This way, your vocabulary will be uniform, and the writer will be aware of your needs.
The same paper from different writers
You can order essay or any other work from two different writers to choose the best one or give another version to a friend. This can be done through the add-on "Same paper from another writer."
Copy of sources used by the writer
Our college essay writers work with ScienceDirect and other databases. They can send you articles or materials used in PDF or through screenshots. Just tick the "Copy of sources" field on the order form.
Testimonials
See why 20k+ students have chosen us as their sole writing assistance provider
Check out the latest reviews and opinions submitted by real customers worldwide and make an informed decision.
11,595
Customer reviews in total
96%
Current satisfaction rate
3 pages
Average paper length
37%
Customers referred by a friend
OUR GIFT TO YOU
15% OFF your first order
Use a coupon FIRST15 and enjoy expert help with any task at the most affordable price.
Claim my 15% OFF Order in Chat

## Sometimes it is hard to do all the work on your own

Let us help you get a good grade on your paper. Get professional help and free up your time for more important courses. Let us handle your;

• Dissertations and Thesis
• Essays
• All Assignments

• Research papers
• Terms Papers
• Online Classes