Question

You just graduated from college and are starting your new job. You realized the importance to save for the future and have figured out that you will save $3,000 per quarter for the next 12 years; and then increase to $7,000 per quarter for the following 4 years. The amount accumulated at the end of these investments will be your retirement egg nest. You plan to start retirement and start withdrawing quarterly amounts the following quarter (you will be in retirement for 24 years). If your required rate of return is 12% compounded quarterly, how much are your quarterly withdrawals?

Do not use the $ sign. Use commas to separate thousands. Use to decimals. Round to the nearest dollar. For example if you obtain $1,432.728 then enter 1,433;  if you obtain $432.00 then enter 432

Answer 1

Initially you deposit 3,000 per quarter for the next 12 years which is compunding quarterly. The future value can be found by FV function in EXCEL

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

rate=12%/4=3%

nper=number of periods=12*4=48

pmt=3000

pv=0

=FV(3%,48,-3000,0,0)

FV=313,225

Again, this amount becomes =313225*(1+(12%/4))^(4*4) =313225*(1.03)^16=502,634 in next 4 years at the time of the retirement.

He starts paying 7,000 for the next 4 years per quarter

=FV(3%,16,-7000,0,0)

FV=141,098

So, add these two figures to arrive at the retirement corpus=502,634+141,098=643,732 (there would be decimal difference because of the round off)

Now, to find the quarterly withdrawals for 24 years, the formula is=PMT(rate,nper,pv,fv,type)

rate=12%/4=3%

nper=4*24=96

pv=643,732

=PMT(3%,96,643732,0,0)

PMT=20,513

The quarterly withdrawals is 20,513