This assignment is designed to familiarize you with the use of Excel functions to make time value of money calculations in order to answer questions related to a retirement needs analysis.

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

1. Layout the worksheet in a format similar to how the lecture slides illustrate to calculate the Future Value of a Single Amount

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

3. Rate of Interest = 3% (inflation rate)

4. Number of Years = years to retirement

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