Excel Spreadsheet

Ben and Karen want to understand the consequences of changing the current education goal from $35,000 in the future to the future value of $35,000. What will be the new required savings rate? How will this impact the Capital Needs Analysis of Ben and Karen? How will this impact the retirement capital should they decide to fund college with their 401(k) plans?

a. OUTPUT: College Funding tab, CNA Karen tab, CNA Ben tab, Retire Capital with College tab

Capital Needs Analysis for Ben Assumptions Inflacin Investments Retirement Age Retirement Year Inflation SS Capital Pleeds Immediat Reserve Funeral Debts College Pre-Retirement Retirement Total Capital Needs Year B-ge 2023 2024 2025 2026 2027 2028 2029 2030 2031 2032 2033 2034 2035 2036 2037 2038 2039 2040 2041 2042 2043 2043 2044 2045 2046 2047 2048 2049 2049 2050 205169204,058 2052 2053 2053 2054 F G H E M N P D E J L B Capital Rleeds Assets Life Ins Kare 250,000 3.00% 39,230 6.00% 10,000 70 279,324 328,554 1.50% \begin{tabular}{|l|c|} \hline Pre-Retirement & - \\ \hline Retirement & - \\ \hline Total Capital Weeds & 328.554 \\ \hline \end{tabular} \begin{tabular}{|r|r|} \hline & \\ \hline & 250,000 \\ \hline \end{tabular} 10. 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 3233 34 35 36 37 39 40 41 42 43 Ben Retires SSFY 102,078 71103,609 72105,163 B-Income Carl SS Susie SS Edward SS Total Ine COL Debts Net NPV 4544 41 8,712 20,988 20,988 139,877 79,360 36,840 23,677$299,907 8,843 21,30321,303 143,313 81,741 36,840 24,732 \begin{tabular}{|l|l|r|} \hline 21,622 & 21,622 & 146,841 \\ \hline 21,947 & 21,947 & 150,463 \\ \hline 22,276 & 22,276 & 154,62 \\ \hline \end{tabular} 25,808 22,276 \begin{tabular}{r|r} 22,276 & 154,182 \end{tabular} 84,193 36,840 31,704 32,821 \begin{tabular}{r|r|r|} 22,610 & 22,610 & 158,000 \\ \hline \end{tabular} 89,320 32,040 33,960 22,949 \begin{tabular}{|r|r|} \hline 22,949 & 161,921 \\ \hline 23,293 & 156,27 \end{tabular} 92,000 32,040 24,960 42,201 23,643 156,278 33,715 116,372 [12,135] \begin{tabular}{|l|l|l|l|} \hline 119,863 & 106,653 & 24,960 & (11,751) \end{tabular} [369] 127,162 901,83424,960 369 130,977 104,889 24,960 1,129 134,907 108,035 1,911 138,954 24,960 2,717 15,009 \begin{tabular}{|r|r|r|r|} \hline 143,122 & 103,153 & 24,960 & 15,009 \\ \hline 147,416 & 106,248 & 24,960 & 16,208 \\ \hline \end{tabular} 151,838 109,435 24,960 17,443 156,394 18,715 161,085 112,718 24,960 20,026 165,918 116,100 107,624 24,960 33,334 170,896 110,853 24,960 35,082 176,022 114,179 117,604 24,960 36,884 186,742 40,650 \begin{tabular}{|r|r|r|r|} \hline 196,742 & 121,132 & 24,960 & 40,650 \\ \hline 192,344 & 124,766 & & 67,578 \\ \hline \end{tabular} 67,606 71,694 [34,257] (36,816) 105,163 [39,475] [533,384]1,554,6161,021,232 401(k) Plan 144,638 1,021,232 \begin{tabular}{rr} 198,115 & 128,509 \\ \hline 204,058 & 132,364 \\ \hline 102,078 & 136,335 \end{tabular} . - 105,163 [39,475] \begin{tabular}{|l|l|} \hline \\ \hline \\ \hline \\ \hline \\ \hline \\ \hline \\ \hline 1533,384) \\ \hline 1,554,616 \\ \hline 1,021,232 & 401(k)PPlan \\ \hline \end{tabular} Capital Needs Analvsis for Karen \begin{tabular}{|c|c|c|c|c|c|c|c|c|c|c|c|} \hline 44 & 2053 & 71 & 103,609 & - & & - & 103,609 & 140,425 & {[36,816]} & 1,554,616 & 401(k) Plan \\ \hline 45 & 2054 & 72 & 105,163 & - & - & - & 105,163 & 144,638 & {[39,475]} & 1,021,232 & \\ \hline 46 & 2055 & 73 & 106,741 & - & - & - & 106,741 & 148,977 & (42,237) & & \\ \hline 47 & 2056 & 74 & 108,342 & - & - & - & 108,342 & 153,447 & (45,105) & & \\ \hline 48 & 2057 & 75 & 109,967 & - & - & - & 109,967 & 158,050 & {[48,083]} & & \\ \hline 49 & 2058 & 76 & 111,617 & - & - & - & 111,617 & 162,792 & (51,175) & & \\ \hline 50 & 2059 & 77 & 113,291 & - & - & - & 113,291 & 150,908 & (37,617) & & \\ \hline 51 & 2060 & 78 & 114,990 & - & - & - & 114,990 & 155,435 & (40,445) & & \\ \hline 52 & 2061 & 79 & 116,715 & - & - & - & 116,715 & 160,098 & (43,383] & & \\ \hline 53 & 2062 & 80 & 118,466 & - & - & - & 118,466 & 164,901 & (46,435) & & \\ \hline 54 & 2063 & 81 & 120,243 & - & - & - & 120,243 & 169,848 & (49,605) & & \\ \hline 55 & 2064 & 82 & 122,046 & - & - & - & 122,046 & 157,449 & (35,403) & & \\ \hline 56 & 2065 & 83 & 123,877 & - & - & - & 123,877 & 162,173 & {[38,296]} & & \\ \hline 57 & 2066 & 84 & 125,735 & - & - & - & 125,735 & 167,038 & (41,303) & & \\ \hline 58 & 2067 & 85 & 127,621 & - & - & - & 127,621 & 172,049 & {[44,428]} & & \\ \hline 59 & 2068 & 86 & 129,536 & - & - & - & 129,536 & 177,210 & (47,675) & & \\ \hline 60 & 2069 & 87 & 131,479 & - & - & - & 131,479 & 164,274 & (32,796) & & \\ \hline 61 & 2070 & 88 & 133,451 & - & - & - & 133,451 & 169,202 & {[35,752]} & & \\ \hline 62 & 2071 & 89 & 135,452 & - & - & - & 135,452 & 174,278 & {[38,826]} & & \\ \hline 63 & 2072 & 90 & 137,484 & - & - & - & 137,484 & 179,507 & (42,022) & & \\ \hline 64 & 2073 & 91 & 139,547 & - & - & - & 139,547 & 184,892 & (45,345) & & \\ \hline 65 & 2074 & 92 & 141,640 & - & - & - & 141,640 & 171,395 & (29,755) & & \\ \hline 66 & 2075 & 93 & 143,764 & - & - & - & 143,764 & 176,537 & (32,772) & & \\ \hline 67 & 2076 & 94 & 145,921 & - & - & - & 145,921 & 181,833 & (35,912) & & \\ \hline 68 & 2077 & 95 & 148,110 & - & - & - & 148,110 & 187,288 & (39,178) & & \\ \hline 6 & & & & & & & & & & & \\ \hline \end{tabular}