Question

Please do not use excel for solving this. Thanks

3. Nancy is 30 years old, and she currently has $24,000 in savings. She plans to work until she's 66, and expects to live until she's 87. She is currently working at her first job, which allows her to save $175 per month. In five years' time, she will take a vacation costing $15,000, and in fifteen years' time, buy a cabin in cash for $57,000. When retired, Nancy would like to withdraw funds every month to support her living expenses, and upon her death, she would like to leave an inheritance of $250,000. Nancy expects that she can borrow and lend at 7% prior to retirement, and at 3% after retirement, with both rates compounded annually. How much can she withdraw every two weeks during retirement?

Answer 1

You need to go step by step.

First calculate monthly retrun which is 7% /12 = 0.005833 and time period till 35= 5*12=60

FV at 35 is of two components 1) FV of 24000 and FV of 175 monhtly saving invested at 7% montly rate

FV of 24000 = 24000*(1+0.005833)^60=34023.0062 and

FV of 175 monthly saving = PMT /i *( 1+i)^n -1) = (175/0.005833)*((1+0.005833)^60 -1) = 12528.7578

FV at 35 = 46551.764

Cash outflow of 15000 at age of 35. So net FV at 35 =  46551.764 - 15000 = 31551.764

First calculate monthly retrun which is 7% /12 = 0.005833 and time period till 45= 10*12=120

FV at 45 is of two components 1) FV of 31551.764 and FV of 175 monhtly saving invested at 7% montly rate

FV of 31551.764 = 31551.764*(1+0.005833)^120= 63408.3615 and

FV of 175 monthly saving = PMT /i *( 1+i)^n -1) = (175/0.005833)*((1+0.005833)^120 -1) = 30289.8413

FV at 45 = 93698.2028

Cash outflow of 7000 at age of 45. So net FV at 45 =  86698.2028

Similarly FV at age of retirement will be FV of 86698.2028 and 175 monthly saving

FV at 66 = 475384.861

Now you have 21 years. You have PV ( at age 66) = 475384.861 and FV (at 87) of 250000. Your rate is 3% for next 21 years. You need to find annuity outflow for every two weeks.

You have 365/7 weeks in a year. Divide this by 2 to get biweekly rate = (365/7)/2=0.001151

Your time period = (87-66)*(365/7)= 1095

So annuity will be 649.82 using PMT in excel. Annuity = PMT ( 0.001151, 1095,475384.861, 250000)