Question

Bob has nothing in his retirement account. However, he plans to save 8,000 per years in his retirement account for each of the next 17 years. His first contribution to his retirement account is expected in 1year, Bob expects to earn 6.3% per year in his retirement account. Bob plans to retire in 17 years immediately after making his last 8,000 contribution to his retirement account. In his retirement, Bob plans to withdraw 35,000 per year for as long as he can. How many payments of 35,000 can Bob expect to receive in retirement if he receives annual payments of 35,000 in retirement and his first retirement payment is received exactly 1 year after he retires?

Answer 1

First, we calculate the value of the account at retirement. Value of the account at retirement is calculated using FV function in Excel :

rate = 6.3%

nper = 17

pmt = -8000 (This is entered with a negative sign as it is a cash outflow for Bob)

FV is calculated to be $231,784.89

Number of payments Bob will receive in retirement is calculated using NPER function in Excel ;

rate = 6.3%

pmt = -35000 (Yearly withdrawal. This is entered with a negative sign as it is a cash outflow from the account)

pv = 231784.89 (value of account at retirement)

NPER is calculated to be 8.84

Bob can receive 8.84 payments