Question

Assume you want to retire in 35 years and save a $15,000 at the end of the first year. Assume you will earn 7% annually on your investment and you expect 2% inflation before retirement. After you retired, you expect to live for another 30 years. Assume you can earn a nominal annual rate of 4% and you expect inflation to be 3% during retirement. a) Calculate how much money you are accumulating by the time you retire if you increase your annual savings by the inflation rate. b) Calculate your real annual withdrawals over the next 30 years so as to maintain a constant standard of living. Assume the first withdrawal is made at the beginning of the year.

Please do this in excel and show the cell references

Answer 1

x fc =K1-($L$1*2) C D E F G H I & 898828 S 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 N J K L 2E+06 71000 1 2E+062E+061 2E+06 2E+06 3 2E+06 2E+06 4 2E+06 2E+06 5 2E+06 2E+06 6 2E+06 2E+06 7 2E+06 2E+06 8 2E+06 2E+06 9 2E+06 2E+06 10 3E+06 2E+06 11 3E+06 2E+06 12 3E+06 2E+06 13 3E+06 2E+06 14 4E+06 2E+06 15 SE+06 3E+06 16 8E+06 4E+06 17 1E+07 7E+06 18 2E+07 1E+07 19 SE+07 2E+07 20 1E+08 SE+07 21 2E+08 1E+08 22 SE+08 2E+08 23 1E+09 SE+08 24 3E+09 1E+09 25 9E+09 3E+09 26 2E+10 9E+09 27 7E+10 2E+10 28 2E+11 7E+10 29 7E+11 2E+11 30 2E+12 7E+11 15000 149672 15300 139880 15606 130729 15918 122177 16236 114184 16561 106714 16892 99733 17230 93208 17575 87110 17926 81411 18285 76086 18651 71108 19024 66456 19404 62108 19792 58045 20188 54248 20592 50699 21004 47382 21424 44282 21852 41385 22289 38678 22735 36148 23190 33783 23653 31573 24127 29507 24609 27577 25101 25773 25603 24087 26115 22511 26638 21038 27170 19662 27714 18376 28268 17174 28833 16050 29410 15000 2E+06 NWAGE

As shown in the above table, the 2073553.175 (in the picture it is 2E+06, which after adjustinng the column width gives the number before the bracket)

The annual withdrawals can be 71000 per year as calculated in the next table in the image. The number 71000 has been calculated by trial and error method. if you make the 71000, 72000 the results show negative cashflows hence 71000 is taken.