Question

James and Lily are convinced that their young son, Harry, will follow in their footsteps by attending Boston College. They would like to have saved enough money to cover in full four years’ worth of BC expenses – tuition, fees, room, and board – when Harry starts as a freshman in exactly seventeen years. In 2018, four years at BC is expected to cost $279,768. BC expenses are expected to increase by 2% each year. Lily and James opened a savings account that pays 1.25% annual interest, compounded monthly, and plan to make deposits of $1,700 at the end of each month. How much should they deposit today to have enough in seventeen years to pay for Harry’s time at BC? Use Excel functions to calculate the amount

Answer 1

Boston college fees in Year 18	Using future value function in MS excel	fv(rate,nper,pmt,pv,type) rate = 2% nper = 17 pmt = 0 pv = 279768 type =1 - it is assumed that fee is paid at the beginning of year	FV(2%,17,0,279768,1)	($391,742.74)
future value of monthly payment	Using future value function in MS excel	fv(rate,nper,pmt,pv,type) rate = 1.25/12% nper = 17*12 = 204 pmt =-1700 pv = 0 type =0	FV(0.10416%,204,-1700,0,0)	$386,176.37
amount to be needed apart from monthly savings	391.742.74-396176.37			5566.37
Amount deposited today	future value/(1+r)^n r = .1041% n = 17*12 = 204	5566.37/(1.001041)^204	4501.855273	4501.86