Question

You are planning to save for retirement over the next 32 years. To do this, you will invest $859 per month in a stock account and $329 per month in a separate bond account. The return of the stock account is expected to be 12%, and the bond account will pay 6%. When you retire, you will combine your money into an account with an expected 9% return.

How much can you withdraw each month in retirement from your account assuming a 20-year withdrawal period? (Round answer to 2 decimal places. Do not round intermediate calculations).

Answer 1

First use FV function in EXCEL to find the fund value after 32 years.

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

For stock account:

rate=12%/12=1%

nper=32*12=384 months

pmt=859

pv=0

=FV(1%,384,-859,0,0)=$3,835,134.8

For bond account:

rate=6%/12=0.5%

nper=384 months

pmt=329

=FV(0.5%,384,-329,0,0)=$380,877.02

The value of the fund at 32 years=$3,835,134.8+$380,877.02=$4,216,011.81

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

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

rate=9%/12=0.75%

nper=12*20=240 months

pv=4216011.81

fv=0

=PMT(0.75%,240,-4216011.81,0,0)=$37,932.55

Monthly withdrawl=$37,932.55