Your $25-$year$-$old client wants to retire when he is $60$ years old, and have a retirement income equivalent to $$4,500$ per month in today$$s 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 foreseeable future is $2\%.$ Because of inflation, he will need substantially higher retirement monthly income to maintain the same purchasing power. 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 $5\%$ 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?

$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?

$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?

$4)$ Are there any non$-$quantifiable factors that you should be aware of $($list $5)$

Instructions:

I need Excel solutions please!!

Hints:

Inflation is annual and happens once a year, so use annual frequency for inflation related adjustments.

Rest of the calculations are with monthly compounding.

EAR is not needed here.