Excel Spreadsheet

This needs to be a smart spreadsheet where you can change a variable on-demand and the rest of the results will automatically change (dropdown menus for changing variables is the cleanest way to go)

Use your creativity in designing this spreadsheet

Download this template Excel Workbook. Anywhere that you see an asterisk (*) you need to be making a calculation and anywhere that looks like a raised box in for input only. You will be using this template to complete your client's financial plan.

You will need to draw upon your prior knowledge (from FIN 301) of the time value of money (TVM) to figure out how to annuitize the payment stream from the stated pool of investments. See the video at the bottom of this page if you need a TVM refresher.

Assume Annuity Due for all TVM calculations

Unless there is a specific estate goal, then assume that the client is comfortable with the future value = 0

Use this formula sheet to help you in developing your spreadsheet

Written Presentation

Be creative and professional! This should look like something that you would present to a real client with your future boss present for the meeting.

I am looking for a summary of all items as stated by your client (this gives the client the chance to make alterations is data is wrong).

I am looking for a statement of their base case status.

If their base case shows Additional Target Funding Needed, then you need to make a recommendation of how to solve the calculated shortfall. You also need to recommend one alternative fix to give your client a choice.

Next, I am looking for a discussion (in a well-organized a professional format) or your stress tested scenarios. There must be at least five (5) stress tested scenarios. Each one should have an accompanying recommended solution and alternative solution.

Use this example as a template. I expect your client letter to look like it could be handed to an actual client.

Grading Rubric for Retirement Planning Project

Total Points: 50

Written Assignment: Word Document

Assignment Points: 25

There will be 5 points awarded for each of 5 stress testing scenarios. Point reductions will be assessed for major grammatical errors or for faulty logic.

Base Case Retirement Projection: Excel File

Assignment Points: 25

The excel file that you turn in should be the base case scenario. Leave it in the unsolved state. You should include separate tabs for each of your stress testing scenarios. The points are equally distributed across each of the 15 areas listed below. You will receive points for each category listed below that is calculated properly in your un-adjusted, base case scenario.

Estimated Value in Retirement (FV of the investments)

FV of Social Security

Private Savings Contribution

Surplus (or Deficit)

Funds Needed with Principal Erosion

PV of Inflation Protected Stream (with Principal Erosion)

PV of Non-Inflation Protected Stream (with Principal Erosion)

DIPP Assuming Principal Erosion

Additional Target Funding Needed (with Principal Erosion))

Level Annual Funding (with Principal Erosion)

PV of Target for Stepped-Up Calculation (with Principal Erosion)

Initial Stepped-Up Funding Amount (with Principal Erosion))

Funds Needed Income Only

DIPP Income Only

Additional Target Funding Needed Income Only

Your fictitious client is John Wilson (pictured above). He is currently single and 55 years old. He plans to retire at age 66. He estimates his life expectancy to be age 90. This is a conservative assumption factoring his health and family history, and it extends well past the government table-issued life expectancy. Further case details are as follows:

His current salary is $75,000, he is saving 10% of his salary each year, and his desired replacement ratio is 80%.

He is planning on earning a 7% nominal return on his investments (average over time) and an assumed inflation rate of 4%.

He is fortunate enough to have a defined benefit plan, which is expected to provide $20,000 of retirement income annually (in todays dollars). His pension is not adjusted for inflation during retirement, but it is during his accumulation years.

He currently has $50,000 in a 401(k), which is estimated to earn 7% annually in nominal terms and he wants to leave a $30,000 estate for his niece.

His PIA (in today's dollars) is $2,109.34 (based upon AIME of $5,600). This is at his NRA.

His house is three years away from being completely paid off (mortgage-free during retirement) and is currently valued at $250,000.

Having taken an investments class in undergraduate studies and continuing to remain well-read in the discipline, he has a sophisticated understanding of investments.