Question

Your sister will start college in five years. She just informed your parents that she wants to go to Georgia Southern University. It will cost $20,000 per year for four years (cost assumed to come at the beginning of each year). Anticipating her ambitions, your parents have saved already $15,000 and will continue to invest at the end of each month for the next five years. How much more will your parents have to invest each month for the next 5 years to have the necessary funds for your sister’s college education? Assume the interest rate is 10% annual compounding.

Answer 1

The amount required at end of 5 years from now to fund college is calculated using PV function in Excel :

rate = 10% (rate of return earned)

nper = 4 (number of years in college)

pmt = -20000 (Yearly cost of college. This is entered with a negative sign because it is a withdrawal)

fv = 0 (amount payable at end of college education is zero)

type = 1 (each year's college fee is payable at the beginning of the year)

PV is calculated to be $69,737.04

Monthly investment for the next 5 years to accumulate the required amount 5 years from now is calculated using PMT function in Excel :

rate = 10%/12 (converting annual rate of return to monthly rate)

nper = 5 * 12 (total number of monthly investments = number of years until college * 12)

pv = -15000 (Amount currently saved. This is entered with a negative sign because it is like a payment into the college fund)

fv = 69737.04 (amount required at end of 5 years from now)

PMT is calculated to be $581.86

Your parents have to invest $581.86 each month