Problem 1 Mr. & Mrs. Smith are planning for the education of their young child. They plan to save a fixed amount of money every month for the next 15 years. Once the kid goes to college, they expect to spend $3,000 per month for 3.5 years to cover all college-related expenses, such as tuition and living expense.

The grandparents plan to contribute a lump sum investment today (period 0) in the amount of $7,000 and an additional $250 per month over the next 10 years.

The expected rate of retum is 3.5% per year, with monthly compounding. Below is the timeline in years.

{contribution

stop} {spending stage}

0 1 10 15 16 18.5

|-----|---------------------------------|------------------|--------|--------------|---------------->

{ Saving stage }

Note that you are solving the problem from scratch. You can organize the spreadsheet as you see lit. However, all calculations should be done in Excel. Please show all your work for full credit.

1. Calculate how much money Mr. and Mrs. Smith will need to have 15 years from now, right before their kid goes to college. Please use formulas.

2. Calculate how much Mr. and Mrs. Smith need to save everv month from year 1 to year 15 so they can achieve their goal and send their kid to college for 3.5 years. Do not forget to take into account the contributions of the grandparents over the first 10 years.