Retierment Analysis

Excel Retirement Analysis Project

You are to design a spreadsheet that will allow your friends, parents, and eventually yourself to determine whether they can afford to retire. This spreadsheet will need to project annual income, expenses and remaining assets (net worth) on an annual basis. I suggest you layout your income and expenses across the top of the columns and use the rows to list the resulting data for each year. I will want to see each form of income and/or expense as a separate column. I will expect you to sum the income and expenses into summary columns before you make any decisions. Each column will use conditional statements to determine the income or expenses for that year based on the age of the husband and/or wife (i.e. Social Security will be received each year from the age to begin SS until the income ceases at the age of death. However, remember to include the expected annual raise as COMPOUND interest).

You will implement a retirement analysis spreadsheet based on the following data:

Husband Wife
Current Age in 2009 55 53
Expected Age to collect Social Security 65 65
Expected Age at Death 85 87
Current Salary (per year) $30,000 $23,000
Expected Annual Raise 3% 4%
Second Career Income (per year) $5,000 $0
Beginning Second Career at age 57 55
Ends Second Career at age 62 55
Increase per year 0% 0%
Starting Pension (per year) $20,000 $18,000
Annual Pension Increase 1% 1%
Expected Social Security Starting at (per year) $10,000 $8,000r
Annual SS Raise 1% 1%
Current Joint Net Worth/Savings $200,000
Expected Interest Rate for Savings 4%
Current Joint Annual Expenses $52,000 per year
Inflation Rate for Expenses 3%
Calendar year of Retirement 2012

You are expected to create a Workbook Analysis Titled “Retirement Analysis”.

TAB 3 is to be titled “Calculations” and is to be a spreadsheet of your own design. Be sure to include Titles and proper formatting (including themes)
TAB 2 is to be titled “Graph” and contain a bar graph showing the annual NET WORTH of the couple’s assets (NOTE: You cannot retire if NET WORTH equals zero prior to death of BOTH husband and wife). Again, be sure to use titles and graph types to display the data. A second pie graph illustrating the source of income over the couple’s lifetime would also be helpful.
TAB 1 is to contain a Dashboard Presentation as shown in an attachment.

NOTE: The core data listed above should be listed in tabular form on BOTH the dashboard and spreadsheet and need to be linked such that changing the data on the dashboard causes the spreadsheet to recalculate driving the colored dashboard lights. Set the master data on the dashboard and use a formula to copy to the spreadsheet.

Assumptions:
All calculations and changes are made annually on January 1st of each year
Any income in excess of expenses is automatically deposited in Savings