Question

Once you’ve saved 25 years for retirement, how much can you draw from the retirement account over the next 30 years? In cell B14, enter a formula to calculate how much you can pay yourself from the retirement account every month.

Answer 1

you have to use the payment formula in excel:

=PMT (rate, nper, pv, [fv], [type])

where:

• rate = the interest rate earned by your retirement account . If the distributions re annual, then use the annual rate, if the distributions are monthly, then you must adjust the effective monthly rate.
• nper = number of distributions. Assuming that you will only withdraw once a year, then nper = 30. If you are going to collect monthly distributions, then nper = 360.
• pv = the present value of your retirement account.
• fv (optional) = by default it = 0, so it is correct for this problem.
• type (optional) = by default the payments are considered an ordinary annuity, which should work in this case.

for e.g., assuming that you have $1,000,000 in your retirement account, you can earn 6% interest rate and you will receive 30 distributions.

=PMT (6%,30,1000000, [fv], [type]) = -$72,648.91

Excel uses a minus sign because the principal will decrease as distributions are made.