In: Accounting
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 $20,176 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%.
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)
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 |
-------------------------------
-------------------------------