Question

You realize the wisdom of starting early at age 22 in saving for your retirement and plan on making 43 equal end of the year annual deposits in an IRA account in hopes of having at least 1,000,000 once you retire at age 65 (immediately after your last deposit into the IRA account) but you think it would be best to have $1,750,000 at age 65 to retire. Answer the following questions based on this information. 1. What rate compounded annually would you need to earn in order to have 2. What annual deposit would you need in order to accumulate $1,750,000 upon 3 Now you decide you might need more than $1,750,000 in your IRA once you turn 65 $1,750,000 upon retirement if your annual deposit is $4000? retirement if your IRA earns 7.9% compounded annually? given the uncertainty of Social Security. How much money would you have in your IRA at age 65 after 43 end of the year deposits of $6,000 invested at an expected annual return of 7.9%? Let's now assume you carry out the plan described in the last question (#3) and expect to continue to earn an 7.9% annual return on your IRA account after retirement. Once you reach retirement at age 65, you decide you will live exactly 25 more years. At age 65, you want two things from your retirement fund: $250,000 to leave to your grandkids upon your death in 25 years and a 25-year annuity-due (25 equal annual withdrawals beginning immediately upon retirement). How large can 4. your annual retirement annuity be while still leaving $250,000 to your grandkids upon your death at age 90 if your IRA account continues to earn 7.9% annually? In looking back at your answer #4, you wonder if this retirement annuity will be enough to live on and you worry about potential loss of purchasing power after retirement. Let's forget about leaving money to the grandkids, they never visit anyway, and make some different retirement income assumptions. Assume you want your first retirement withdrawal, taken immediately upon retirement, to be $150,000 at age 65. Also, let's assume you want each of the remaining (24) withdrawals to increase by 3% annually. Assume your retirement account will still earn an 7.9% expected annual return. 5. a How much will you need in your retirement account at age 65 to fund this desired retirement income stream? b) How much will you have to deposit annually at the end of each year into your account over the next 43 years to fund this desired retirement stream? Your results from question 5 (the last question) have you somewhat concerned. Re do question 5 assuming you'll wait until age 67 to retire and you will live 23 years after retirement. Assume you want your first retirement withdrawal, taken immediately upon retirement, to be $160,000 at age 67. Also, let's assume you want each of the remaining (22) withdrawals to increase by 3% annually. Assume your retirement account will still earn an 7.9% expected annual return. 6. a How much will you need in your retirement account at age 67 to fund this desired retirement income stream? How much will you have to deposit annually at the end of each year into your account over the next 45 years to fund this desired retirement stream? b)

PLEASE SHOW HOW YOU WOULD SOLVE USING EXCEL SOFTWARE

Answer 1

I can only answer 1 question at a time so I am solving question 1.

1. This is a simple future value of an annuity question. The formula used in excel is shown in the below image.

Please do rate me and mention doubts, if any, in the comments section..

Answer 2

The following are the obtained results in spreadsheet:

Current age Retirement age Number of years Retirement fund Desired Retirement fund ears years NPER FV $1,000,000 $1,750,000 6 Solution 1. Required rate of return compounded annuallv NPER Number of years Desired Retirement fund Annual deposit Required rate of return $1,750,000 $4,000 8.97% PMT RATE Solution 2. Annual deposit to accumulate $1.750.000 if rate is 7.9% NPER Number of years Desired Retirement fund Required rate of return Annual deposit $1,750,000 7.90% $5,464.87 15 RATE 17 PMT 18 Solution 3. Accumulated fund at 65 if annual deposits of $6,000 Number of years Required rate of return Annual deposit Accumulated fund 43 7.90% $6.000 $1,921,364.33 NPER RATE PMT 20 23 Solution 4. Annual withdrawals after retirement 25 26 27 Retirement age Death Number of years Retirement fund Annual rate Money for grandkids Annual withdrawals after retirement (Annuity Due Beginning of the year) 25 $1,921,364.33 7.90% $250,000 $162,174.33 ear vear NPER PV RATE 30 31 32 PMT Solution 5 (a). Present value of growing annuity (with inflation) 34 35 36 37 38 39 Retirement age Death Number of years Annual rate Money for grandkids (No money left) year ear NPER 90 7.90% $0 FV

First withdrawal(Annuity Due Beginning of the vear) Inflation (Growth rate) Real rate of return (1+Nominal rate)/(1+Inflation)-1 The money needed at retirement for desired income stream $150,000 3% PMT 41 42 43 RATE $2,269,559.69 PV 45 Solution 5 (b). Annual deposite to accumulate desired amount Retirement age Current age Number of years Annual rate Inflation (Growth rate) Real rate of return (1+Nominal rate)/(1+Inflation)-1 The money needed at retirement for desired income stream Annual deposites required (with inflation) Annual deposites required (without inflation) 46 65 ear vear NPER 48 49 50 Implied that inflation need 7.90% 3% 4.76% $2,269,559.69 $16,929.18 $7,087.34 to be adjust here as well RATE FV PMT PMT 54 Solution 6 (a). Present value of growing annuity (with inflation) Retirement age Death Number of years Annual rate Money for grandkids (No money left) First withdrawal(Annuity DueBeginning of the year) Inflation (Growth rate) Real rate of return (1+Nominal rate)/(1+Inflation)-1 The money needed at retirement for desired income stream 67 90 23 7.90% $0 $ 160.000 3% 4.76% $2,313,480.05 ear eaf NPER 58 60 FV PMT 63 RATE PV 65 Solution 6 (b). Annual deposite to accumulate desired amount 67 68 69 70 Retirement age 67 ear ear NPER Number of years Annual rate Inflation (Growth rate Real rate of return (1+Nominal rate) (1+Inflation)-1 The monev needed at retirement for desired income stream Annual deposites required (with inflation) Annual deposites required (without inflation) Implied that inflation need 7.90% 3% 4.76% $2,313,480.05 $15,509.18 $6,170.91 to be adjust here as wel RATE FV PMT PMT 72

The following are the data inputs in spreadsheet:

Current age Retirement age Number of years Retirement fund Desired Retirement fund years years NPER 65 B2-B1 1000000 1750000 4 Solution 1. Required rate of return compounded annually Number of years Desired Retirement fund Annual deposit Required rate of return B3 B5 NPER FV PMT RATE RATE(B8,-B10,0,B9,0 13 Solution 2. Annual deposit to accumulate $1,750,000 if rate is 7.9% Number of years Desired Retirement fund Required rate of return Annual deposit B3 B5 0.079 NPER FV RATE PMT 15 17 PMT(B16,B14,0,B15 Solution 3. Accumulated fund at 65 if annual deposits of $6,000 19 20 21 Number of years Required rate of return Annual deposit Accumulated fund B3 0.079 NPER RATE PMT FV FV(B21,B20,-B22,0,0 25 Solution 4. Annual withdrawals after retirement Retirement age Death Number of years Retirement fund Annual rate Money for grandkids 65 year year NPER PV RATE FV PMT 27 B27-B26 --B23 0.079 250000 30 32 Anual withdrawals after retirement (Annuity DueBeginning of the year PMT(B30,B28,B29,B31,1 Solution 5 (a). Present value of growing annuity (with inflation) 34 35 36 37 38 39 Retirement age Death Number of years Annual rate Money for grandkids (No money left) ear vear NPER B36-B35 0.079

First withdrawal(Annuity Due Beginnin 150000 0.03 40 of the vear PMT Inflation (Growth rate Real rate of return (1+Nominal rate) (1+Inflation)-1 The money needed at retirement for desired income stream (1+B38) (1+B41)-1 RATE PV PV(B42,B37,B40,0,1) 45 Solution 5 (b). Annual deposite to accumulate desired amount Retirement age Current age Number of years Annual rate Inflation (Growth rate 46 ear eaf NPER Implied that inflation need to be adjust here as well RATE FV PMT PMT 48 49 50 51 B46-B47 0.079 0.03 Real rate of return (1+Nominal rate) (1+Inflation)-1 (1+B49) (1+B50)-1 B43 The money needed at retirement for desired income stream Annual deposites required (with inflation) Annual deposites required (without inflation) PMT(B51,B48,0,B52,0 PMT(B49,B48,0,B52,0) Solution 6 (a). Present value of growing annuity (with inflation) Retirement age Death Number of years Annual rate Money for grandkids (No money left) First withdrawal(Annuity Due Beginning of the year) Inflation (Growth rate) Real rate of return (1+Nominal rate) (1+Inflation)-1 The money needed at retirement for desired income stream year 90 ear B58-B57 0.079 60 FV PMT 160000 0.03 62 63 1+B60) (1+B63)-1 PV(B64,B59,B62,0,1 RATE Solution 6 (b). Annual deposite to accumulate desired amount Retirement age Current age Number of years Annual rate Inflation (Growth rate) Real rate of return (1+Nominal rate) (1+Inflation)-1 The money needed at retirement for desired income stream Annual deposites required (with inflation) Annual deposites required (without inflation) 68 69 70 ear eaf NPER B68-B69 0.079 0.03 (1+B71(1+B72)-1 Implied that inflation need to be adjust here as wel RATE FV PMT PMT B65 PMT(B73,B70,0,B74,0 PMT(B71,B70,0,B74,0)