Question

A professor has two daughters that he hopes will one day go to college. Currently, in-state students at the local University pay about $22,478.00 per year (all expenses included). Tuition will increase by 3.00% per year going forward. The professor's oldest daughter, Sam, will start college in 16 years, while his youngest daughter, Ellie, will begin in 18 years. The professor is saving for their college by putting money in a mutual fund that pays about 7.00% per year. Tuition payments are at the beginning of the year and college will take 4 years for each girl. (Sam's first tuition payment will be in exactly 16 years)

The professor has no illusion that the state lottery funded scholarship will still be around for his girls, so how much does he need to deposit each year in this mutual fund to successfully put each daughter through college. (ASSUME that the money stays invested during college and the professor will make his last deposit in the account when Sam, the OLDEST daughter, starts college.)

Answer 1

College expense in future year = current college expense * (1 + inflation rate)^{number of years}

The college fee to be paid in 16 years from now, through 22 years from now is calculated as below :

16 years from now - $22,478.00 * (1 + 3%)¹⁶ = $36,070.59

17 years from now - $22,478.00 * (1 + 3%)¹⁷ = $37,152.71

and so on.

Next, we find the present value of these college expenses 16 years from now. This will give us the value of the college fund that is required 16 years from now to fund the college expenses

present value = future value / (1 + rate of return)^{number of years}

This is calculated to be $262,776.14

Now, we calculate the yearly deposit so that the fund has $262,776.14 after 16 years. This is calculated using PMT function in Excel :

rate = 7% (rate of return on mutual funds)

nper = 16 (number of yearly deposits made)

pv = 0 (beginning amount in college fund is zero)

fv = 262,776.14 (required value of fund at end of 16 years from now)

PMT is calculated to be $9,422.53. This is the yearly deposit required to fund their college expense