You are a Certified Financial Planner. You have been hired by Mr$.$ and Mrs$.$ Smith to help them review their financial situation

and plan for retirement. The Smiths have provided you the following information: $($There are $3$ areas where they need help, see A$,$ B and C$)$

A$)$ Retirement Analysis

ANSWER QUESTIONS $1-4$ to complete the Assignment

a Both Mr$.$ and Mrs$.$ Smith are $25$ years old

b They want to retire when they are $65$ years old $(40$ years$)$

c They do not expect to receive any of their retirement from Social Security

d They expect inflation to average $3\%$ over their remaining life expectancy

e They have a moderate tolerance for risk

$f$ The Smiths are both CPAs and have their own CPA firm with no employees now and do not expect any in the future

g Their main investment is a SEP IRA which limits their total investment per year to $$51,000$

$h$ The financial institution they have chosen offers the following investment options for their SEP IRA

I They expect to live $40$ years after they retire $(105$ years old$)$

j They have calculated that they need a minimum of $15,000$ a month to live on through retirement $($but would like as much as possible without exceeding their risk tolerance$)$

$k$ The average interest rate after retirement is $5\%$

I They want to leave their Kids $$3,000,000$ when they die

j They want to structure their investments to return a minimum of $5\%$ annually

$1$ What is the minimum amount that the Smiths must save by retirement to retire at $65?$

Arguments

Amount

Required savings at retirement

$2$ How much will they need to invest each month using the minimum acceptable rate of return $(5\%)$ to achieve that goal in question $1$

Arguments

Amount

Monthly required investment

$3$ Does this amount exceed the SEP IRA Max?

Total amount per year

SEP IRA Max

Does it exceed the Max?

Enter your arguments here and reference these cells in your formula

$($use the help function to determine the required arguments$)$

Enter your arguments here and reference these cells in the PV formula

$($use the help function to determine the required arguments$)$

Monthly required investment $x12$

$51,000$

$4$ IF the amount is below the Max how much would they have at retirement, if they invest the max?

Arguments

Amount

Enter your arguments here and reference these cells in your formula

$($use the help function to determine the required arguments$)$

$($The $$51,000$ should be divided by $12$ because the smiths are investing monthly$)$

Amount expected at retirement