Question

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 ?

Answer 1

Here is the solution to Questions 4 and 5.

Questions 6, 7, 8, 9 and 10 are not given.

We know that monthy rate is nominal Annual rate 12 But it is given EAR (Effective annual Rate) So we have to find Nominal Annual Rete for both before and after she retares & let the Nominal Annual Rate before retires is No. we know that period per Year Annual Effective Rate - t li " Nominal annual rate Period peryear , Pulling values in formula 0.160 = (1 + More - • No = So 12((1.152-1) = 12(1.008-1) = 0.096 Nb = 0.096 So monthy nomet nominal rate = oog6 = 0.008 12 So Monthy Nominal rate is 0.8% before retires.

After retires EAR is 6.5% Putting in formulae let Nominal Annual rate be NA after retirement 30.065 = (1+ Noa) - 12 - NA = 12((1.065) "2-1) = 12(0.0053) = 0.0636 I so Mauthly Nominal rate osasto = 0.0636 = 0.0053 O 0636 = 0.0 12 So after she retires monthy rate will rate will be 0.53%