Need help with formulas

Assumptions Inflation Raise 0.03 0.04 Returns Savings 0.07 0.099 Retirement 0.7 250,000 Low Tax (150K) 0.1 0.17 0.25 Jan 1 of Annual Disposable FV of Annual Year I'm Gross Income Taxes Net Income Savings Income Savings @ Savings = 10% @ Savings -15% 21 Total FV of savings when I'm 68 Total FV of savings when I'm 68 22 57000 9690 47310 4683.69 42626.31 105254.8014 $3,854,931.99 $5,782,397.98 23 58710 9980.7 48729.3 4824.201 43905.0993 101320.0425 Total PV of retirement spending when I'm Total PV of retirement spending when I'm 68 24 62890.152 10691.33 52198.8262 5167.684 47031.1424 101433.6724 $3,795,224.70 $3,795,224.70 25 64776.85656 11012.07 53764.7909 5322.714 48442.0766 97641.75944 Difference (Bequeathment) Difference (Bequeathment) 26 66720.16226 11342.43 55377.7347 5482.396 49895.3389 93991.60021 $59,707.29 $1,987, 173.29 27 71470.63781 12150.01 59320.6294 5872.742 53447.8871 94097.01135 28 73614.75694 12514.51 61100.2483 6048.925 55051.3237 90579.36607 @ Savings = 9.9% @ Savings = 20% @ % 29 75823.19965 12889.94 62933.2557 6230.392 56702.8634 87193.22154 Total FV of savings when I'm 68 Total FV of savings when I'm 68 30 81221.81147 13807.71 67414.1035 6673.996 60740.1073 87291.00833 $3,816,382.67 $7,709,863.97 31 83658.46581 14221.94 69436.5266 6874.216 62562.3105 84027.79307 Total PV of retirement spending when I'm Total PV of retirement spending when I'm 68 32 MBA (0) $3,795,224.70 $3,795,224.70 33 MBA (0) Difference (Bequeathment) Difference (Bequeathment) 34 167316.9316 41829.23 125487.699 12423.28 113064.417 123960.92 $21.157.97 $3,914,639.28 35 172336.4396 43084.11 129252.33 12795.98 116456.349 119326.8669 Total PV Disposable Income 36 184606.7941 46151.7 138455.096 13707.05 124748.041 119460.6915 $1,545, 566.65 37 190144.9979 47536.25 142608.748 14118.27 128490.482 114994.8712 38 195849.3478 48962.34 146887.011 14541.81 132345.197 110695.9975 39 209793.8214 52448.46 157345.366 15577.19 141768.175 110820.1426 40 216087.636 54021.91 162065.727 16044.51 146021.22 106677.3335 41 222570.2651 55642.57 166927.699 16525.84 150401.857 102689.3958 42 238417.268 59604.32 178812.951 17702.48 161110.469 102804.5615 43 245569.786 61392.45 184177.34 18233.56 165943.783 98961.40031 44 252936.8796 63234.22 189702.66 18780.56 170922.096 95261.90871 45 270945 9854 67736.5 203209.489 20117.74 183091.75 95368.74449 46 279074.365 69768.59 209305.774 20721.27 188584.502 91803.55778 47 287446.596 71861.65 215584.947 21342.91 194242.037 88371.64908 48 307912.7936 76978.2 230934.595 22862.52 208072.07 88470.75747 49317150.1774 79287.54 237862.633 23548.4 214314.232 85163.43943 50 326664.6827 81666.17 244998.512 24254.85 220743.659 81979.75946 51 349923.2081 87480.8 262442.406 25981.8 236460.608 82071.69937 52 360420.9044 90105.23 270315.678 26761.25 243554.426 79003.59846 53 371233.5315 92808.38 278425.149 27564.09 250861.059 76050.19291 54 397665.3589 99416.34 298249.019 29526.65 268722.366 76135.48285 55 409595.3197 102398.8 307196.49 30412.45 276784,037 73289.29657 56 421883.1793 105470.8 316412.384 31324.83 285087.558 70549.50979 57 451921.2617 112980.3 338940.946 33555.15 305385.793 70628.63073 58 465478.8995 116369.7 349109.175 34561.81 314547.366 67988. 30809 59 479443.2665 119860.8 359582.45 35598.66 323983.787 65446.68909 60 513579.6271 128394.9 385184.72 38133.29 347051.433 65520.08725 61 528987.0159 132246.8 396740.26239277.29 357462.976 63070.73819 62 544856.6264 136214.2 408642.47 40455.6 368186.865 60712.95359 63 583650.4182 145912.6 437737.814 43336.04 394401.77 60781.04288 64 601159.9307 150290 450869.948 44636.12 406233.823 58508.85436 65 619194.7286 154798.7 464396.046 45975.21 418420.838 56321.60747 66 663281.3933 165820.3 497461.045 49248.64 448212.402 56384.77189 Retirement PV (at 68) of 67 683179.8351 170795 512384.876 50726.1 461658.774 54276.92995 Spending Annual Spending 68 Retire (0) 250000 250000 69 257500 240654.2056 70 265225 231657.7867 71 273181.75 222997.6825 72 281377.2025 214661.3206 73 289818.5186 206636.5983 74 298513.0741 198911.8657 75 307468.4664 191475.9081 76 316692.5203 184317.9302 77 326193.296 177427.5403 78 335979.0948 170794.735 79 346058.4677 164409.8851 80 356440.2217 158263.7212 81 367133.4284 152347.3204 82 378147.4312 146652.0935 83 389491.8542 141169.7722 84 401176.6098 135892.3976 85 413211.9081 130812.3079 86 425608.2653 125922. 1282 87 438376.5133 121214,7589 88 451527.8087 116683.3661 89 465073.6429 112321.3711 90 Die 0 0 A suggested game plan: Step 1: Figure out what you'll make each year until you retire. Step 2: Figure out how much money you'll need through retirement each year. Step 3: Discount the retirement spending to January 1 of the year you turn 68 and sum the values to find the value of money you'll need in your account at retirement. Step 4: Estimate an annual savings rate and calculate how much you're saving each year. Step 5: Compound forward your annual savings to January 1 of the year you turn 68 and sum the values to find the value of money you'll have in your account at retirement. Step 6: Calculate the difference in values of Step 5 and Step 3. Step 7: Adjust your annual savings rate such that you have at least enough money to cover your retirement. You are done when you have a bequeathment (i.e., inheritance) that's at least zero. Some suggested simplifying assumptions: The annual inflation rate is 3% for the rest of your life. The average return on your investments will be 7%. You will plan to retire on December 31st of the calendar year you turn 67. You have no retirement savings currently. You plan to live you are 90 years old; i.e., you will pass away December 31 of the year you turn 90. . HE . Intra Your retirement spending, beginning January 1 of the year you turn 68, will be $250,000 or 70% of your age 67 salary, whichever is lower. This spending will increase each year at the cost of living fi.e., inflation). (That's actually not an unreasonable figure-many retirees take up new hobbies or travel to fill up their time and consider that a nursing home costs $61,500 per year today-after Inflation over 40+ years-could come out to $200,000 per year when you retire!) This savings plan will begin on January 1, 2014. Any savings you make will be done on December 31 of a given year, including on the day you plan to retire. These savings will probably increase each year to account for inflation. (Where does this cash come from? Let's suppose you stockpile it in your checking account all year and then make a single contribution to your plan at the end of the year. I'll acknowledge that this is extraordinarily simplistic, but convenient for our purposes.) All savings will be done after taxes are paid on income. . Your effective tax rate will depend on how much income you have. We'll simplify this a lot: o If you make less than $40,000 your effective tax rate is 10% If you make at least $40,000 and less than $150,000, your effective tax rate is 17% o if you make more than $150,000, your effective tax rate is 25%. Dyd The article w - - Uw Ta ww VIENMAR BALN 2 www . olen 24 an. MMMM. SA M. SA... 354 ww www.wwww Swan 2 CALORES MAM W logo CHORA DE WY WIFI FILI VE ww0WII PENYEWE SMCT chini SEN . WIU W 19 WAIT IT PUTU MEIL ON THE WWW.PIE MUT IM IPA CAMBIO CAMERE CREATE BET T. W TYMU weet FPT Tes RUMCA IN TO BIOS MULT.W wru L M M HALLOT ERR WWW W KORTH LESS - NUME WAM WA SHULLAM SIA X X X X 224www.www.MWL . ' ANWAS SO MUY W 2015 ER SHARE Www PCE W GD wYO YA MSX Sony W. en 9... 2. W JAR.IS WHY MINER Sew Y SIKKE ER HET WHERE WE WWW HEUW LAN AMD WIR LWY 20. - YAMA WMS ABAIN ME www.va IN MAW - www WWW OEM'AWYTI I * MA LA AWAMBAR XUXLIS. PRIME WrIY VOSI A VOLT. Lees : We also need to make some assumptions about your career path. 78 7 : LAW TWI SOS TIR CAN SVET 1 21 . 21 The current average starting salary for a business major is $57,000 per year. If you start a job in a . a the year you turn 22, we'll assume that you get paid a full year salary on January 1 of the year you turn 22. . You will get a cost of living raise every year (l.e. at the rate of Inflation). . Every three years, you'll get a 4% raise for good performance on the job (or you move to a better paying job, etc.) on top of your cost of living increase. If you'd like to add a layer of complexity, you can get a 2-year MBA, but it will remove two years from your earnings. (We'll assume that you can get a full-tultion scholarship and stipend so it won't cost anything other than the opportunity cost of working o If you go straight from college to graduate school without any significant prior work experience, your starting salary will be $68,000. With fewer than four years of work experience, your salary will increase by 1.5 times your pre-MBA salary. If you wait until you have at least four years of work experience before going for your MBA, your salary after eaming a masters' will be 2 times your pre-MBA salary. TH 22 12 w A AL WELLA w WWE WWW 1917 VW KT w 1190871 Assumptions Inflation Raise 0.03 0.04 Returns Savings 0.07 0.099 Retirement 0.7 250,000 Low Tax (150K) 0.1 0.17 0.25 Jan 1 of Annual Disposable FV of Annual Year I'm Gross Income Taxes Net Income Savings Income Savings @ Savings = 10% @ Savings -15% 21 Total FV of savings when I'm 68 Total FV of savings when I'm 68 22 57000 9690 47310 4683.69 42626.31 105254.8014 $3,854,931.99 $5,782,397.98 23 58710 9980.7 48729.3 4824.201 43905.0993 101320.0425 Total PV of retirement spending when I'm Total PV of retirement spending when I'm 68 24 62890.152 10691.33 52198.8262 5167.684 47031.1424 101433.6724 $3,795,224.70 $3,795,224.70 25 64776.85656 11012.07 53764.7909 5322.714 48442.0766 97641.75944 Difference (Bequeathment) Difference (Bequeathment) 26 66720.16226 11342.43 55377.7347 5482.396 49895.3389 93991.60021 $59,707.29 $1,987, 173.29 27 71470.63781 12150.01 59320.6294 5872.742 53447.8871 94097.01135 28 73614.75694 12514.51 61100.2483 6048.925 55051.3237 90579.36607 @ Savings = 9.9% @ Savings = 20% @ % 29 75823.19965 12889.94 62933.2557 6230.392 56702.8634 87193.22154 Total FV of savings when I'm 68 Total FV of savings when I'm 68 30 81221.81147 13807.71 67414.1035 6673.996 60740.1073 87291.00833 $3,816,382.67 $7,709,863.97 31 83658.46581 14221.94 69436.5266 6874.216 62562.3105 84027.79307 Total PV of retirement spending when I'm Total PV of retirement spending when I'm 68 32 MBA (0) $3,795,224.70 $3,795,224.70 33 MBA (0) Difference (Bequeathment) Difference (Bequeathment) 34 167316.9316 41829.23 125487.699 12423.28 113064.417 123960.92 $21.157.97 $3,914,639.28 35 172336.4396 43084.11 129252.33 12795.98 116456.349 119326.8669 Total PV Disposable Income 36 184606.7941 46151.7 138455.096 13707.05 124748.041 119460.6915 $1,545, 566.65 37 190144.9979 47536.25 142608.748 14118.27 128490.482 114994.8712 38 195849.3478 48962.34 146887.011 14541.81 132345.197 110695.9975 39 209793.8214 52448.46 157345.366 15577.19 141768.175 110820.1426 40 216087.636 54021.91 162065.727 16044.51 146021.22 106677.3335 41 222570.2651 55642.57 166927.699 16525.84 150401.857 102689.3958 42 238417.268 59604.32 178812.951 17702.48 161110.469 102804.5615 43 245569.786 61392.45 184177.34 18233.56 165943.783 98961.40031 44 252936.8796 63234.22 189702.66 18780.56 170922.096 95261.90871 45 270945 9854 67736.5 203209.489 20117.74 183091.75 95368.74449 46 279074.365 69768.59 209305.774 20721.27 188584.502 91803.55778 47 287446.596 71861.65 215584.947 21342.91 194242.037 88371.64908 48 307912.7936 76978.2 230934.595 22862.52 208072.07 88470.75747 49317150.1774 79287.54 237862.633 23548.4 214314.232 85163.43943 50 326664.6827 81666.17 244998.512 24254.85 220743.659 81979.75946 51 349923.2081 87480.8 262442.406 25981.8 236460.608 82071.69937 52 360420.9044 90105.23 270315.678 26761.25 243554.426 79003.59846 53 371233.5315 92808.38 278425.149 27564.09 250861.059 76050.19291 54 397665.3589 99416.34 298249.019 29526.65 268722.366 76135.48285 55 409595.3197 102398.8 307196.49 30412.45 276784,037 73289.29657 56 421883.1793 105470.8 316412.384 31324.83 285087.558 70549.50979 57 451921.2617 112980.3 338940.946 33555.15 305385.793 70628.63073 58 465478.8995 116369.7 349109.175 34561.81 314547.366 67988. 30809 59 479443.2665 119860.8 359582.45 35598.66 323983.787 65446.68909 60 513579.6271 128394.9 385184.72 38133.29 347051.433 65520.08725 61 528987.0159 132246.8 396740.26239277.29 357462.976 63070.73819 62 544856.6264 136214.2 408642.47 40455.6 368186.865 60712.95359 63 583650.4182 145912.6 437737.814 43336.04 394401.77 60781.04288 64 601159.9307 150290 450869.948 44636.12 406233.823 58508.85436 65 619194.7286 154798.7 464396.046 45975.21 418420.838 56321.60747 66 663281.3933 165820.3 497461.045 49248.64 448212.402 56384.77189 Retirement PV (at 68) of 67 683179.8351 170795 512384.876 50726.1 461658.774 54276.92995 Spending Annual Spending 68 Retire (0) 250000 250000 69 257500 240654.2056 70 265225 231657.7867 71 273181.75 222997.6825 72 281377.2025 214661.3206 73 289818.5186 206636.5983 74 298513.0741 198911.8657 75 307468.4664 191475.9081 76 316692.5203 184317.9302 77 326193.296 177427.5403 78 335979.0948 170794.735 79 346058.4677 164409.8851 80 356440.2217 158263.7212 81 367133.4284 152347.3204 82 378147.4312 146652.0935 83 389491.8542 141169.7722 84 401176.6098 135892.3976 85 413211.9081 130812.3079 86 425608.2653 125922. 1282 87 438376.5133 121214,7589 88 451527.8087 116683.3661 89 465073.6429 112321.3711 90 Die 0 0 A suggested game plan: Step 1: Figure out what you'll make each year until you retire. Step 2: Figure out how much money you'll need through retirement each year. Step 3: Discount the retirement spending to January 1 of the year you turn 68 and sum the values to find the value of money you'll need in your account at retirement. Step 4: Estimate an annual savings rate and calculate how much you're saving each year. Step 5: Compound forward your annual savings to January 1 of the year you turn 68 and sum the values to find the value of money you'll have in your account at retirement. Step 6: Calculate the difference in values of Step 5 and Step 3. Step 7: Adjust your annual savings rate such that you have at least enough money to cover your retirement. You are done when you have a bequeathment (i.e., inheritance) that's at least zero. Some suggested simplifying assumptions: The annual inflation rate is 3% for the rest of your life. The average return on your investments will be 7%. You will plan to retire on December 31st of the calendar year you turn 67. You have no retirement savings currently. You plan to live you are 90 years old; i.e., you will pass away December 31 of the year you turn 90. . HE . Intra Your retirement spending, beginning January 1 of the year you turn 68, will be $250,000 or 70% of your age 67 salary, whichever is lower. This spending will increase each year at the cost of living fi.e., inflation). (That's actually not an unreasonable figure-many retirees take up new hobbies or travel to fill up their time and consider that a nursing home costs $61,500 per year today-after Inflation over 40+ years-could come out to $200,000 per year when you retire!) This savings plan will begin on January 1, 2014. Any savings you make will be done on December 31 of a given year, including on the day you plan to retire. These savings will probably increase each year to account for inflation. (Where does this cash come from? Let's suppose you stockpile it in your checking account all year and then make a single contribution to your plan at the end of the year. I'll acknowledge that this is extraordinarily simplistic, but convenient for our purposes.) All savings will be done after taxes are paid on income. . Your effective tax rate will depend on how much income you have. We'll simplify this a lot: o If you make less than $40,000 your effective tax rate is 10% If you make at least $40,000 and less than $150,000, your effective tax rate is 17% o if you make more than $150,000, your effective tax rate is 25%. Dyd The article w - - Uw Ta ww VIENMAR BALN 2 www . olen 24 an. MMMM. SA M. SA... 354 ww www.wwww Swan 2 CALORES MAM W logo CHORA DE WY WIFI FILI VE ww0WII PENYEWE SMCT chini SEN . WIU W 19 WAIT IT PUTU MEIL ON THE WWW.PIE MUT IM IPA CAMBIO CAMERE CREATE BET T. W TYMU weet FPT Tes RUMCA IN TO BIOS MULT.W wru L M M HALLOT ERR WWW W KORTH LESS - NUME WAM WA SHULLAM SIA X X X X 224www.www.MWL . ' ANWAS SO MUY W 2015 ER SHARE Www PCE W GD wYO YA MSX Sony W. en 9... 2. W JAR.IS WHY MINER Sew Y SIKKE ER HET WHERE WE WWW HEUW LAN AMD WIR LWY 20. - YAMA WMS ABAIN ME www.va IN MAW - www WWW OEM'AWYTI I * MA LA AWAMBAR XUXLIS. PRIME WrIY VOSI A VOLT. Lees : We also need to make some assumptions about your career path. 78 7 : LAW TWI SOS TIR CAN SVET 1 21 . 21 The current average starting salary for a business major is $57,000 per year. If you start a job in a . a the year you turn 22, we'll assume that you get paid a full year salary on January 1 of the year you turn 22. . You will get a cost of living raise every year (l.e. at the rate of Inflation). . Every three years, you'll get a 4% raise for good performance on the job (or you move to a better paying job, etc.) on top of your cost of living increase. If you'd like to add a layer of complexity, you can get a 2-year MBA, but it will remove two years from your earnings. (We'll assume that you can get a full-tultion scholarship and stipend so it won't cost anything other than the opportunity cost of working o If you go straight from college to graduate school without any significant prior work experience, your starting salary will be $68,000. With fewer than four years of work experience, your salary will increase by 1.5 times your pre-MBA salary. If you wait until you have at least four years of work experience before going for your MBA, your salary after eaming a masters' will be 2 times your pre-MBA salary. TH 22 12 w A AL WELLA w WWE WWW 1917 VW KT w 1190871