Consider designing the spreadsheet to analyze each respective year$$s cash

requirements versus cash inflows, and then analyze the shortfall in each year on

a $$present value$$ basis.

o To ease computations, assume the cash flow shortfall occurs at the end of

each calendar year of Bill$$s retirement.

Case Study and Questions

Your client, Bill Smith engages you to assist him in planning for his retirement.

Specifically, he wants the following question answered:

In addition to the amounts described below, how much money will I need to invest at

the end of each of the next three years $($i$.$e$.,$ December $2025,2026$ and $2027),$

invested at $5\mathrm{.}0\%($compounded annually$),$ so I may retire at my desired standard of

living at the end of three years from now $($December $31,2027).$ His first day of

retirement is January $1,2028.$

Assume today is January $1,2025$ and ignore the impact of income taxes throughout.

In order to answer Bill$$s question, you gather the following information from him:

o Bill desires an income in today$$s dollars of $$85,000$ during each year of his

retirement, which is assumed to last $16$ years.

UCI Paul Merage School of Business

Financial Reporting

$2$

o One of Bill greatest concerns is inflation. He recently read inflation is expected to

average $2\mathrm{.}5\%$ annually over the next twenty years. Thinking about the impact of

inflation, remember that to maintain $$1$ in purchasing power, $$1\mathrm{.}025$ would be

required next year if inflation was $2\mathrm{.}5\%.$

o Bill will be eligible for Social Security. Assuming Social Security benefits

increase $2\%$ per year as they have in the past, Bill would receive $$24,500$ in the

first year of retirement, increasing thereafter at the historical rate $(2\%).$

o Bill has been told by his employer that as of December $31,2027$ he will be

eligible for a fixed pension of $$32,000$ during each year of his retirement. This

pension is fixed and does not increase in subsequent years.

o Bill currently has $$20,000$ in a certificate of deposit account earning $3\%$

compounded annually which matures on his retirement date. He states that

these funds will be made available to support his retirement savings pool as of

the date of his retirement $($at which time these funds will be combined with the

account set up for his three annual payments and will begin earning the higher

rate of return$).$

In addition to the answer to his question, Bill asks that you provide sufficient detail so he

may review and understand your analysis.

For Further Thought

Not required but for your further thought $($when you have more time....$)$:

Assume Bill is much younger and wants to retire in $25$ years, how much does he need

to set aside in each of the next $25$ years to meet his goal? Assume his retirement lasts

$16$ years and other facts above still apply as of the date of his retirement.

Bill$$s employer provides a pension of $$32,000$ per year, what amount of money does the

Company need to have set aside at his retirement date to provide this benefit assuming

the funds earn $4\%$ per year? What is the amount required to be set aside if the funds