I am needing help with my assignment, I have started the assignment on the Excell worksheet that has been uploaded. Of the other two word cocuments one is the steps to completing the worksheet and the other is the information for imputting. Any help is appreciated. 
Client Name Retirement Funding Assumptions Date for Financial Plan 2012 Economic Data Inflation Rate Before Retirement Before-Tax Inv Rate Before Retirement After- Tax Investment Rate After Retirement Before- Tax Inv. Rate After Retiarement After- Tax Investment Rate Marginal Tax Rate Husband H- Age H- Retirement Age H- Years to Retirement H- Life Expectancy H- Years in Retirement H- Social Security Start H- Projected FRA Monthly Soc. Sec. Today H- Projected FRA Annual Soc. Sec. Today H- Adjustment for Early Retirement H- Adjusted Annual Soc. Sec. Today H- Defined Contribution Plan H- IRA H- Roth IRA H- Other Assets for Retirement H-Total Retirement Assets H- Expected Defined Benefit Pmts H- Start of DB Pmts H- Retirement Plan Contributions H- Employer Match Percentage H- Employer Contributions H- Expected Contribution Escalation H- Current Total Employee/Employer Contributions Wife W- Age W- Retirement Age W- Years to Retirement W- Life Expectancy W- Years in Retirement W- Social Security Start W- Projected FRA Monthly Soc. Sec. Today W- Projected FRA Annual Soc. Sec. Today W- Adjustment for Early Retirement W- Adjusted Annual Soc. Sec. Today W- Defined Contribution Plan W- IRA W- Roth IRA W- Other Assets for Retirement W-Total Retirement Assets W- Expected Defined Benefit Pmts W- Start of DB Pmts W- Retirement Plan Contributions W- Employer Match Percentage W- Employer Contributions W- Expected Contribution Escalation W- Current Total Employee/Employer Contributions Other Data Joint Total Retirement Assets Joint Annual Needs in Today's $$ Survivor Needs as Percentage of Joint Survivor Annual Needs in Today's $$ Additional Travel Funds in Today's $$ Additional Expenses in todays $$ Additional Funds for Inheritance Total Employee/Employer Contributions Plan Year 4/13/2013 14:56 3.90% 9.40% 6.90% 7.25% 5.30% 27.00% 47 65 18 85 20 65 1,049 12,588 100.00% 12,588 0 0 25,000 0 25,000 2,000 100.00% 2,000 4,000 43 62 19 90 28 62 1,300 15,600 70.00% 10,920 150,000 0 0 0 150,000 3,000 100.00% 3,000 6,000 175,000 60,000 200,000 10,000 2012 Years to Retirement Years in Retirement Survivor Annual Needs Today Projected Annual Soc. Sec Today Adjusted Annual Soc. Sec. Today Total Retirement Assets Joint Total Retirement Assets Total Employee/Employer contributions 37 48 0 28,188.00 47,919.60 175,000.00 175,000.00 10,000.00 Client Name Retirement Funding Analysis Date of Plan End of Year from Today H usband's Age Wife's Age Year in Retirement- H Year in Retirement- W Calendar Year Future Retirement Needs Social Security - H Social Security - W Defined Benefit Payments- H Defined Benefit Payments- W Inheritance Distribution N et Retirement Needs Amount Needed at Retirement 18 65 61 1 0 2030 19 66 62 2 1 2031 20 67 63 3 2 2032 T otal Amount Needed at Retirement Projected Value of Current Retirement Assets- Jt Surplus (shortfall) at retirement Current Surplus (shortfall) Total Payment Needed Monthly Payment to fund shortfall- Beginning Monthly Payment to fund shortfall- Ending Annual Payment to fund shortfall- Beginning Annual Payment to fund shortfall- Ending Current Retirement Savings Required Supplemental Savings 21 68 64 4 3 2033 4/13/2013 14:56 22 69 65 5 4 2034 23 70 66 6 5 2035 24 71 67 7 6 2036 25 72 68 8 7 2037 26 73 69 9 8 2038 27 74 70 10 9 2039 28 75 71 11 10 2040 29 76 72 12 11 2041 30 77 73 13 12 2042 31 78 74 14 13 2043 32 79 75 15 14 2044 33 80 76 16 15 2045 34 81 77 17 16 2046 35 82 78 18 17 2047 36 83 79 19 18 2048 37 84 80 20 19 2049 38 85 81 21 20 2050 39 86 82 22 21 2051 40 87 83 23 22 2052 41 88 84 24 23 2053 42 89 85 25 24 2054 43 90 86 26 25 2055 44 91 87 27 26 2056 45 92 88 28 27 2057 46 93 89 29 28 2058 47 94 90 30 29 2059 48 95 91 31 30 2060 49 96 92 32 31 2061 50 97 93 33 32 2062 51 98 94 34 33 2063 52 99 95 35 34 2064 53 100 96 36 35 2065 54 101 97 37 36 2066 55 102 98 38 37 2067 56 103 99 39 38 2068 57 104 100 40 39 2069 58 105 101 41 40 2070 59 106 102 42 41 2071 60 107 103 43 42 2072 61 108 104 44 43 2073 62 109 105 45 44 2074 63 110 106 46 45 2075 64 111 107 47 46 2076 65 112 108 48 47 2077 66 113 109 49 48 2078 Client Name Retirement Sinking Fund Analysis Date for the Financial Plan Contribution Year Husbands Age Wife's Age Distribution for Retirement Year- H Distribution for Retirement Year- W Calendar Year Beginning Balance Annual Contribution Investment Return Annual Distribution Ending Balance 4/13/2013 14:56 1 48 44 (20) (21) 2013 2 49 45 (19.00) (20.00) 2014 3 50 46 (18.00) (19.00) 2015 4 51 47 (17.00) (18.00) 2016 5 52 48 (16.00) (17.00) 2017 6 53 49 (15.00) (16.00) 2018 7 54 50 (14.00) (15.00) 2019 Client Name Retirement Funding Assumptions Date for the Financial Plan Economic Data Inflation Rate Before Retirement Before-Tax Inv Rate Before Retirement After-Tax Investment Rate After Retirement Before-Tax Inv Rate After Retirement After-Tax Investment Rate Marginal Tax Rate 3.9% 9.4% 6.9% 7.25% 5.3% 27% Husband H - Age H - Retirement Age H - Years to Retirement H - Life Expectancy H - Years in Retirement H - Social Security Start H - Projected FRA Monthly Soc. Sec. Today H - Projected FRA Annual Soc. Sec. Today H - Adjustment for Early Retirement H - Adjusted Annual Soc. Sec. Today 47 65 18 85 20 65 1,049 12,588 100% 12,588 H - Defined Contribution Plan - H - IRA H - Roth IRA - H - Other Assets for Retirement H - Total Retirement Assets H - Expected Defined Benefit Pmts (Current Dollars) H - Start of DB Pmts H - Retirement Plan Contributions H - Employer Match Percentage H - Employer Contributions H - Expected Contribution Escalation H - Current Total Employee/Employer Contributions 25,000 25,000 2,000 100% 2,000 4,000 Wife W - Age W - Retirement Age W - Years to Retirement W - Life Expectancy 43 62 19 90 W - Years in Retirement W - Social Security Start W - Projected FRA Monthly Soc. Sec. Today W - Projected FRA Annual Soc. Sec. Today W - Adjustment for Early Retirement W - Adjusted Annual Soc. Sec. Today 28 62 1,300 15,600 70% 10,920 150,00 0 W - Defined Contribution Plan W - IRA - W - Roth IRA - W - Other Assets for Retirement - W - Total Retirement Assets W - Expected Defined Benefit Pmts (Current Dollars) W - Start of DB Pmts W - Retirement Plan Contributions W - Employer Match Percentage W - Employer Contributions W - Expected Contribution Escalation W - Current Total Employee/Employer Contributions 150,00 0 3,000 100% 3,000 6,000 Other Data Joint Total Retirment Assets Joint Annual Needs in Today's $$ Survivor Needs as a Percentage of Joint Survivor Annual Needs in Today's $$ Additional Travel Funds in Today's $$ Additional Expenses in Today's $$ Additional Funds for Inheritance Total Employee/Employer Contributions Plan Year 175,00 0 60,000 200,00 0 10,000 2012 RETIREMENT FUNDING using EXCEL Steps to Worksheet Development - - - Three Worksheets - - - - - - Important Concepts Retirement Funding Assumptions Retirement Funding Analysis Retirement Sinking Fund Take BABY STEPS!! All Assumptions variables will be \"named\" All Inputs in calculations will be referenced No information is to be typed into a formula Exception - \"12\" when converting Monthly to Annual or vice versa You must know how to use Absolute, Relative, and semi-fixed cell references ROUND projections to the nearest $1,000!! Round all PVs and savings amounts to the nearest penny Sinking fund will not zero out in last year if you round PV's or savings amounts to nearest $ or larger Assumptions - - - - Rename Sheet 1 \"Retirement Funding Assumptions\" Heading (Cells A1-A3) Client Name Retirement Funding Assumptions Date for the Financial Plan - You will also want to have a cell on each worksheet that will show the last date and time the worksheet was changed (Cell F1) =NOW() Format the cell to show the date and time. This will update whenever the worksheet is calculated. Assumptions Setup - Variable Names are listed in column A - Variables must be listed in column B 1 Input the Following Variable Names Economic Data (Bold) Inflation Rate (Indent) Before Retirement Before-Tax Inv Rate Before Retirement After-Tax Investment Rate After Retirement Before-Tax Inv Rate After Retirement After-Tax Investment Rate Marginal Tax Rate Husband (Bold) H - Age H - Retirement Age H - Years to Retirement H - Life Expectancy H - Years in Retirement H - Social Security Start H - Projected FRA Monthly Soc. Sec. Today H - Projected FRA Annual Soc. Sec. Today H - Adjustment for Early Retirement H - Adjusted Annual Soc. Sec. Today H - Defined Contribution Plan H - IRA H - Roth IRA H - Other Assets for Retirement H - Total Retirement Assets H - Expected Defined Benefit Pmts H - Start of DB Pmts H - Retirement Plan Contributions H - Employer Match Percentage H - Employer Contributions H - Expected Contribution Escalation H - Current Total Employee/Employer Contributions Wife (Bold) W - Age W - Retirement Age W - Years to Retirement W - Life Expectancy W - Years in Retirement W - Social Security Start W - Projected FRA Monthly Soc. Sec. Today W - Projected FRA Annual Soc. Sec. Today W - Adjustment for Early Retirement W - Adjusted Annual Soc. Sec. Today W - Defined Contribution Plan W - IRA 2 W - Roth IRA W - Other Assets for Retirement W - Total Retirement Assets W - Expected Defined Benefit Pmts W - Start of DB Pmts W - Retirement Plan Contributions W - Employer Match Percentage W - Employer Contributions W - Expected Contribution Escalation W - Current Total Employee/Employer Contributions Other Data Joint Total Retirement Assets Joint Annual Needs in Today's $$ Survivor Needs as Percentage of Joint Survivor Annual Needs in Today's $$ Additional Travel Funds in Today's $$ Additional Expenses in Today's $$ Additional Funds for Inheritance Total Employee/Employer Contributions Plan Year Input Variable Information Formatting Requirements Use \Client Name Retirement Funding Assumptions Date for Financial Plan 2012 Economic Data Inflation Rate Before Retirement Before-Tax Inv Rate Before Retirement After- Tax Investment Rate After Retirement Before- Tax Inv. Rate After Retiarement After- Tax Investment Rate Marginal Tax Rate Husband H- Age H- Retirement Age H- Years to Retirement H- Life Expectancy H- Years in Retirement H- Social Security Start H- Projected FRA Monthly Soc. Sec. Today H- Projected FRA Annual Soc. Sec. Today H- Adjustment for Early Retirement H- Adjusted Annual Soc. Sec. Today H- Defined Contribution Plan H- IRA H- Roth IRA H- Other Assets for Retirement H-Total Retirement Assets H- Expected Defined Benefit Pmts H- Start of DB Pmts H- Retirement Plan Contributions H- Employer Match Percentage H- Employer Contributions H- Expected Contribution Escalation H- Current Total Employee/Employer Contributions Wife W- Age W- Retirement Age W- Years to Retirement W- Life Expectancy W- Years in Retirement W- Social Security Start W- Projected FRA Monthly Soc. Sec. Today W- Projected FRA Annual Soc. Sec. Today W- Adjustment for Early Retirement W- Adjusted Annual Soc. Sec. Today W- Defined Contribution Plan W- IRA W- Roth IRA W- Other Assets for Retirement W-Total Retirement Assets W- Expected Defined Benefit Pmts W- Start of DB Pmts W- Retirement Plan Contributions W- Employer Match Percentage W- Employer Contributions W- Expected Contribution Escalation W- Current Total Employee/Employer Contributions Other Data Joint Total Retirement Assets Joint Annual Needs in Today's $$ Survivor Needs as Percentage of Joint Survivor Annual Needs in Today's $$ Additional Travel Funds in Today's $$ Additional Expenses in todays $$ Additional Funds for Inheritance Total Employee/Employer Contributions Plan Year 4/13/2013 14:56 3.90% 9.40% 6.86% 7.25% 5.29% 27.00% 47 65 18 85 20 65 1,049.00 12,588.00 100.00% 12,588.00 0.00 0.00 25,000.00 0.00 25,000.00 2,000 100.00% 2,000 4,000 43 62 19 90 28 62 1,300.00 15,600.00 70.00% 10,920.00 150,000.00 0.00 0.00 0.00 150,000.00 3,000.00 100.00% 3,000.00 6,000.00 175,000.00 60,000.00 0.00 200,000 10,000 2012 Years to Retirement Years in Retirement Survivor Annual Needs Today Projected Annual Soc. Sec Today Adjusted Annual Soc. Sec. Today Total Retirement Assets Joint Total Retirement Assets Total Employee/Employer contributions 37 48 0.00 28,188.00 47,919.60 175,000.00 175,000.00 10,000.00 Client Name Retirement Funding Analysis Date of Plan End of Year from Today Husband's Age Wife's Age Year in Retirement- H Year in Retirement- W Calendar Year Future Retirement Needs Social Security - H Social Security - W Defined Benefit Payments- H Defined Benefit Payments- W Inheritance Distribution Net Retirement Needs Amount Needed at Retirement T otal Amount Needed at Retirement Projected Value of Current Retirement Assets- Jt Surplus (shortfall) at retirement Current Surplus (shortfall) Monthly Payment to fund shortfall- Beginning Monthly Payment to fund shortfall- Ending Annual Payment to fund shortfall- Beginning Annual Payment to fund shortfall- Ending 18 65 61 1 0 2030 $119,000.00 ($25,063.20) 0.00 0.00 0.00 19 66 62 2 1 2031 $124,000.00 ($26,040.67) (22,590.09) 20 21 67 68 63 64 3 4 2 3 2032 2033 $129,000.00 $134,000.00 ($27,056.26) ($28,111.45) (23,471.11) (24,386.48) $93,936.80 ($93,936.80) $75,369.24 ($70,274.35) $78,472.64 $81,502.07 ($68,221.87) ($66,065.81) 4/13/2013 14:56 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 2034 2035 2036 2037 2038 2039 2040 2041 2042 2043 2044 2045 2046 2047 2048 2049 $139,000.00 $145,000.00 $150,000.00 $156,000.00 $162,000.00 $169,000.00 $175,000.00 $182,000.00 $189,000.00 $196,000.00 $204,000.00 $212,000.00 $220,000.00 $229,000.00 $238,000.00 ($29,207.80) ($30,346.90) ($31,530.43) ($32,760.12) ($34,037.76) ($35,365.23) ($36,744.48) ($38,177.51) ($39,666.43) ($41,213.43) ($42,820.75) ($44,490.76) ($46,225.90) ($48,028.71) ($49,901.83) ($51,848.00) (25,337.55) (26,325.72) (27,352.42) (28,419.17) (29,527.51) (30,679.09) (31,875.57) (33,118.72) (34,410.35) (35,752.35) (37,146.69) (38,595.41) (40,100.64) (41,664.56) (43,289.48) (44,977.77) 38 85 81 21 20 2050 39 86 82 22 21 2051 40 87 83 23 22 2052 41 88 84 24 23 2053 42 89 85 25 24 2054 43 90 86 26 25 2055 44 91 87 27 26 2056 45 92 88 28 27 2057 46 93 89 29 28 2058 47 94 90 30 29 2059 ($53,870.07) (46,731.90) (48,554.44) (50,448.07) (52,415.54) (54,459.75) (56,583.68) (58,790.44) (61,083.27) (63,465.52) 48 95 91 31 30 2060 (65,940.67) $1,254,805.11 ($934,763.05) $805,995.32 ($128,767.73) ($27,958.42) T otal Payment Needed ($272.85) ($274.98) ($3,068.52) ($3,356.96) Current Required Retirement Supplemental Savings Savings 833.33 $560.49 833.33 $558.35 10,000.00 $6,931.48 10,000.00 $6,643.04 $84,454.65 $88,327.38 $91,117.15 $94,820.72 $98,434.73 $102,955.68 $106,379.95 $110,703.77 $114,923.22 $119,034.22 $124,032.56 $128,913.83 $133,673.47 $139,306.73 $144,808.69 ($96,825.77) ($100,601.97) ($48,554.44) ($50,448.07) ($52,415.54) ($54,459.75) ($56,583.68) ($58,790.44) ($61,083.27) ($63,465.52) ($65,940.67) ($63,831.40) ($62,245.63) ($59,870.97) ($58,092.78) ($56,230.24) ($54,837.12) ($52,830.75) ($51,261.59) ($49,618.10) ($47,918.91) ($46,555.77) ($45,116.98) ($43,620.27) ($42,385.57) ($41,081.21) $25,611.93 $24,811.93 $11,165.69 $10,816.93 $10,479.06 $10,151.74 $9,834.64 $9,527.46 $9,229.86 $8,941.56 $8,662.27 TOTAL $3,272,000.00 ($792,507.66) ($1,177,493.97) $0.00 $0.00 $1,254,805.11 $1,301,998.37 ($934,763.05) Client Name Retirement Sinking Fund Analysis Date for the Financial Plan Contribution Year Husbands Age Wife's Age Distribution for Retirement Year- H Distribution for Retirement Year- W Calendar Year Beginning Balance Annual Contribution I nvestment Return Annual Distribution Ending Balance 4/13/2013 14:56 1 48 44 (16) (17) 2013 175,000.00 $3,356.96 16,450.00 2 49 45 (15) (16) 2014 194,806.96 $ 3,356.96 18,311.85 3 50 46 (14) (15) 2015 216,475.78 $ 3,356.96 20,348.72 4 51 47 (13) (14) 2016 240,181.47 $ 3,356.96 22,577.06 5 52 48 (12) (13) 2017 266,115.50 $ 3,356.96 25,014.86 6 53 49 (11) (12) 2018 294,487.32 $ 3,356.96 27,681.81 7 54 50 (10) (11) 2019 325,526.09 $ 3,356.96 30,599.45 8 55 51 (9) (10) 2020 359,482.51 $ 3,356.96 33,791.36 9 56 52 (8) (9) 2021 396,630.83 $ 3,356.96 37,283.30 10 57 53 (7) (8) 2022 4 37,271.09 $ 3,356.96 4 1,103.48 11 58 54 (6) (7) 2023 4 81,731.54 $ 3,356.96 4 5,282.76 12 59 55 (5) (6) 2024 530,371.27 $ 3,356.96 4 9,854.90 13 60 56 (4) (5) 2025 583,583.13 $ 3,356.96 54,856.81 14 61 57 (3) (4) 2026 641,796.91 $ 3,356.96 60,328.91 15 62 58 (2) (3) 2027 705,482.79 $ 3,356.96 66,315.38 16 63 59 (1) (2) 2028 775,155.13 $ 3,356.96 72,864.58 17 64 60 0 (1) 2029 851,376.68 $ 3,356.96 80,029.41 194,806.96 216,475.78 240,181.47 266,115.50 294,487.32 325,526.09 359,482.51 396,630.83 4 37,271.09 4 81,731.54 530,371.27 583,583.13 641,796.91 705,482.79 775,155.13 851,376.68 934,763.05 18 65 61 1 0 2030 934,763.05 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 2031 2032 2033 2034 2035 2036 2037 2038 2039 2040 2041 2042 2043 2044 2045 2046 2047 2048 2049 2050 2051 2052 2053 2054 2055 2056 2057 2058 2059 2060 995,722.96 1,062,448.60 1,133,786.86 1,210,077.51 1,291,685.16 1,378,928.60 1,472,294.93 1,572,161.81 1,679,007.03 1,793,270.75 1,915,570.33 2,046,423.16 2,186,456.90 2,336,345.05 2,496,737.70 2,668,404.93 2,852,172.97 3,048,855.77 3,259,399.18 3,502,725.49 3,763,966.73 4 ,040,374.52 4 ,336,959.15 4 ,655,188.82 4 ,996,638.34 5,362,996.94 5,756,076.52 6,177,820.61 6,630,313.85 7,115,792.30 60,959.90 66,725.64 71,338.26 76,290.65 81,607.66 87,243.44 93,366.33 99,866.88 106,845.21 114,263.72 122,299.58 130,852.83 140,033.75 149,888.14 160,392.66 171,667.23 183,768.03 196,682.80 210,543.41 243,326.31 ($93,936.80) ($75,369.24) ($78,472.64) ($81,502.07) ($84,454.65) ($88,327.38) ($91,117.15) ($94,820.72) ($98,434.73) ($102,955.68) ($106,379.95) ($110,703.77) ($114,923.22) ($119,034.22) ($124,032.56) ($128,913.83) ($133,673.47) ($139,306.73) ($144,808.69) $96,825.77 995,722.96 1,062,448.60 1,133,786.86 1,210,077.51 1,291,685.16 1,378,928.60 1,472,294.93 1,572,161.81 1,679,007.03 1,793,270.75 1,915,570.33 2,046,423.16 2,186,456.90 2,336,345.05 2,496,737.70 2,668,404.93 2,852,172.97 3,048,855.77 3,259,399.18 3,502,725.49 261,241.24 276,407.79 $ 100,601.97 $ 48,554.44 3,763,966.73 4 ,040,374.52 296,584.64 $ 50,448.07 4 ,336,959.15 318,229.67 341,449.52 $ 52,415.54 $ 54,459.75 4 ,655,188.82 4 ,996,638.34 366,358.60 $ 56,583.68 5,362,996.94 393,079.58 $ 58,790.44 5,756,076.52 4 21,744.08 $ 61,083.27 6,177,820.61 4 52,493.24 $ 63,465.52 6,630,313.85 4 85,478.45 $ 65,940.67 7,115,792.30
