| Excel Worksheet 10.2 Personal Financial Planner : Retirement Planning Worksheet |
| Directions: Use this worksheet to estimate a monthly savings goal that will be sufficient to meet your retirement funding objectives. In the blue-shaded cells, enter information about your current annual expenses and your expectations for changes that will occur in retirement. The solution will calculate in the peach-shaded cells. |
| STEP 1. Estimate before-tax income needs. |
| a. Enter current annual household expenses. |
| b. Adjust for changes in expenses in retirement. |
| Possible reductions: |
| Employment expenses |
| Retirement savings |
| Housing expenses |
| Total reductions |
|
| Possible increases: |
| Health care/insurance |
| Leisure activities |
| Gifts/donations |
| Total increases |
|
| c. Adjusted expenses in current dollars
= Expenses − Reductions + Increases | |
|
| d. Adjust for inflation to future dollars |
| Expected annual inflation rate between now and retirement |
| Years until retirement |
| After-tax expenses in first year of retirement |
|
| e. Calculate before-tax total income needs: |
| Expected tax rate in retirement |
| Before-tax income needs = After-tax needs ÷ (1 − Tax rate) | |
|
| STEP 2. Estimate annual retirement income from defined benefit retirement plan(s). |
| Total income from defined benefit plans
Use your most recent statement from your pension sponsor or
estimate based on known benefit formula(s). | | | | | | | |
| 40000 |
| Use calculator at www.ssa.gov to estimate Social Security benefit (in future dollars). |
| STEP 4. Retirement income shortfall in first year of retirement. |
| Before-tax income needs less expected benefits from pensions and Social Security = Step 1 − Step 2 − Step 3 | |
|
| STEP 5. Estimate total retirement wealth needed. |
| a. Expected rate of return on retirement savings |
| b. Expected annual inflation rate during retirement |
| c. Expected number of years you will be retired (until death) |
| d. Inflation-adjusted discount rate | 0.0% |
| e. Retirement wealth factor | 0.0000 |
| f. Retirement wealth needed | |
|
| STEP 6. Estimate retirement savings goal. |
| a. Total value of current retirement savings (DC, IRA, other
savings) |
| b. Future value of current retirement savings with no additional funding (assumes return on investment and years to retirement as above) | | $0 |
| c. Retirement savings goal | | $0 |
| STEP 7. Estimate monthly savings required to meet retirement funding objective. |
| a. Current monthly contribution to employer retirement plan |
| b. Additional monthly savings to reach retirement funding objective. | | $0 |
| |
meero181
Excel_Worksheet_10.xlsx
