Question

Taylor has a retirement account that pays 4% per year compounded monthly. Every month for 20 years, Taylor deposits $444, with the first deposit at the end of month 1 The day the last deposit is made, the interest rate increases to 6% per year compounded monthly. During retirement, Taylor plans to make equal monthly withdrawals for 15 years, thus depleting the account. The first withdrawal occurs one month after the last deposit. How much can be withdrawn each month?

Answer 1

Calculating Amount Accumulated after 20 years

Amount deposited per month = $444

Since interest rate is compounded monthly

Monthly rate = Annual rate compounded monthly / 12 = 4%/12

No of end of month deposits = 12 x no of years = 12 x 20 = 240

These end of the month deposits form an ordinary annuity, To find the Amount accumulated after 20 years we need to find future value of ordinary annuity using FV function in excel

Formula to be used in excel: =FV(rate,nper,-pmt)

Using FV function in excel, we get amount accumulated after 20 years or at retirement = $162847.9339

Calculating Amount that can be withdrawn per month

Monthly interest rate during retirement = Annual interest rate / 12 = 6%/12 = 0.50%

No of withdrawls = 12 x no of years = 12 x 15 = 180

We can find the monthly withdrawl during retirement using PMT function in excel

Formula to be used in excel: =PMT(rate,nper,-pv)

Using PMT function in excel, we get monthly withdrawl during retirement = $1374.2034 = $1374.20

Hence Amount that can be withdrawn per month = $1374.20