Question

Please answer the questions in an excel spreadsheet with formulas showing

Part III: College Education

You and your spouse just had a baby. Ecstatic with the outstanding education you received at the University of Pittsburgh, you want to send your baby to college in 18 years and be able to

1

pay for your baby’s college education. You need to estimate cost of each year of college (you’re only paying for the 4 years necessary to complete a bachelor’s degree) for when your baby starts college 18 years from today. Also, you want to estimate how much you need to save in order to be able to pay these future college costs. Here are some assumptions to help you with your analysis:

• You want your baby to enjoy the same quality education at Pitt that you received. The current annual cost estimate to cover all student expenses today is $33,000.

• You anticipate that college costs will rise 3.8% at an annual basis until baby starts college in 18 years. After the start of college, you expect college costs to go up only 1.5% from year 1 to 2, 2% from year 2 to 3, and 2.5% from year 3 to 4 since the tuition part of baby’s college costs is fixed for the course of your baby’s undergraduate program.

• Your baby will need annual college money at the beginning of each year. This means 4 withdrawals from baby’s college fund at the beginning of her freshman year (18 years from today), sophomore year, junior year, and senior year.

• Any money saved in baby’s college fund will earn an after-tax return of 5.3% annually. The balance of baby’s college fund should be zero after the withdrawal at the beginning of baby’s senior year of college (21 years from today).

  Answer the following questions to help finalize your savings goals for baby’s college fund.

1. I What will be the expected cost of each year of baby’s college?

2. II One way you can fund your baby’s future college costs (from the previous question) is by making a single deposit today in the college fund that pays a 5.3% annual rate. How large of a deposit do you need to make today?

3. III After determining the single deposit that you would need to make from the last question, you get sticker shock and decide that is too much to handle all at once today. To help you get started, the grandparents decide to deposit $20,000 today in baby’s college fund. You decide to cover the rest of baby’s future college costs by making 18 annual equal deposits at 5.3% into baby’s college fund starting a year from today (the last deposit will be made when your baby starts college). How large does this annual deposit need to be in addition to the grandparents’ $20,000 initial gift to baby’s college fund?

Answer 1

1. Expected cost of education each year

College expenses at current rate= $33,000

Rate growth before commencement of college in 18 years=3.8%

Therefore, expenses for 1^st year= $33,000*(1+3.8%)^18 = $33,000* 1.956827= $ 64575.28

Growth rate for year 2 expenses= 1.5%

Therefore, expenses for 2^nd year =$ 64,575.28 * 1.015 = $ 65,543.91

Growth rate for year 3 expenses= 2%

Therefore, expenses for 3^rd year =$ 65,543.91 * 1.02 = $ 66,854.79

Growth rate for year 4 expenses= 2.5%

Therefore, expenses for 4^th year =$ 66,854.79 * 1.025 = $ 68,526.16

2. Lump sum deposit towards expenses:

Interest rate= 5.3% annually

Value of the expenses as at the commencement of education=

$ 64575.28 + $ 65,543.91/(1+5.3%) +$ 66,854.79/(1+5.3%)^2 + $ 68,526.16/(1+5.3%)^3

= $ 64,575.28 + $ 62,244.93 + $ 60,294.23 + $ 58,690.97 = $ 245,805.41

Lump sum deposit required today is the present value of the above amount= $ 97,024.35

Details as follows:

A B C D E 1 Present Value 2 3 Present value of an amount is calculated using the formula V=F/(1+r)^n 4 Where F= Amount after the specified period (Future Value), 5 n= Number of compounbding periods. 6 r= Rate of interest per period in decimals 7 All amounts in $ Given that 9 Future value F= 245805.41 No. of years (N)= 18 10 Discount Rate (% p.a)=R= 5.3 No. of times compounded a year (t) 11 12 Calculations: 13 Formula Cell reference Value 14 Discount rate per period in decimals (r = R/(100*t) D10/(100*610) 0.053 15 No. of periods (n)= N*t G9*G10 18 16 (1+r)= 1+G14 1.053 17 (1+r)^n= G164G15 2.53344034 18 Present value = F/(1+r)^n D9/G17 97024.3531 19 Rounded to $97,024.35 20

III. Alternate method:

Grand parents’ contribution= $20,000

Future value of this amount in 18 years= $20,000 * (1+5.3%)^18 = $20,000*2.533440339=$50,668.81

Net amount required= $ 245,805.41-$ 50,668.81 = $ 195,136.60

Yearly deposit required to accumulate this amount (in additions to grandparents' gift of $20,000)

= $6,744.47

Details as follows:

в с D 3 Amount of periodical payments is calculated using the formula PMT= (PV*r)/(1-(1+r)^-n] 4 Where PMT= Periodical payments required, 5 PV= Present value of future sum which is FV/(1+r)^n 6 n= Number of payments. 7 r= Rate of interest per period in decimals 8 All amounts in $ 9 Given that 10 Future value required FV= 195136.6 No. of years (N) 11 Rate of interest (% p.a)=R= 5.3 No. of payments a year (t) 11 12 13 Calculations: 14 Formula Cell reference Value 15 Interest rate per period in decimals (r )= R/(100*t) D11/(100*G11) 0.053 16 No. of periods (n)= N*t O'G11*G11 18 17 (1+r)= 1+G15 1.053 18 (1+r)^n= G174G16 2.533440339 19 Present Value of future amount (PV)= FV/(1+r)^n= D10/G18 77024.35183 20 (1+r)^-n= G17A-G16 0.39472017 21 [1-(1+r)^-n]= 1-G20 0.60527983 22 FV*r= G19*G15 4082.290647 23 Periodical payment required (PMT)= (FV*r)/(1-(1+r)^-n] G22/621 6744.468329 Rounded to $6,744.47 24