Question

If you wish to have a comfortable retirement 25 years long by withdrawing $2000 a month from an account earning 3% APR compounded monthly. What approximate rate of return rounded to two decimals would you need in the accumulation phase (savings) if you deposit $300 a month in an account compounded monthly for 40 years?

Answer 1

First, present value at the end of 40 years of retirement's monthly withdrawal shall be calculated: Monthly interest rate, RATE # of monthly withdrawal, NPER Monthly withdrawal amount, PMT Balance in account at the end, FV Monthly withdrawal type, TYPE Present value of retirement withdrawal in 25 years 0.25% [3% / 12 --> Annual interest rate is converted into monthly rate] 300 [25 years x 12 as there will be 12 monthly withdrawal per year] $2,000 $0 [Because all will be withdrawn over retirement tenure] 0 = 0 if paid at the end of each period; 1 if paid at the beginning of each period. $421,752.91 [Ref: NOTE-1] NOTE-1: B Present value of retirement withdrawal is calculated using EXCEL FUNCTION PV(rate,nper,pmt,fv,type) where rate=0.25%; nper=300; pmt=-2000; fv=-0; type=0; Here, pmt is negative as it denotes cash outflows; type=0 as withdrawal takes place at the end of each month only. A с D 1 Monthly interest rate, RATE =3%/12 [3% / 12 --> Annual interest rate is converted into monthly rate] 2 # of monthly withdrawal, NPER =25*12 (25 years x 12 as there will be 12 monthly withdrawal per year] 3 Monthly withdrawal amount, PMT $2,000 4 Balance in account at the end, FV $0 (Because all will be withdrawn over retirement tenure) 5 Monthly withdrawal type, TYPE 0 = 0 if paid at the end of each period; 1 if paid at the beginning of each period. 6 Present value of retirement withdrawal =PV(B1,B2,-B3,B4,B5) [Ref: NOTE-1] 7

Now, considering $421752.91 as the target maturity value, approximate rate of return required to be earned shall be calculated: # of monthly deposits to maturity, NPER Monthly deposit amount, PMT Initial deposit in account, PV Target maturity value at the end, FV Monthly deposit type, TYPE Monthly interest rate Number of months per year Annualized rate of return required 480 [40 years x 12 --> As there will be 12 months per year] $300 $0 $421,752.91 [As calculated above] 0 = 0 if deposited at the end of each period; 1 if deposited at the beginning of each period 0.3903% (Ref: EXCEL SCREENSHOT] 12 4.68% (0.3903% x 12] EXCEL SCREENSHOT: Monthly interest rate is calculated using EXCEL FUNCTION RATE(nper,pmt,pv,fv,type) where nper=480; pmt=-300; pv=0; fv=-421752.91; type=0; Here, pmt is positive as it denotes cash outflows; fv is negative as it denotes cash inflow; type = 0 as deposited at the end of each period only. A 1 # of monthly deposits to maturity, NPER 2 Monthly deposit amount, PMT 3 Initial deposit in account, PV 4 Target maturity value at the end, FV 5 Monthly deposit type, TYPE 6 Monthly interest rate 7 Number of months per year 8 Annualized rate of return required 9 B C D =40*12 [40 years x 12 --> As there will be 12 months per year] $300 $0 $421,752.91 [As calculated above) 0 = 0 if deposited at the end of each period; 1 if deposited at the beginning of each period =RATE(B1,B2,B3,-B4,B5) [Ref: EXCEL SCREENSHOT] 12 =B6*B7 (0.3903% x 12]