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 babys college education. You need to estimate cost of each year of college (youre only paying for the 4 years necessary to complete a bachelors 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 babys college costs is fixed for the course of your babys undergraduate program.

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

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

  Answer the following questions to help finalize your savings goals for babys college fund.

1. I What will be the expected cost of each year of babys college?

2. II One way you can fund your babys 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 babys college fund. You decide to cover the rest of babys future college costs by making 18 annual equal deposits at 5.3% into babys 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 babys college fund?