- Answer In Excel Average Tuition Today 22 000 00 Annual Increase In Tuition 3 00 Rate Of Return Before 1 (418.65 KiB) Viewed 27 times
22,000.00
Annual increase in tuition.
3.00%
Rate of Return before college starts.
10.50%
Rate of Return after college starts
2.00%
a) How much money will you need to have
accumulated one year before each child’s first
tuition payment to be able to meet the total tuition
requirements? (Use Excel’s NPV function)
b) If you were to make a single lump sum investment today,
how much would you need to invest today to meet each child’s
total tuition requirements?
c) If you were to invest an equal dollar amount at the end
of each year, how much would you need to invest annually to
meet each child’s total tuition requirements?
d) If you were to invest an equal dollar amount annually
and your first investment is today, how much would you need to
invest annually to meet each child’s total tuition
requirements?
e) Assume you can save $7,000 for child 1 and $5,000 for
child 2 at the end of each year to invest towards their tuition
requirements. What rate of return do you need to earn in
pre-college years to meet each child’s total tuition
requirements?
Tuition Planning Worksheet Average tuition cost today Annual increase in tuition Rate of return before college starts Rate of return after college starts $Amount needed for child 1 in 9 years $Amount needed for child 2 in 13 years ) Lump sum investment required today for child 1 Lump sum investment required today for child 2
Annual Investment required (end year) child 1 Annual Investment required (end year) child 2 1) Annual Investment required (beginning year) child 1 Annual Investment required (beginning year) child 2 Annual saving at end of year child 1 Annual saving at end of year child 2 Pre-retirement rate required child 1 Pre-retirement rate required child 1 EC scenario analysis for child 1 output in separate tab -) 22,000.00 3.00% 10.50% 2.00% 7,000.00 5,000.00 Child 1 Year in college 10 11 12 13 Years from now 1 2 3 4 Tuition needed 29,566.16 30,453.15 31,366.74 32,307.74 Child 2 Years from now Year in college 14 1 2 3 4 H561 17 Tuition needed 33,276.97 34,275.28 35,303.54 36,362.65