Savings and Mortgage Repayment Calculations

Question One: Saving for retirement (3 marks)

Question One:  Saving for retirement  (3 marks)

Jim works full time and has a good job that generates a stable income.  Jim currently has $200,000 cash in his savings account for retirement. To maximise his savings for retirement,  Jim plans to deposit $1,000 every month into this saving account from now onwards.  The savings account earns an after-tax interest rate of 1.25% p.a., compounding monthly. Jim is highly committed to this monthly contribution to the savings account and will not withdraw any fund from this account.  Jim plans to retire in 20 years.  

a.How much will Jim have in this savings account when he retires 20 years later?  Show all work clearly.  (1 mark) 

b. Suppose that at the time Jim retires he has a total cash from all his assets (including the above savings) in the amount of $1,000,000.  It is estimated that this fund will be earning an interest rate of 3% p.a., with interests to be compounded monthly.  Jim believes that he can live healthily for 35 years after he retires and he intends to spend this savings by withdrawing an equal amount of money every month from this fund for 35 years.  What is the equal amount of money that he withdraw from this savings account every month for 35 years?  (Assuming there will be no more cash to be deposited into this account over the 35 years.)  (1 mark)

c. It is estimated that Jim will need $3,000 every month to meet all his monthly expenditure in his retirement days, will the monthly withdrawal provided from his $1,000,000 fund in part (b) be sufficient to meet his monthly expenditure?  Briefly explain your answer. (1 mark)

Question Two: Mortgage Repayment  (5 marks)

Excel is required for this question.

Tara has just bought a house for $930,000 with a 30% down payment and a 70% mortgage loan for 20 years at the interest rate of 2.19%, compounded monthly.  She is scheduled to repay interest and principal every month over the loan period unless she sells the house at some point and fully pay off the loan.  

a. Using Excel, prepare a monthly loan amortization table for the entire loan period. The table should display the values for the columns as displayed in the table below.

i. Display here the formulae for calculating the Periodic (Annuity)  Loan Payment, Interest Expense, Principal Repayment and Outstanding Loan Balance.  (1 mark) 
ii. Display here the following template table with relevant answers.  (You can copy and paste your Excel table here)

iii. Describe and explain the trend of amount of periodic (annuity) loan payment, interest expense and principal repayment and outstanding loan balance over time. Are the interest expense and principal repayment increasing or decreasing over time?  State and interpret the final outstanding loan balance at the end of the entire loan period.   

b.Tara is considering different options for this property investment.  Tara may sell this house at the end of 3 years if the market price is attractive.  If Tara sells the house 3  years later, how much will she have to fully repay the mortgage at that time?  Show all work clearly.  (1 mark) 

c. Instead of taking the option from part (b), if Tara makes a lump sum payment (in addition to the periodic regular repayment) of $200,000 one year after she purchased this house (assuming no penalty for early payment).  Suppose that the interest rate remains the same one year later, and that she continues to repay her loan (interest and principal) periodically at the same annuity repayment amount calculated in part (a), how long will it take to fully repay the remaining balance? Show all work clearly. Briefly compare the your answer with the initial mortgage loan period in part (a).  (1 mark)