Question

Question 5 1 pts Your clients want help figuring out how much they need to save each month so that they can reach their retirement goal. If your clients are currently 40 years old and want to retire when they are age 68, how much do they need to save at the end of each month to reach their goal? They currently have $190,000 saved for retirement. Assume the following: • Your clients currently spend $100,000 (in today's dollars), and they want to spend 90% of that in retirement • General inflation is about 2.9% per year • Your clients can earn an annual rate of return of 7.6% on their investments • Your clients are both the same age and want to retire at the same age You estimate that your clients' planning horizon should be until age 92 Your clients don't plan on leaving any money to their children when they die

Answer 1

Dhamendra worksheet.xisx Saved A Dharmendra Kumar. AutoSave 0n - Search A Dharmandra Beview File Page Layout Formulas View Help Foxit PDF Share Home Insert Data Comments AutoSu Cut 11A A Calibri Gencral X Wrap Text Fill Capy Conditional Tormat a Formatting Ideas Paste Cell Insert Delete Termat Sert & Tind & в IU A E Merge & Center 0 00 Clear Farmat Painter lable Styles Filter Select clipbcard Alignment Styles Cels Euitirg Ideas Font Number B4 н м В C D E EF G К N P R 1 4 Reirement goal planning: 6 Particulars Current age Retirement age Current savings Varlables UOM 7 40 Years 8 68 Years 190000 S 10 Life expectancy - 92 Years Inflation adiusted return- 11 Annual rate of return- 7.6 % 4.7 % 12 General Inflation 2.9 % Corpus left after death 13 0S Annual spending/p.a. 14 90000 S Inf Adj Corpus pre inf. Return After retirement spending without adjusting inflation effect 15 Post spending Inf Adj. Pre-retirement Post-retirement Saving/pa Spending/p Corpus 16 Closing value Closing value Age Aвe Corpus pre-inf. Retum 17 40 9811.501 199811.501 9391.141 219014.1425 69 1339051943 90000 1249051.943 58705.44 1307757 1307757.384 18 41 9811.501 219014.1425 10293.66 239119.3082 70 90000 1217757.384 57234.6 1274992 19 42 9811.501 239119.3082 11238.61 260169.4167 71 1274991.981 s0000 1184991.981 55694.62 1240687 20 go000 1150686.604 54082.27 1204769 43 9811.501 260159.4167 12227.96 282208.8803 72 1240686.604 21 44 9811.501 1204768.874 282208.8803 13263.82 305284.1987 3 90000 1114/68.874 52394.14 1167163 329444,057 22 45 9811.501 305284.1987 14348.36 74 1167163.011 90000 1077163.011 50626.66 1127790 23 46 9811.501 329444.057 15483.87 354739.4287 75 1127789.673 90000 1037789.673 48776.11 1086566 381223.6829 1086565.78B 24 47 9811.501 354739.4287 16672.75 76 s0000 996565.7876 46838.59 1043404 25 48 9811.501 381223.6829 17917.51 408952.697 77 1043404.38 go000 953404.3796 44810.01 998214.4 26 49 9811.501 408952.697 19220.78 437984.9747 78 998214.3855 g0000 908214.3855 42686.08 950900.5 27 9811.501 Forward rate of a bond 50 437984.9747 20585.29 468381.7695 79 950900.4616 90000 860900.4616 40462.32 901362.8 HASBRO IncRetirement goal planning WACC 100% 3:55 PM OHt Type here to search х ENG 11/22/2019 28

Dhamendra worksheet.xisx Saved A Dharmendra Kumar. AutoSave 0n A Dharmandra Search File Page Layout Formulas Review View Help Foxit PDF Share Home Insert Data Comments AutoSu Cut 11A A Calibri Wrap Text Gencral X Fill LCapy Conditional Tormat a Formatting Ideas Pasle Cell Insert Delete Termat Sert & ind & в IU A E Merge & Center 0 00 Farmat Painter Clear lable Styles Filter Select clipbcard Alignment Styles Cels Euitirg Ideas Fant Number B4 X м N A В G н P R 20 51 9811.501 468381.7695 22013.94 S00207.2137 80 901362.7833 90000 811362.7833 38134.05 849496.8 29 52 9811.501 500207.2137 23509.74 533528.4537 81 849496.8341 90000 759496.8341 35696.35 795193.2 90000 30 53 9811.501 533528.4537 25075.84 568415.7921 82 795193.1853 705193.1853 33144,08 738337.3 31 90000 648337.265 30471.85 678B09.1 54 9811.501 568415.7921 26715.54 604942.B353 дз 738337.265 32 678809.1165 55 9811.501 604942.8353 28432.31 643186.5496 84 so000 588809.1165 27674.03 616483.1 33 56 9811.501 643186.6496 30229.77 683227.9231 85 616483.1449 g0000 526483.1449 24744.71 S51227.9 34 57 9811.501 683227.9231 32111.71 725151.1365 86 551227.8527 90000 461227.8527 21677.71 482905.6 35 58 9811.501 725151.1365 34082.1 769044,7409 87 482905.5618 90000 392905.5618 18466.56 411372.1 36 59 9811.501 769014.7409 36145.1 815001.3447 88 411372.1232 s0000 321372.1232 15104,49 336176.6 37 336476.613 60 9811.501 815001.3447 38305.06 863117.9089 89 g0000 246476.613 11584.4 258061 38 61 9811.501 863117.9089 40566.54 913495.9516 90 258061.0138 g0000 168061.0138 7898.868 175959.9 39 62 9811.501 913495.9516 42934.31 966241.7623 91 175959.8815 90000 85959.88148 4040.114 90000 40 63 9811.501 966241.7623 45413.36 1021466,626 92 89999.99591 g0000 -0.004087339 -0,00019 -0.00428 or Zero 9811.501 1021466.626 48008.93 1079287.059 41 64 42 24 periods 65 9811.501 1079287.059 50726.49 1139825.051 Post retirement 43 9811.501 1139825.051 53571.78 1203208.33 44 67 9811.501 1203208.33 56550.79 1269570.622 1339051.943 45 68 9811.501 1269570.622 59669.82 46 47 29 periods Pre-retirement 40 Formulan: 49 F-P(1+rate ) nper + A*(((1+rate ) ^nper -1)/rate) FFV(rate , nper ,A,P) F FV(rate,nper, -A,-P) 50 51 52 53 CA HASBRO Inc WACC Forward rate of a bond Retirement goal planning 100% 3:55 PM х W OHt Type here to search ENG 11/22/2019 28
Based on above working solution and iflation adjusted constant return of 4.7% client is advised to save $9811.501 every year till the age of attaining retirement at 68 years.

After spending S90000/pa post retirement untill the age of 92 years client will be spending entire corpus leaving a zero balance for their children.