Question

In: Statistics and Probability

Jennifer was just born and her parents have decided to save money to pay for her...

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:

    1. What is the mean and the standard deviation of the ACCUMULATION?
    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 of ACCUMULATION?   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.

Solutions

Expert Solution

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


Related Solutions

(TCO 5) Jane’s parents have created a savings account to save for her college education. If...
(TCO 5) Jane’s parents have created a savings account to save for her college education. If they invest $1,000 a year at 6% interest beginning on her first birthday, how much will be in the account when she reaches age 18? (TCO 5) You own a contract that promises an annuity cash flow of $250 year-end cash flows for each of the next 3 years. (Note: The first cash flow is exactly 1 year from today). At an interest rate...
You want to save some money for graduate school. To do this you have decided that...
You want to save some money for graduate school. To do this you have decided that you will put $10,000 in the bank at the beginning of each of the next 7 years. The bank has agreed to pay you 8 percent nominal interest compounded annually. How much money will you have in the bank 7 years from today? A. $17,138.24 B. $89,228.03 C. $96,366.28 D. $56,228.80 E. Something Else You have just purchased a Scratch-and-Sniff lottery ticket. Scratching on...
You are going to save money for your son’s education. You have decided to place $4,710...
You are going to save money for your son’s education. You have decided to place $4,710 every half year at the end of the period into a saving account earning 4.31 percent per year, compounded semi-annually for the next 13 years. How much money will be in the account at the end of that time period? Round the answer to two decimal places.
Today is your cousin’s 12th birthday. Her parents are preparingto save for her college tuition....
Today is your cousin’s 12th birthday. Her parents are preparing to save for her college tuition. They decide that they will save the equal amount of $10,000 into a savings account, starting today and continuing every birthday up to and including her 20th birthday. Assume the savings account pays 7% interest compounded annually. If instead they gave a lump-sum amount TODAY, how much money will your cousin’s parents need to deposit in the account to give her to give the...
You have just entered an MBA program and have decided to pay for your living expenses...
You have just entered an MBA program and have decided to pay for your living expenses using a credit card that has no minimum monthly payment. You intend to charge $1, 000 per month on the card for the next 21 months. The card carries a monthly interest rate of 1%. How much money will you owe on the card 22 months from now, when you receive your first statement postgraduation?
You have just entered college and have decided to pay for your living expenses using a...
You have just entered college and have decided to pay for your living expenses using a credit card that has no minimum monthly payment. You intend to charge $900 per month on the card for the next 45 months. The card carries a monthly interest rate of 0.9 %. How much money will you owe on the card 46 months from​ now, when you receive your first statement​ post-graduation? After 45 months you will owe $____. (Round to the nearest...
Your new baby was born yesterday. To save for her education, you decide to invest in...
Your new baby was born yesterday. To save for her education, you decide to invest in a 529 plan and will make QUARTERLY contributions until your child enters the great UNLV when she turns 18. That is, you will save for the next 17 years (Or should it be 18 years? Think about it), and the contribution will be made at the END of each quarter. You expect that the 529 plan will return 8.5% per year with quarterly compounding....
Your new baby was born yesterday. To save for her education, you decide to invest in...
Your new baby was born yesterday. To save for her education, you decide to invest in a 529 plan and will make QUARTERLY contributions until your child enters the great UNLV when she turns 18. That is, you will save for the next 17 years (Or should it be 18 years? Think about it), and the contribution will be made at the END of each quarter. You expect that the 529 plan will return 8.5% per year with quarterly compounding....
Katie is a 7-month-old baby girl, the second child born to her parents. Her mother had...
Katie is a 7-month-old baby girl, the second child born to her parents. Her mother had a healthy, full-term pregnancy, and Nona’s birth weight was normal. She did not respond well to breastfeeding and was changed entirely to a formula based on cow’s milk at 4 weeks. Between 7 and 12 weeks of age, she was admitted to the hospital twice with a history of screaming after feeding, but was discharged after observation without a specific diagnosis. Elimination of cow’s...
You have just inherited $590,000. You plan to save this money and continue to live off...
You have just inherited $590,000. You plan to save this money and continue to live off the money that you are earning in your current job. If you can invest the money in a bond that pays 4.03 percent interest annually, how long will it be before your inheritance is worth $1 million? (If you solve this problem with algebra round intermediate calculations to 5 decimal places, in all cases round your final answer to 2 decimal places, e.g. 8.72.)...
ADVERTISEMENT
ADVERTISEMENT
ADVERTISEMENT