Step $1$: Set Goals

a$)$ How many years from now till retirement?

b$)$ How long will retirement last?

c$)$ Estimate how much per year will be needed during retirement on a before$-$tax basis. Assume

inflation will be $4\%$ per year. Also, assume you or your client will own a home.$)$

i$.$ In today$$s dollars

ii$.$ In retirement year dollars

Step $2$: Estimate income from other sources

a$)$ Estimate how much income you$/$client will be getting from Social Security.

i$.$ In today$$s dollars

ii$.$ In retirement year dollars

b$)$ Estimate how much income you will be getting from your$/$client$$s company$$s $$defined benefit$$

plan, if any. $($This is not about $401($k$),$ nor IRA savings.$)$

i$.$ In today$$s dollars

ii$.$ In retirement year dollars

Step $3$: Calculate the Annual Shortfall

a$)$ Calculate the income shortfall $($step $1$c $$ step $2)$

i$.$ In today$$s dollars

ii$.$ In retirement year dollars

Step $4$: Calculate the Total Wealth Needed at Retirement Time to Finance the Income Shortfall

a$)$ Estimate the average annual return on investments given the selected asset allocation after

retirement. Use asset class rates given in class.

b$)$ Adjust the nominal average rate of return for inflation of $4$ percent per year.

c$)$ Calculate the total target wealth needed at retirement $($using retirement time dollars$).$

Step $5$: Calculate Wealth Shortfall at Retirement

a$)$ Estimate an average rate of return consistent with the chosen asset allocation during the pre$-$

retirement phase.

b$)$ Estimate the future value of the retirement savings $(401($k$),$ IRAs, etc.$)$ that currently exist. No

need to adjust for inflation.

c$)$ Subtract $5$b from $4$c$,$ to get the wealth shortfall at retirement.

Step $6$: Determine How Much Must be Saved Annually Between Now and Retirement to Cover the

Wealth Shortfall

a$)$ Case A: assume an equal dollar amount of savings per year.

b$)$ Case B: Assume an equal percentage of salary per year. For case B$,$ make a conservative forecast

of salary and determine what fixed percentage savings rate will allow goal achievement.

Step $7$: Put the Plan in View

a$)$ Build a spreadsheet with all the numbers year$-$by$-$year in nominal terms for the accumulation

phase $($pre$-$retirement$).$ Do one spreadsheet for case A and one for case B of step $6.$ Make sure to

include annual contributions and balances.

b$)$ Build a spreadsheet with all the number for the distribution stage $($post$-$retirement$)$ in nominal

dollars. Make sure to include withdrawals, social security payments, balances, etc. $----$ CAN YOU PLEASE SOLVE THIS PROJECT AND GIVE US THE CALCULATIONS IN SPREADSHEET AS REQUESTED IN THE QUESTIONS.