Question

Assume you make $50,000 per year. You expect your pay to increase by 2.5% year from now until you retire in 30 years. Your goal is to withdraw an amount equal to 80% of your annual income at retirement each year for 25 years (assume withdrawals are made at the end of each year in retirement). How much would you have to invest each year from now until retirement if your investment returns are 8% per year while working and 6% per year while retired.

Answer 1

The annual pay at the end of 30 years=$50,000*((1+2.5%)^30)=$104,878.4

You need to withdraw 80% of $104,878.4 during your retirement period for 25 years=80%*$104,878.4=$83,902.7

Now we have to use PV function in EXCEL to find the accumulated amount needed to withdraw this $83,902.7 every year at 6% for 25 years.

=PV(rate,nper,pmt,fv,type)

=PV(6%,25,83902.7,0,0)

PV=$1,072,558.1

==> Now to need to find the deposits made each year for next 30 years with 8% interest rate to accumulate $1,072,558.1. use PMT function in EXCEL

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

rate=8%

nper=30

pv=0

fv=1,072,558.1

type=0

=PMT(8%,30,0,1072558.1,0)

PMT=$9467.94

The annual deposits made until retirement=$9467.94