Question

Babu Baradwaj is saving for his son's college tuition. His son is currently 11 years old and will begin college in seven years. Babu has an index fund investment worth $7,500 that is earning 9.5 percent annually. Total expenses at the University of Maryland, where his son says he plans to go, currently total $15,000 per year but are expected to grow at roughly 6 percent each year. Babu plans to invest in a mutual fund that will earn 11 percent annually to make up the difference between the college expenses and his current savings. In total, Babu will make seven equal investments with the first starting today and the last being made a year before his son begins college.

    What will be the present value of the four years of college expenses at the time that Babu's son starts college? Assume a discount rate of 5.5 percent.
    What will the value of the index mutual fund be when his son just starts college?
    What is the amount that Babu will have to have saved when his son turns 18 if Babu plans to cover all of his son's college expenses?
    How much will Babu have to invest every year in order to have enough funds to cover all his son's expenses?

Answer 1

Please refer to below spreadsheet for calculation and answer. Cell reference also provided.

B Current College Fee Annual growth in college fee Years to attend college $15,000.00 6% College Year (beginning) Fee $22,554.45 $23,907.72 $25,342.18 $26,862.72 4 Discount rate 5.50% Present value of College at start of college $90,861.20 $7,500.00 Current value of Index fund Index fund rate of return Years to attend college 9.50% Value of Index fund at start of college $14,156.64 23 3) Required Amount to save when his turns 18 $76,704.57 24 25 4) 11% Rate of return of Mutual fund No. of equal installments Investments are made at beginning of each year Amount of equal installments $7,063.40

Cell reference -

B Current College Fee Annual growth in college fee Years to attend college 15000 0.06 College Year (beginning) Fee =C2*(1+C3)^7 =C7*(1+$C$3) =C8*(1+$C$3) =C9*(1+$C$3) Discount rate 0.055 Present value of College at start of college =NPV(C12,08:C10)+C7 Current value of Index fund Index fund rate of return Years to attend college 7500 0.095 Value of Index fund at start of college =C17*(1+C18)^7 23 3) Required Amount to save when his turns 18 =C14-C21 24 25 4) 0.11 Rate of return of Mutual fund No. of equal installments Investments are made at beginning of each year Amount of equal installments =PMT(C25,C26,,-C23,1)

Assumption: College fees are to be at beginning of the year.

Hope this will help, please do comment if you need any further explanation. Your feedback would be highly appreciated.