Question

DO NOT SOLVE USING EXCEL.

You are doing some long-range retirement planning. On the day you retire (23 years from now) you want to be able to withdraw $200,000. Then, you want to withdraw the following amounts at the end of each year after that (during your retirement period). Years 1-4 $160,000 Years 5-9 $175,000 Years 10-15 $165,000 Years 16-26 $145,000 At the end of the 26th year in retirement, you’d like to have $500,000 remaining in your retirement account available for withdraw. During your retirement years, you anticipate earning a 4.5% rate of return. You currently have $275,000 that you are going to use to start your retirement savings today. In addition, you plan to save $700 at the end of each month for the next 8 years. At that point (8 years from today) you will add another $150,000 to your retirement fund. Then, over the remaining 15 years, how much must you save at the end of each month to reach your goal if you earn 8.9% as a rate of return during the first 8 years and 7.6% over the final 15 years in which you are saving for retirement?

Answer 1

0	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	26
200000	160000	160000	160000	160000	175000	175000	175000	175000	175000	165000	165000	165000	165000	165000	165000	145000	145000	145000	145000	145000	145000	145000	145000	145000	145000	145000	500000
	4.5%	4.5%	4.5%	4.5%	4.5%	4.5%	4.5%	4.5%	4.5%	4.5%	4.5%	4.5%	4.5%	4.5%	4.5%	4.5%	4.5%	4.5%	4.5%	4.5%	4.5%	4.5%	4.5%	4.5%	4.5%	4.5%	4.5%
present value afetr 23 years	153110.45	146516.79	140207.46	134169.81	140428.93	134381.75	125594.98	123057.40	117758.27	106248.07	101672.79	97294.54	93104.82	89095.52	85258.87	71698.05	68610.58	65656.05	62828.76	60123.21	57534.18	55056.63	52685.77	50417	48245.94	46168.36	159201.24
total	2589125.84

After 23 years, he needs = 200000 + 2589125.24 = 2789125.24

Present value	0	0	150000	275000	275000
rate	8.9%	7.6%	7.6%	8.9%	7.6%
nper	8	15	15	8	15
frequency	12	12	0	0	0
payment of month	700	700	0	0	0
future value	97469.79	233823.76	450065.09	543945.93	825119.23

Total value at the end of 23 years = 97469.79 + 233823.76 + 450065.09 + 543945.93 + 825119.23

= 2150423.91

Amount required to fulfill his goal = 2789125.84 - 2150423.91 = 638701.93

Future value	638701.93
rate	7.6%
nper	15
frequency	12
present value	0
payment of month	5957.20

He must save 5957.20 at the end of each month to reach his goal over the 15 years