Modelling Assignment #2 - Stellar Trimmed.xlsx - CMIS 2250...

This preview shows page 1 - 13 out of 44 pages.

CMIS 2250 Modeling Assign # 2 Retirement Planning Parameters Base Case Initial assets 167 167 Salary 116 116 Salary growth 5% #NAME? 5% Inflation 2% 2% Investment return pre-retire 9% 9% Investment return post-retire 5% 5% Retire date 65 #NAME? 65 Post-retirement withdrawal rate 50% #NAME? 50% Post-retirement tax rate 30% 30% SS Income at retirement 30 30 Decisions Savings Rate 10% #NAME? Outputs Y axis Last salary 266 Base Case 266 Initial post-retirement income 133 Base Case 133 Year assets = 0 #N/A Base Case 85 Retirement asset balance in first year 1999 Base Case 1999 Years in Retirement #N/A Base Case 20 Calculations Retirement Post-retirement SS Age Salary Contributions Needs Income 47 116 23 0 0 48 122 24 0 0 49 128 26 0 0 50 134 27 0 0 51 141 28 0 0 52 148 30 0 0 53 155 31 0 0 54 163 33 0 0 55 171 34 0 0 56 180 36 0 0 57 189 38 0 0 58 198 40 0 0 59 208 42 0 0 60 219 44 0 0 61 230 46 0 0 62 241 48 0 0 63 253 51 0 0 64 266 53 0 0 65 0 0 133 30 66 0 0 136 31
67 0 0 138 31 68 0 0 141 32 69 0 0 144 32 70 0 0 147 33 71 0 0 150 34 72 0 0 153 34 73 0 0 156 35 74 0 0 159 36 75 0 0 162 37 76 0 0 165 37 77 0 0 169 38 78 0 0 172 39 79 0 0 175 40 80 0 0 179 40 81 0 0 182 41 82 0 0 186 42 83 0 0 190 43 84 0 0 194 44 85 0 0 198 45 86 0 0 201 45 87 0 0 206 46 88 0 0 210 47 89 0 0 214 48 90 0 0 218 49 91 0 0 222 50 92 0 0 227 51 93 0 0 231 52 94 0 0 236 53 95 0 0 241 54 96 0 0 246 55 97 0 0 251 57 98 0 0 256 58 99 0 0 261 59 100 0 0 266 60 101 0 0 271 61 102 0 0 277 62 103 0 0 282 64 104 0 0 288 65 105 0 0 294 66 106 0 0 299 68 107 0 0 305 69 108 0 0 311 70 109 0 0 318 72 110 0 0 324 73
Difference to Base Case 0 0 #N/A 0 #N/A Post-retirement Retirement Year Assets = 0 Taxes Assets #N/A 0 167 0 Err:522 47 0 205 0 0 48 0 248 0 0 49 0 296 0 0 50 0 349 0 0 51 0 409 0 0 52 0 476 0 0 53 0 549 0 0 54 0 632 0 0 55 0 723 0 0 56 0 824 0 0 57 0 936 0 0 58 0 1059 0 0 59 0 1196 0 0 60 0 1348 0 0 61 0 1515 0 0 62 0 1700 0 0 63 0 1903 0 0 64 31 1999 0 0 65 31 1965 0 0 66 4748495051525354555657585960616263646566676869707172737475767 -4000 -3000 -2000 -1000 0 1000 2000 3000 Retirement Assets in Working Years and R Assets Age Retirement Assets ($1000)
32 1926 0 0 67 33 1883 0 0 68 33 1836 0 0 69 34 1783 0 0 70 35 1724 0 0 71 35 1659 0 0 72 36 1589 0 0 73 37 1511 0 0 74 38 1427 0 0 75 38 1335 0 0 76 39 1236 0 0 77 40 1128 0 0 78 41 1011 0 0 79 42 885 0 0 80 42 749 0 0 81 43 603 0 0 82 44 446 0 0 83 45 277 0 0 84 46 96 0 0 85 47 -98 1 1 86 48 -306 1 0 87 49 -528 1 0 88 50 -766 1 0 89 51 -1019 1 0 90 52 -1290 1 0 91 53 -1578 1 0 92 54 -1886 1 0 93 55 -2213 1 0 94 56 -2561 1 0 95 57 -2932 1 0 96 58 -3325 1 0 97 59 -3744 1 0 98 61 -4188 1 0 99 62 -4660 1 0 100 63 -5161 1 0 101 64 -5692 1 0 102 66 -6255 1 0 103 67 -6851 1 0 104 68 -7484 1 0 105 70 -8153 1 0 106 71 -8862 1 0 107 72 -9613 1 0 108 74 -10407 1 0 109 75 -11247 1 0 110
777879808182838485868788899091929394959697 Retirement Years
0 0 0 0
CMIS 2250 Modeling Assign # 2 SENSITIVE Last Salary Before Retirement Retirement Date Amount of Salary($) (X-Axis) (Y-Axis) $D$10 [Modeling Assign 2 - Ch 4 and 5 Assignment.xlsx]Base Case'!$C$21 60 208 61 219 62 230 63 241 64 253 65 266 Base 66 279 67 293 68 308 69 323 70 339 Positive Non-Linear relationship exists. Salary amount variable increases as retirement date increases.
CMIS 2250 Modeling Assign # 2 SENSITIVE Initial Post-Retirement Income Retirement Date Amount of Income ($) (X-axis) (Y-axis) $D$10 [Modeling Assign 2 - Ch 4 and 5 Assignment.xlsx]Base Case'!$C$22 60 104 61 109 62 115 63 121 64 127 65 133 Base 66 140 67 147 68 154 69 162 70 170 Positive non-linear relationship (slight curve) between the retirement date and initial post-retirement income.
.
CMIS 2250 Modeling Assign # 2 SENSITIVE Retirement Assets at First Year of Retirement *Starting retirement assets at a age range 60 -70 **If he retires at 60, he'll have 1,059,000 in the bank ***Using Base Case Parameters Retirement Age Amount of Retirement Assets($) (X-Axis) (Y-Axis) $D$10 [Modeling Assign 2 - Ch 4 and 5 Assignment.xlsx]Base Case'!$C$24 60 1059 61 1196 62 1348 63 1515 64 1700 65 1903 BASE 66 2128 67 2375 68 2648 69 2947 70 3277 Positive Non-linear relationship (curved line) exists. Retirement asesets variable increase with age.

  • Left Quote Icon

    Student Picture

  • Left Quote Icon

    Student Picture

  • Left Quote Icon

    Student Picture