51.3k views
3 votes
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.

User Wyu
by
8.3k points

1 Answer

4 votes

Answer:

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.

User Thijs Riezebeek
by
8.7k points
Welcome to QAmmunity.org, where you can ask questions and receive answers from other members of our community.