Question

You are planning to save for retirement over the next 30 years. To do this, you will invest $750 per month in a stock account and $325 per month in a bond account. The return of the stock account is expected to be an APR of 10.5%, and the bond account will earn an APR of 6.1%. When you retire, you will combine your money into an account with an APR of 6.9%. All interest rates are compounded monthly. How much can you withdraw each month from your account assuming a withdrawal period of 25 years?

Answer 1

Value of stock account after 30 years is calculated using FV function in Excel :

rate = 10.5%/12 (converting annual rate into monthly rate)

nper = 30 * 12 (total number of monthly deposits = number of years * 12)

pmt = -750 (Monthly deposit. This is entered with a negative sign because it is a cash outflow)

FV is calculated to be $1,887,300.74

Value of bond account after 30 years is calculated using FV function in Excel :

rate = 6.1%/12 (converting annual rate into monthly rate)

nper = 30 * 12 (total number of monthly deposits = number of years * 12)

pmt = -325 (Monthly deposit. This is entered with a negative sign because it is a cash outflow)

FV is calculated to be $332,782.27

Total value of stock account + bond account after 30 years = $1,887,300.74 + $332,782.27

Total value of stock account + bond account after 30 years = $2,220,083.01

Monthly withdrawal during retirement is calculated using PMT function in Excel :

rate = 6.9%12 (converting annual rate into monthly rate)

nper = 25 * 12 (total number of monthly withdrawals = number of years * 12)

pv = 2220083.01 (Total value of stock account + bond account after 30 years)

PMT is calculated to be $15,549.74

Monthly withdrawal during retirement is $15,549.74