Don and Dana Jenkins, both age 53, need your help in planning their retirement. Don is a manager and Dana is a bookkeeper. They have been married for just over 20 years. Don and Dana have two children who are married and have moved away from home. Don earns $70,000 a year; Dana earns $72,000 a year. Both expect their salaries to match inflation which is approximately 3% per year. Their federal tax bracket is 25%; their state tax bracket is 5%. They currently are both qualified for Social Security benefits. Don and Dana are looking forward to retiring in approximately 20 years. They are not sure how much they would need to have saved in order to maintain their standard of living. They question whether or not they should plan on taking Social Security benefits early or not. Both of them are in good health and they do not anticipate needing extraordinary healthcare measures. According to current mortality tables, both Don and Dana have a life expectancy to age 85. If they work until they are eligible for full Social Security retirement benefits, they would like to know how much they would need to save on a regular basis so they would not run out of money in retirement. Their assets, liabilities, and expenses are listed below. Please complete the retirement planning worksheets in steps 2 and 3 to determine a reasonable estimate for the amount they should have in their retirement savings plan on the day that they retire.

Now that you know the estimated living expenses in retirement, use this spreadsheet to compute how much is needed in the retirement fund. Then compute the monthly savings needed to reach that goal. Assume 3% inflation and 5% net after tax return on investments. Submit your completed spreadsheet below.

Please based on picture 1 and 2 to calculate 3 and 4.

Now that you know the estimated living expenses in retirement, use this spreadsheet to compute how much is needed in the retirement fund. Then compute the monthly savings needed to reach that goal. Assume 3% inflation and 5% net after tax return on investments. Submit your completed spreadsheet below.

ASSETS LIABILITIES Checking Account $15,000 Mortgage Balance Savings Account $25,000 BMW Auto Loan Balance $300,000 $10,000 $5,100 Don's 401(k) $425,000 VISA Credit Card Don's traditional IRA $75,000 Dana's 401(k) $525,000 Dana's Roth IRA $125,000 Primary Residence $550,000 BMW Automobile $55,000 Mazda Automobile $45,000 Household Furnishings $50,000 Artwork $15,000 Note: They expect to have their house fully paid for before retirement. If they downsize, they do not plan to carry a mortgage. They do plan on having 1 car payment on an ongoing basis. Their current living expenses are summarized below. EXPENSE CURRENT AMOUNT Pretax Medical Premiums Don's 401(k) contributions Dana's 401(k) contributions Don's traditional IRA Dana's Roth IRA Mortgage payment Property tax License plates Homeowners insurance Car payments Auto insurance Food/groceries Dining out Clothing Household maintenance Utilities Travel Entertainment Charitable contributions Gifts Miscellaneous personal expense $4800 $3500 $4000 $5000 $5000 $1050 $4500 $300 $2000 $450 $750 $400 $300 $400 $100 $350 $3000 $500 $100 $2000 $100 FREQUENCY Annually Annually Annually Annually Annually Monthly Annually Annually Annually Monthly Semi-annually Monthly Monthly Quarterly Monthly Monthly Annually Monthly Monthly Annually Monthly LIVING EXPENSES IN RETIREMENT Complete the annual amount by extending current amount by frequency. Estimate their annual retirement expenses based on the data they provided. EXPENSE CURRENT FREQUENCY ANNUAL EXPENSES in AMOUNT AMOUNT RETIREMENT Pretax Medical Premiums Don's 401(k) contributions Dana's 401(k) contributions Don's traditional IRA Dana's Roth IRA Mortgage payment Property tax License plates Homeowners insurance Car payments Auto insurance Food/groceries Dining out Clothing Household maintenance Utilities Travel Entertainment Charitable contributions Gifts Miscellaneous personal expense $4,800 Annually $3,500 Annually $4,000 Annually $5,000 Annually $5,000 Annually $1,050 Monthly $4,500 Annually $300 Annually $2,000 Annually $450 Monthly $750 Semi-annually $400 Monthly $300 Monthly $400 Quarterly $100 Monthly $350 Monthly $3,000 Annually $500 Monthly $100 Monthly $2,000 Annually $100 Monthly TOTALS 0 0 Retirement Goal Current annual living expenses Current inflation rate Number of years in retirement Years until retirement starts Anticipated annual return on retirement savings Step 1: Future Value of Annual Living Expenses PV = (Annual expenses) I/Y (Current inflation rate) N = (Years until retirement starts) PMT= 0 CPT FV = ?? (Annual living expenses in future dollars) Step 2: Cost of Retirement N = (Number of years in retirement) I/Y = (Retirement inflation rate) PMT = (Annual living expenses computed above) FV = 0 CPT PV = ??? (Future amount needed for retirement years) Step 3: Compute MONTHLY savings needed Note: Be sure to convert from annual to monthly N = (Number of months until retirement starts) PV = (Current amount in retirement savings fund I/Y = (Monthly interest rate for retirement savings fund) FV = (Cost of retirement computed above) CPT Pmt = ??? (MONTHLY deposit into retirement fund) MONTHLY SAVINGS FOR RETIREMENT Variables ASSETS LIABILITIES Checking Account $15,000 Mortgage Balance Savings Account $25,000 BMW Auto Loan Balance $300,000 $10,000 $5,100 Don's 401(k) $425,000 VISA Credit Card Don's traditional IRA $75,000 Dana's 401(k) $525,000 Dana's Roth IRA $125,000 Primary Residence $550,000 BMW Automobile $55,000 Mazda Automobile $45,000 Household Furnishings $50,000 Artwork $15,000 Note: They expect to have their house fully paid for before retirement. If they downsize, they do not plan to carry a mortgage. They do plan on having 1 car payment on an ongoing basis. Their current living expenses are summarized below. EXPENSE CURRENT AMOUNT Pretax Medical Premiums Don's 401(k) contributions Dana's 401(k) contributions Don's traditional IRA Dana's Roth IRA Mortgage payment Property tax License plates Homeowners insurance Car payments Auto insurance Food/groceries Dining out Clothing Household maintenance Utilities Travel Entertainment Charitable contributions Gifts Miscellaneous personal expense $4800 $3500 $4000 $5000 $5000 $1050 $4500 $300 $2000 $450 $750 $400 $300 $400 $100 $350 $3000 $500 $100 $2000 $100 FREQUENCY Annually Annually Annually Annually Annually Monthly Annually Annually Annually Monthly Semi-annually Monthly Monthly Quarterly Monthly Monthly Annually Monthly Monthly Annually Monthly LIVING EXPENSES IN RETIREMENT Complete the annual amount by extending current amount by frequency. Estimate their annual retirement expenses based on the data they provided. EXPENSE CURRENT FREQUENCY ANNUAL EXPENSES in AMOUNT AMOUNT RETIREMENT Pretax Medical Premiums Don's 401(k) contributions Dana's 401(k) contributions Don's traditional IRA Dana's Roth IRA Mortgage payment Property tax License plates Homeowners insurance Car payments Auto insurance Food/groceries Dining out Clothing Household maintenance Utilities Travel Entertainment Charitable contributions Gifts Miscellaneous personal expense $4,800 Annually $3,500 Annually $4,000 Annually $5,000 Annually $5,000 Annually $1,050 Monthly $4,500 Annually $300 Annually $2,000 Annually $450 Monthly $750 Semi-annually $400 Monthly $300 Monthly $400 Quarterly $100 Monthly $350 Monthly $3,000 Annually $500 Monthly $100 Monthly $2,000 Annually $100 Monthly TOTALS 0 0 Retirement Goal Current annual living expenses Current inflation rate Number of years in retirement Years until retirement starts Anticipated annual return on retirement savings Step 1: Future Value of Annual Living Expenses PV = (Annual expenses) I/Y (Current inflation rate) N = (Years until retirement starts) PMT= 0 CPT FV = ?? (Annual living expenses in future dollars) Step 2: Cost of Retirement N = (Number of years in retirement) I/Y = (Retirement inflation rate) PMT = (Annual living expenses computed above) FV = 0 CPT PV = ??? (Future amount needed for retirement years) Step 3: Compute MONTHLY savings needed Note: Be sure to convert from annual to monthly N = (Number of months until retirement starts) PV = (Current amount in retirement savings fund I/Y = (Monthly interest rate for retirement savings fund) FV = (Cost of retirement computed above) CPT Pmt = ??? (MONTHLY deposit into retirement fund) MONTHLY SAVINGS FOR RETIREMENT Variables