Question

You had your first child recently. You would like to set aside some funds so that your child will be able to attend the University of Texas as an undergraduate without taking on any student loans. Total costs of attendance for undergraduate students currently amount to $28,000 per year and are expected to continue to grow at a 2.5% growth rate per year. Assume that the four-year college expenses for the first year of college need to be paid exactly 18 years from today and that the subsequent costs need to be paid at an annual frequency 19, 20, and 21 years from today. You would like to make 18 equal annual payments starting today to your child’s college savings account to be able to cover the expected college costs. The savings are invested in risk-free Treasury securities that offer a return of 2%. How large are the equal annual contributions to the college savings account over the next 18 years?

Answer 1

Fee to be paid X years from today = fee in today's dollars * (1 + inflation rate)^X

Amount to accumulate in 18 years to fund college = sum of present values of each year's fee 18 years from today

Amount to accumulate in 18 years to fund college = $175,970

The equal annual contributions required to accumulate $175,970 18 years from today is calculated using PMT function in Excel :

rate = 2% (return earned on savings)

nper = 18 (number of equal annual contributions)

pv = 0 (beginning amount in savings is zero)

fv = 175970 (Amount to accumulate in 18 years)

type = 1 (first contribution is made today - annuity due)

PMT is calculated to be $8,057.05

1 A B C Years PV - 18 from Fee to be years from now paid now 18 $ 43,670 $ 43,670 19 $ 44,762 $ 43,885 20 $ 45,881 $ 44,100 21 $ 47,028 $ 44,316 TOTAL $ 175,970 PMT ($8,057.05)

А в Years from 1 now 2 18 3 19 Fee to be paid =28000*(1+2.5%)^A2 =28000*(1+2.5%)^A3 =28000*(1+2.5%)^A4 =28000*(1+2.5%)^A5 TOTAL PMT PV - 18 years from now =B2/(1+2%)^(A2-18) =B3/(1+2%)^(A3-18) =B4/(1+2%)^(A4-18) =B5/(1+2%)^(A5-18) =SUM(C2:05) =PMT(2%,18,0,C6,1)