In: Finance
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?
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