Analyze this saving plan and enter your answer in Questions 4-10. If you use Excel functions to calculate the answers, submit the completed Excel file as a group and make sure to include the names of your group members who work on the problem.

A 30-year old wants to make a saving plan to meet the following three goals:

First Goal: To save $150,000 in 5 years for the down payment to purchase a house, which is estimated to be $600,000. At that time, she will borrow the rest by taking a 30-year fixed rate mortgage at an APR of 5.5% for the house purchase.

Second Goal: To retire in 35 years, that is, when she is 65. By that time the money she saves during the 35 years, is able to generate $18,000 each month for 20 years of retirement living. Assume she will withdraw the $18,000 at the beginning of each month.

Third Goal: To leave $1,000,000 to her children, which is expected to occur at the end of her 20-year retirement life.

She thinks she can save $3000 at the end of each month in the following 5 years. Then she will need to withdraw $150,000 from her account for the down payment to buy a house. After then, she will continue saving $X dollars each month while paying the monthly mortgage payment until she retires at the age 65. She will pay off the mortgage loan when she retires.

Assume she can earn 10% EAR before she retires and 6.5% EAR on her money after she retires.

Q4. What is the monthly interest rate she can earn before she retires ?

Q5. What is the monthly interest rate she can earn after she retires ?

Q6. What is the total saving required on the day she retires to support her Goal I and Goal II ?

Q7. After withdrawing the $150,000 for down payment five years later, how much is left in her account?

Q8. To achieve the three goals, how much must she save at the end of each MONTH after she purchases the house until she retires?

Q9. What is her monthly mortgage payment?

Q10. If the inflation rate is 3% each year, what is the purchasing power of $18,000 after 35 years (the time when she retires)?