Mini$-$Case

Bill Petty, $56,$ just retired after $31$ years of teaching. He is a husband and father of two children who are still dependent. He received a $$150,000$ lump$-$sum retirement bonus and will receive $$2,800$ per month from his retirement annuity. He has saved $$150,000$ in a $403($b$)$ retirement plan and another $$100,000$ in other accounts. His $403($b$)$ plan is invested in mutual funds, but most of his other investments are in bank accounts earning $2$ or $3$ percent annually. Bill has asked your advice in deciding where to invest his lump$-$sum bonus and other accounts now that he has retired. He also wants to know how much he can withdraw per month, considering he has two children and a nonworking spouse. Because he has children, his current monthly expenses total $$5,800.$ He is not eligible for Social Security until age $62,$ when he will draw approximately $$1,200$ per month; however, he would rather defer drawing on Social Security until age $67$ to increase his monthly benefit amount to $$1,550.$

Questions

Bill has an emergency fund already set aside, so he can use his $$400,000$ of savings for retirement. How much can he withdraw on a monthly basis to supplement his retirement annuity if his investments return $5$ percent annually and he expects to live $30$ more years?

Ignoring his Social Security benefit, is the amount determined in question $1$ sufficient to meet Bill$$s current monthly expenses $($keep in mind that he will receive a pension of $$2,800$ per month$)?$ If not, how long will his retirement savings last if his current expenses remain the same? What if his expenses are reduced to $$4,500$ per month?

Considering the information obtained in question $2,$ should Bill wait until age $67$ for his Social Security benefits? If he waits until age $67,$ how will his monthly Social Security benefit change the answers to question $2?($HINT: Calculate his portfolio value as of age $67,$ and then calculate how long that amount will last if it earns $5$ percent annually.$)$

If the inflation rate averages $3\mathrm{.}5$ percent during Bill$$s retirement, how old will he be when prices have doubled from current levels? How much will a soda cost when Bill dies if he lives the full $30$ years and the soda costs $$1$ today?

Please answer all questions and solve in Excel.