In: Finance
1st withdrawal (to be made after 26 years) = $150000*1.02^26 and all subsequent withdrawals will increase by 2% p.a.
value at retirement date of your retirement goals
= present value of 20 payments + present value of bequest amount
= (150000*1.02^26/1.07+150000*1.02^27/1.07^2+....+150000*1.02^45/1.07^20 )+ 1000000/1.07^20
=150000*1.02^26/1.07 * (1-(1.02/1.07)^20)/(1-1.02/1.07) + 1000000/1.07^20
= $3350910.52
Let a deposit of $3,500 per month is made for 25 years or 300 months (first deposit one month from now)
Monthly interest rate = 1.07^(1/12)-1 = 0.0056541
So, Future value of savings of $150000 + Future value of deposits
= 150000*1.0056541^300 + 3500/0.0056541*(1.0056541^300-1)
= $ 3,554,761.48
As the Future value of savings and deposits is more than what is required, the deposits of $3500 is sufficient to meet the retirement goals
Excess amount in account, relative to goal, as of retirement date = $3554761.48- $3350910.52 = $203850.96
If first deposit is $3500 and all subsequent deposits are higher by inflation rate
Monthly inflation rate = 1.02^(1/12)-1 = 0.0016516
Future value of savings of $150000 + Future value of deposits
= 150000*1.0056541^300 + (3500*1.0056541^299+3500*1.0016516*1.0056541^298+....+3500*1.0016516^299)
= 814114.90+ 3500*1.0056541^299*(1-(1.0016516/1.0056541)^300)/(1-1.0016516/1.0056541)
= 814114.90 + 3311350.67
=$4125465.56
Excess amount in account, relative to goal, as of retirement date = $4125465.56- $3350910.52 = $774555.05
Suppose $4000 is contributed for N months and then $2000 for (300-N) months
Future value of savings of $150000 + Future value of deposits = 3350910.52
150000*1.0056541^300 +(4000*1.0056541^299+.....+ upto N months) + (2000*1.0056541^(299-N)+.... upto 300-N months) = 3350910.52
=>814114.90+ (4000*1.0056541^299+.....+ upto 300 months)- (2000*1.0056541^(299-N)+.... upto 300-N months) = 3350910.52
=> 814114.90+ 4000/0.0056541*(1.0056541^300-1) - 2000/0.0056541*(1.0056541^(300-N)-1) = 3350910.52
=> 2000/0.0056541*(1.0056541^(300-N)-1) = 595371.90
1.0056541^(300-N) =2.6831597
=> 300-N = ln(2.6831597)/ln(1.0056541) = 175.05
N = 124.94
So , One needs to contribute $4000 for 125 months and then $2000 for remaining 175 months to achieve the retirement goals