Reference no: EM133074080

You are a financial planner who is investigating the options for a 55 year old man who wishes to have enough money to retire by the time he is 70. This man is very lucky. He has just won the lottery and received a prize of $300,000 after tax. He has approached you to understand his financial situation and make an informed decision regarding his investment options for his financial future. His current financial arrangements are as follows:

• He decides to purchase a new home at a price of $500,000. According to the home loan that he would like to apply for, the initial principal he has to pay upfront is 20% of the property price. The remaining loan will be paid via 10 years' monthly repayments. The repayments are currently at the level of $X at the end of each month, which is enough to have the property paid off exactly in 10 years at the current interest rate of 4% p.a. effective.
• His salary is currently $180,000 p.a., which is taxed at a rate of 33% before being paid to him. This is currently being paid on a monthly basis at the same time as the home loan repayment is due (i.e. at the end of each month).
• Currently each month, $X of the net salary is going towards the home loan, $2,500 is being used in expenses and any additional amount is being invested in a bank account earning 3% p.a. compounded monthly. These payments are being made on the same day the net salary is received. Interest income in the bank account is taxed at a rate of 20% immediately on the interest income earned each month.
• His current bank account balance (before receiving the lottery winnings and paying the initial principle) is assumed to be zero.
• He expects his salary to increase at a rate of 4% p.a. going forward. This increase is processed on a yearly basis, with the next increase to occur after 12 further salary payments at the current rate.
• He expects his expenses to increase by 0.5% each month, including next month (i.e. the expense at the end of the 1st month will be (2500 × 1.005)).
• He intends to live in his new home when he is retired and live off the proceeds of his bank account in retirement.
• Initially, the man wants to deposit the remaining lottery winnings (after he pays the initial principle for the new home) in his bank account.

Qa) Calculate the monthly level payments the man has to repay for the home loan (e.g. find X). You are allowed to simply solve X by setting up the equation of value in excel, instead of using "Goal seek".

b) Calculate the expected amount the man will have in his bank account at age 70 under the basis above. Assume all interest rates remain constant.