Question

Mr. Homer, who turned 36 years old today, is getting serious about retirement planning. He has $85,000 already set aside in his retirement account and plans to add an equal amount in real terms at the end of each of the next 33 years so that he can retire at age 69. His goal is to build a retirement account that will enable him to make 25 annual withdraws with a purchasing power of $75,000 (at today’s prices) on his 70th through 94th birthdays. His retirement account is expected to earn 5.50% per year and the expected inflation rate is 2.50% per year. How much does Mr. Homer need to set aside in real terms at the end of each of the next 33 years to meet his retirement goal
Round to nearest penny

Answer 1

Real return rate on account = ((1 + nominal return rate on account) / (1 + inflation rate)) - 1

Real return rate on account = ((1 + 5.5%) / (1 + 2.5%)) - 1 = 2.9268%.

Money required in account at end of 33 years from now to fund retirement is calculated using PV function in Excel :

rate = 2.9268%

nper = 33

pmt = -75000 (Annual withdrawal. This is entered with a negative sign because it is a cash outflow)

PV is calculated to be $1,316,698.02

The amount to set aside each year to reach retirement goal is calculated using PMT function in Excel :

rate = 2.9268%

nper = 25

pv = -85000 (Beginning amount in account. This is entered with a negative sign because it is like a contribution into the account today)

fv = 1316698.02 (required amount in account at the end of 33 years)

PMT is calculated to be $31,620.84