Question

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. The current in-state cost (tuition and other expenses, if living with parents) for UNLV is about $18,086 per year, and you expect your child to spend 4 years in college. You expect this cost to go up 4% per year until your child finishes college.

1. How much will you need to save per quarter so that your child will have enough funding for college? (Assume: Tuition payments are made ONCE per year at the BEGINNING of the year. Your 529 plan remains invested until your child finishes college.)

2. Construct a one-way data table to perform a what-if analysis by varying the annual investment return between 5% to 12%, with one-percentage-point increment. That is, how much you need to save each quarter to reach your goal if the investment return varies between 5% and 12%.

Answer 1

Here we need to calculate the savings required to fund the child's education.

First we found the amount each year required i.e. at the begining of 18th year, 19th year , 20 year , 21st year.

rate of increase = 4%

Year	Amt Required
18	18086.00
19	18809.44
20	19561.82
21	20344.29

Now we use the excel function of =PMT(8.5/4,72,,-D3,0)

=PMT (rate, nper, pv, [fv], [type])

rate = 8.5/4 as compounding quaterly

nper = 18*4 = 72, 76, 80, 84

pv is left

fv= - 18086, -18809.44, -19561.82, -20344.29

type= 0 as investment is made in begining

			Rate of Increase	1.04
years	18	Year	Amt Required
Factor(qtry comp)	4	18	18086.00	$108.42
Rate Of interest	8.50%	19	18809.44	$112.76
		20	19561.82	$117.27
		21	20344.29	$121.96
			Total Savings	$460.41

We sum all the savings desired to get total savings = $ 460.41

2)

We Just change the rate of interest from 8.5% to 5,6,7......to get the desired savings

ROI	Total savings
5%	663.95
6%	599.65
7%	540.38
8%	485.91
9%	436.02
10%	390.47
11%	349.00
12%	311.36