as attached

profileashley016
20161006140801retirement_planning_template_2016.xls
Home>Business & Finance homework help>Management homework help>as attached

Retirement Savings Worksheet

Retirement Savings Worksheet
Working cash flows Retirement cash flows
Input data Tax Deferred Savings Taxable Savings
Current age 35 Age Salary Regular Savings Additonal Contributions Balance Regular Savings Additonal Contributions (withdrawals) Balance Age Regular Withdrawal Additonal Withdrawals (Contributions) Total Withdrawal Balance after Withdrawal
Retirement age 62 1 36 $ - 0 $ - 0 $ - 0 $ - 0 $ - 0 62 $ - 0 $ - 0 $ - 0
Life expectancy 92 2 37 $ - 0 $ - 0 $ - 0 $ - 0 $ - 0 63 $ - 0 $ - 0 $ - 0
Expected inflation 3.0% 3 38 $ - 0 $ - 0 $ - 0 $ - 0 $ - 0 64 $ - 0 $ - 0 $ - 0
Tax deferred savings return 6.0% 4 39 $ - 0 $ - 0 $ - 0 $ - 0 $ - 0 65 $ - 0 $ - 0 $ - 0
Taxable savings return 6.0% 5 40 $ - 0 $ - 0 $ - 0 $ - 0 $ - 0 66 $ - 0 $ - 0 $ - 0
Retirement portfolio return 5.0% 6 41 $ - 0 $ - 0 $ - 0 $ - 0 $ - 0 67 $ - 0 $ - 0 $ - 0
Starting salary $ - 0 7 42 $ - 0 $ - 0 $ - 0 $ - 0 $ - 0 68 $ - 0 $ - 0 $ - 0
Salary growth rate 0.0% 8 43 $ - 0 $ - 0 $ - 0 $ - 0 $ - 0 69 $ - 0 $ - 0 $ - 0
Tax Deferred Savings 9 44 $ - 0 $ - 0 $ - 0 $ - 0 $ - 0 70 $ - 0 $ - 0 $ - 0
Employer's retirement contribution 0.0% 10 45 $ - 0 $ - 0 $ - 0 $ - 0 $ - 0 71 $ - 0 $ - 0 $ - 0
Your contribution 0.0% 11 46 $ - 0 $ - 0 $ - 0 $ - 0 $ - 0 72 $ - 0 $ - 0 $ - 0
Additional Contributions 12 47 $ - 0 $ - 0 $ - 0 $ - 0 $ - 0 73 $ - 0 $ - 0 $ - 0
$ amount $ - 0 13 48 $ - 0 $ - 0 $ - 0 $ - 0 $ - 0 74 $ - 0 $ - 0 $ - 0
or % of income 14 49 $ - 0 $ - 0 $ - 0 $ - 0 $ - 0 75 $ - 0 $ - 0 $ - 0
After-tax Savings 15 50 $ - 0 $ - 0 $ - 0 $ - 0 $ - 0 76 $ - 0 $ - 0 $ - 0
$ amount 16 51 $ - 0 $ - 0 $ - 0 $ - 0 $ - 0 77 $ - 0 $ - 0 $ - 0
or % of income 0.0% 17 52 $ - 0 $ - 0 $ - 0 $ - 0 $ - 0 78 $ - 0 $ - 0 $ - 0
18 53 $ - 0 $ - 0 $ - 0 $ - 0 $ - 0 79 $ - 0 $ - 0 $ - 0
Current retirement savings $ - 0 19 54 $ - 0 $ - 0 $ - 0 $ - 0 $ - 0 80 $ - 0 $ - 0 $ - 0
Current taxable savings $ - 0 20 55 $ - 0 $ - 0 $ - 0 $ - 0 $ - 0 81 $ - 0 $ - 0 $ - 0
Desired retirement income in current $ $ - 0 21 56 $ - 0 $ - 0 $ - 0 $ - 0 $ - 0 83 $ - 0 $ - 0 $ - 0
22 57 $ - 0 $ - 0 $ - 0 $ - 0 $ - 0 84 $ - 0 $ - 0 $ - 0
Computed data 23 58 $ - 0 $ - 0 $ - 0 $ - 0 $ - 0 85 $ - 0 $ - 0 $ - 0
Time to retirement 27 24 59 $ - 0 $ - 0 $ - 0 $ - 0 $ - 0 86 $ - 0 $ - 0 $ - 0
Years in retirement 30 25 60 $ - 0 $ - 0 $ - 0 $ - 0 $ - 0 87 $ - 0 $ - 0 $ - 0
Required savings at retirement $ - 0 26 61 $ - 0 $ - 0 $ - 0 $ - 0 $ - 0 88 $ - 0 $ - 0 $ - 0
Future value of retirement savings $ - 0 27 62 $ - 0 $ - 0 $ - 0 $ - 0 $ - 0 89 $ - 0 $ - 0 $ - 0
Surplus (Short fall) $ - 0 28 0 $ - 0 $ - 0 $ - 0 $ - 0 $ - 0 90 $ - 0 $ - 0 $ - 0
29 0 $ - 0 $ - 0 $ - 0 $ - 0 $ - 0 91 $ - 0 $ - 0 $ - 0
30 0 $ - 0 $ - 0 $ - 0 $ - 0 $ - 0 92 $ - 0 $ - 0 $ - 0
31 0 $ - 0 $ - 0 $ - 0 $ - 0 $ - 0 0 $ - 0 $ - 0 $ - 0
32 0 $ - 0 $ - 0 $ - 0 $ - 0 $ - 0 0 $ - 0 $ - 0 $ - 0
33 0 $ - 0 $ - 0 $ - 0 $ - 0 $ - 0 0 $ - 0 $ - 0 $ - 0
34 0 $ - 0 $ - 0 $ - 0 $ - 0 $ - 0 0 $ - 0 $ - 0 $ - 0
35 0 $ - 0 $ - 0 $ - 0 $ - 0 $ - 0 0 $ - 0 $ - 0 $ - 0
36 0 $ - 0 $ - 0 $ - 0 $ - 0 $ - 0 0 $ - 0 $ - 0 $ - 0
37 0 $ - 0 $ - 0 $ - 0 $ - 0 $ - 0 0 $ - 0 $ - 0 $ - 0
38 0 $ - 0 $ - 0 $ - 0 $ - 0 $ - 0 0 $ - 0 $ - 0 $ - 0
39 0 $ - 0 $ - 0 $ - 0 $ - 0 $ - 0 0 $ - 0 $ - 0 $ - 0
40 0 $ - 0 $ - 0 $ - 0 $ - 0 $ - 0 0 $ - 0 $ - 0 $ - 0
41 0 $ - 0 $ - 0 $ - 0 $ - 0 $ - 0 0 $ - 0 $ - 0 $ - 0
42 0 $ - 0 $ - 0 $ - 0 $ - 0 $ - 0 0 $ - 0 $ - 0 $ - 0
43 0 $ - 0 $ - 0 $ - 0 $ - 0 $ - 0 0 $ - 0 $ - 0 $ - 0
44 0 $ - 0 $ - 0 $ - 0 $ - 0 $ - 0 0 $ - 0 $ - 0 $ - 0
45 0 $ - 0 $ - 0 $ - 0 $ - 0 $ - 0 0 $ - 0 $ - 0 $ - 0
Thomas Gosnell: This is the starting salary growing at the assumed growth rate.
Thomas Gosnell: This is the year's salary times (the employer's contribution rate + the employee's contribution rate+ the employer's match)
Thomas Gosnell: This is the retirement in in future dollars.

Asset Allocation Optimizer

Asset Class Returns and Risks
U.S. Equity Large Cap U.S. Equity Small Cap International Developed Equity Emerging Markets Equity U.S. Investment Grade U.S. High Yield US TIPS Global Aggregate Ex -US
Expected Return 7.20% 8.20% 6.90% 8.90% 3.30% 5.90% 2.50% 0.90%
Estimated fees 0.00% 0.00% 0.00% 0.00% 0.00% 0.00% 0.00% 0.00%
Net Expected Return 7.20% 8.20% 6.90% 8.90% 3.30% 5.90% 2.50% 0.90%
Risk 16.10% 21.00% 17.90% 23.80% 5.40% 9.80% 6.20% 8.00%
Correlation Matrix (Source: BNY Mellon ISSG)
U.S. Equity Large Cap U.S. Equity Small Cap International Developed Equity Emerging Markets Equity U.S. Investment Grade U.S. High Yield US TIPS Global Aggregate Ex -US
U.S. Equity Large Cap 1.00 0.90 0.87 0.77 0.22 0.69 0.07 0.26
U.S. Equity Small Cap 0.90 1.00 0.77 0.72 0.16 0.66 0.02 0.21
International Developed Equity 0.87 0.77 1.00 0.85 0.34 0.71 0.18 0.47
Emerging Markets Equity 0.77 0.72 0.85 1.00 0.33 0.71 0.24 0.40
U.S. Investment Grade 0.22 0.16 0.34 0.33 1.00 0.53 0.74 0.53
U.S. High Yield 0.69 0.66 0.71 0.71 0.53 1.00 0.32 0.33
US TIPS 0.07 0.02 0.18 0.24 0.74 0.32 1.00 0.55
Global Aggregate Ex -US 0.26 0.21 0.47 0.40 0.53 0.33 0.55 1.00
Covariance Matrix (estimated)
U.S. Equity Large Cap U.S. Equity Small Cap International Developed Equity Emerging Markets Equity U.S. Investment Grade U.S. High Yield US TIPS Global Aggregate Ex -US
U.S. Equity Large Cap 0.025921 0.030429 0.025073 0.029505 0.001913 0.010887 0.000699 0.003349
U.S. Equity Small Cap 0.030429 0.044100 0.028944 0.035986 0.001814 0.013583 0.000260 0.003528
International Developed Equity 0.025073 0.028944 0.032041 0.036212 0.003286 0.012455 0.001998 0.006730
Emerging Markets Equity 0.029505 0.035986 0.036212 0.056644 0.004241 0.016560 0.003541 0.007616
U.S. Investment Grade 0.001913 0.001814 0.003286 0.004241 0.002916 0.002805 0.002478 0.002290
U.S. High Yield 0.010887 0.013583 0.012455 0.016560 0.002805 0.009604 0.001944 0.002587
US TIPS 0.000699 0.000260 0.001998 0.003541 0.002478 0.001944 0.003844 0.002728
Global Aggregate Ex -US 0.003349 0.003528 0.006730 0.007616 0.002290 0.002587 0.002728 0.006400
U.S. Equity Large Cap U.S. Equity Small Cap International Developed Equity Emerging Markets Equity U.S. Investment Grade U.S. High Yield US TIPS Global Aggregate Ex -US
Weights 12.50% 12.50% 12.50% 12.50% 12.50% 12.50% 12.50% 12.50% 100.00%
Minimum weight 0% 0% 0% 0% 0% 0% 0% 0%
Maximum weight 100% 100% 100% 100% 100% 100% 100% 100%
Portfolio
Expected Return= 5.48%
Risk= 10.96%