Your 25-year-old client wants to retire when he is 60 years old, and have a retirement income equivalent to $5,000 per month in todays dollars. We cannot be sure of how long we live after retirement, but the client wants to be extra careful and save for 30 years of after retirement life. Market expectation for average annual inflation for the future is 1.7%. Because of inflation, he will need substantially higher retirement monthly income to maintain the same purchasing power. You can ignore the inflation after the retirement. He plans to purchase a lifetime annuity from an insurance company one month before he retires, where the retirement annuity will begin in exactly 35 years (420 months). The insurance company will add a 3.00 percent premium to the pure premium cost of the purchase price of the annuity. The pure premium is actuarial cost of his anticipated lifetime annuity. He has just learned the concept of time value of money and never saved anything earlier. He will make the first payment in a month from now and the last payment one month before he retires (a total of 419 monthly payments). 1) Given a rate of return of 5% for the foreseeable future, how much does he need to save each month until the month before he retires? (30 points) 2) What if he just received a gift of $30,000 from his grandfather and investment that in the same investment account? How much does he need to save each month? (5 points) 3) Continuing with 1) above, suppose his parents has set up trust that will contribute 500 every month in an investment account while he is working (same number of investment as him). However, that trust will grow with the interest rate of 2% only because is it a relatively safer investment. How much does he need to save each month? (10 points) 4) Are there any non-quantifiable factors that you should be aware of (please write complete sentences)? (list 5) (5 points) Instructions: Please submit this assignment in excel file (even answer for question number 4 can be written in excel file). Only one team member needs to submit the file. There should be input values which are provided in the mini case. The work and the answer should be all formula linked to the input area (i.e. do not paste special value, there will be no points for value answer without any formula or financial functions). Hints: Inflation is annual and happens once a year, so use annual frequency. Rest of the calculations is with monthly compounding. EAR is not needed here.

I gave this question previously two times and it was not correct and even not the calculations was properly done and calculated. This is my third time Kindly read the question carefully, and I need excel file too.