Statement of Facts:

Jennifer Wilson (age 47) has hired you to help her plan for her future retirement

She plans to retire in 20 years when she will be 67 and live to age 100 (33 years in retirement)

Her yearly income is $35,000

Her current yearly spending is $30,000 and she plans to maintain this level in retirement

Inflation is projected to be 3% per year

She currently has saved $100,000 for her retirement

Her investments are projected to grow at 8% per year

Inflation-adjusted rate of return = [(1 + investment return rate 1 + inflation rate) 1] x 100

Her inflation-adjusted rate of return is projected to be 4.85% per year: [(1.08 1.03) 1] x 100

Using Excel Functions to Make Time Value of Money Calculations

Step One Calculating the Future Value of the Yearly Spending Needed: Determine the future value of Jennifers annual spending needed for her first year of retirement and

then solve for the future value of this amount

Layout the worksheet in a format similar to how the lecture slides illustrate to calculate the

Future Value of a Single Amount

Present Value = current yearly spending, but the amount should be negative as it reflects cash spending / outflows

Rate of Interest = 3% (inflation rate)

Number of Years = years to retirement

Type = payment due at the end of the period (0)

Step Two Calculating the Present Value of the Series of Future Spending Payments:

Solve for the present value of the amount Jennifer will need for retirement

Layout the worksheet in a format similar to how the lecture slides illustrate to calculate the

Present Value of an Annuity Due

Annual Annuity Payment = calculated in Step One (link cell to the value from Step One), but the amount should be negative as it reflects cash spending / outflows

Rate of Interest = 4.85% (inflation-adjusted rate of return)

Number of Years = years spent in retirement

Type = payment due at the beginning of the period (1)

Step Three Calculate the Amount Needed to be Saved Annually Solve for the annual payment needed for Jennifer to achieve the future value for her needed

retirement fund

Layout the worksheet in a format similar to how the lecture slides illustrate to calculate the Annual Deposits Amount to Accumulate a Future Sum and add a row for Present Value

Present Value = amount already accumulated, but the amount should be negative as it reflects cash spending / outflows

Future Value = amount needed to support retirement spending (link cell to the value from Step Two)

Rate of Interest = 8% (investment growth rate)

Number of Years = years to retirement

Type = payment due at the end of the period (0)

Step Four Questions to Answer:

How much will Jennifer need to save each year until retirement to meet her future spending

needs?

Is Jennifer on track to meet her yearly savings needs?