Question

You just graduated from college and are starting your new job. You realized the importance to save for the future and have figured out that you will save $1,000 per month for the next 12 years; and then increase to $7,000 per month for the following 4 years. The amount accumulated at the end of these investments will be your retirement egg nest. You plan to start retirement and start withdrawing monthly amounts the following month (you will be in retirement for 28 years). If your required rate of return is 12% compounded monthly, how much are your monthly withdrawals?

Do not use the $ sign. Use commas to separate thousands. Use to decimals. Round to the nearest dollar. For example if you obtain $1,432.728 then enter 1,433; if you obtain $432.00 then enter 432

Answer 1

Calculation of value of fund at the end of 12 years:

Rate = 12% / 12 = 1%
Nper = 12 * 12 = 144
PMT = 1000
PV = 0

Value of fund at the end of 12 years can be calculated by using the following excel formula:
=FV(rate,nper,pmt,pv)
=FV(1%,144,-1000,0)
= $319,061.56

Calculation of value of fund on retirement:

Rate = 1%
Nper = 48
PMT = 7000
PV = 319061.56

Value of fund on retirement can be calculated by using the following excel formula:
=FV(rate,nper,pmt,pv)
=FV(1%,48,-7000,-319061.56)
= $942,957.62

Calculation of monthly withdrawals:

Rate = 1%
Nper = 28 * 12 = 336
PV = $942,957.62
FV = 0

Amount of monthly withdrawals can be calculated by using the following excel formula:
=PMT(rate,nper,pv,fv)
=PMT(1%,336,-942957.62,0)
= $9,774.82 or $9,775

Amount of monthly withdrawals = 9,775 (Rounded to the nearest dollar)