Question

A couple wishes to retire in Orlando, Florida at Disney World.  The couple will withdraw $10,000 each month in retirement (assume beginning of the month withdrawals). Their money will earn 3.0% APR with monthly compounding in retirement, and they expect retirement to last 30 years.  The couple would also like to leave $750,000 to their children at the end of the 30^th year in retirement.

The couple plans on retiring in 25 years. Currently, they have $200,000 in savings and invest in a mutual fund each quarter that pays a 9% APR (quarterly compounding) on average.  What quarterly investment will allow them to reach their goal? (assume end of quarter contributions)

Answer 1

The amount required on retirement date= $2,683,093.46 calculated using PV function of Excel as follows:

D10 to =PV(D2,D6,D9,07,08)*-1 А B с D Function 1 Formula arguments Value 2 Interest rate per year 3%/12 Rate 0.25% 3 Period-number of years (N) Given 30 4 Payment frequency Given Monthly 5 Number of payments a year (t) 12 6 Number of payments N*t NPER 360 7 Future Value Given FV $ 750,000 8 Timing of payments (Beginning of month) Given Type 1 9 Amount of monthly payment Given PMT $ 10,000 10 Present value as on date of retirement PV $2,683,093.46

Amount of end-of-quarter investments to be made to accumulate the above amount= $2,268.75 Calculated using PMT function of Excel with existing savings of $200,000 as PV as follows:

D10 to =PMT(D2,D6,08*-1,07,09)*-1 A B с D Function 1 Formula arguments Values 2 Interest rate per period 9%/4 Rate 2.25% 3 Period- number of years (N) Given 25 4 Payment frequency Given Quarterly 5 Number of payments a year (t) 4 6 Number of payments Nät NPER 100 7 Future Value As above Fv $2,683,093.46 8 Current savings Given $ 200,000.00 9 Timing of deposits (end of quarter) Given Type 0 10 Amount of quarterly investment PMT $2,268.75 Pv