Question

You just started your first full time job out of college. You recall from your finance course the importance of starting to save early for retirement. You plan on making deposits of $215 per pay check into a stock account and $130 per pay check into a bond account. You are paid every two weeks (26 pay checks per year). It is your plan to make these deposits for the next thirty-years. You expect that you will earn 8.75% per year on the stock account and 5.5% on the bond account. When you retire in thirty-years you plan on depositing the balance the money into a money-market account that you expect should pay 2%. How much could you withdraw monthly, and have the money last for the next thirty years?

Answer 1

First use FV function in EXCEL to find the fund value at end of 30 years.

=FV(rate,nper,pmt,pv,type)

For stock account:

rate=8.75%/26=0.3365%

nper=30 years*26=780

pmt=215

pv=0

=FV(0.3365%,780,-215,0,0)=$814,151.16

For bond account:

rate=5.5%/26=0.2115%

nper=780

pmt=130

=FV(0.2115%,780,-130,0,0)=$257,980.86

The value of the fund at 30 years=$814,151.16+$257,980.86=$1,072,132.02

==> If you want to know withdrawl amount each month, use PMT function in EXCEL

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

rate=2%/12=0.1667%

nper=12*30=360 months

pv=1072132.02

fv=0

=PMT(0.1667%,360,-1072132.02,0,0)=$3,962.81

Monthly withdrawl=$3,962.81

Answer 2

Monthly Withdrawal during Retirement = $3962.81