Answer in table (excel) form and show all working (as if in excel)

The Scenario

You are a financial planner who is investigating the options for a 45-year old man who wishes to have enough money to retire by the time he is 65. The man has just turned 45 and has received a sizable inheritance of $250,000. 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:

His salary has just been increased to $120,000 p.a., which is taxed at a rate of 30% before being paid to him. This is currently being paid on a monthly basis at the start of each month.

Any salary over $200,000 p.a. will be taxed at 40% (i.e. the first $200,000 p.a. of salary is taxed at 30%, the amount over $200,000 p.a. is taxed at 40% - tax is paid at a constant rate each month during each year assuming that the monthly salary is to be earned over the whole year).

He is paying off a mortgage on the property he lives in. The payments are currently $3,700 at the end of each month, which is enough to have the property paid off exactly in 7 years at the current interest rate of 3.1% p.a. effective.

Currently each month, $2,900 of his net salary is being used in expenses and any additional amount is being invested in a bank account earning 2.1% p.a. compounded monthly. These payments are being made on the same day the net salary is received. The current bank account balance (before receiving the inheritance) can be assumed to be zero.

He expects his expenses to increase by 0.2% each month.

He expects his salary to increase at a rate of 5% 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 recently increased rate. Interest income in the bank account is taxed at a concessional rate of 15% immediately on the interest income earned each month.

He intends to live in his home when he is retired and live off the proceeds of his bank account in retirement. Initially, the man wants to deposit the inheritance in his bank account.

a) Calculate the expected amount the man will have in his bank account at age 65 under the basis above. Assume all interest rates remain constant. (12 Marks)

B) The first thing you notice about this mans financial affairs is that it would most likely be beneficial for him to pay off the mortgage as quickly as possible. This would involve using the inheritance immediately and making a lump sum contribution to the home loan, and then continuing the $3,700 per month repayments until the loan is paid off. Any additional amount of salary not used is credited to the bank account. b) Calculate how much more the man will have at age 65 if he takes this approach. (6 Marks)

The client mentions to you he is extremely risk averse and does not want to invest in anything where the cash-flows arent known in advance. Based on this you decide to only recommend fixed interest securities, with the intention of investigating the impact of investing the inheritance in either of these securities. Two options which you are considering are as follows:

Bond A A zero coupon bond redeemable in 20 years with a gross yield of 5.25% p.a. effective.

Bond B Pays half yearly coupons of 6.0% p.a.

Redemption is at the decision of the issuer at the following redemption values: 1.1 times the face value on any coupon date in the 11th year through to the 13th year from now 1.25 times the face value on any coupon date in the 14th year through to the 15th year from now This bond has a minimum gross yield of 6.5% p.a. effective.

c) Assuming the man invests in either Bond A or Bond B (but not both at the same time); calculate the nominal amount of each bond which can be purchased using the inheritance amount of $250,000. (7 marks)

Should the client decide to invest in one of the bonds above, you decide to recommend that the mortgage continue to be paid off with the $3,700 per month for 7 years. The client is also subject to a tax of 20% on coupons and capital gains. The tax is payable immediately it is incurred, with coupons to be invested in the bank account when they are received. The gross yields of the bonds are unchanged.

d) For both bond purchase options, calculate how much money the man will have in the bank account at age 65. Assume that Bond B is redeemed at the end of the 13th year. (14 marks)

e) Which of the four alternatives discussed above should the man choose such that he has the largest bank balance at the age of 65? Give a short explanation for your answer. (3 marks)

Your clients son is going to start a small business. He has arranged for finance through a bank to fund most of his requirements. But, he still needs a cash injection of $150,000 in 6 months time and another injection of $100,000 in 18 months time from now. Your client has asked you to work out a way in which he can invest in his sons business. You have been told that the business will not provide any return for the first 5 years. After that the business will provide a level return at the start of each quarter until your client retires. The inheritance is saved in the bank until it is required to be invested in the business. The return received is invested in the bank account.

f) What would be the amount of quarterly return required, if your clients position at retirement is equivalent to the best alternative chosen in part (e). (8 marks) \