Question

Your father is 50 years old and will retire in 10 years. He expects to live for 25 years after he retires, until he is 85. He wants a fixed retirement income that has the same purchasing power at the time he retires as $40,000 has today. (The real value of his retirement income will decline annually after he retires.) His retirement income will begin the day he retires, 10 years from today, at which time he will receive 24 additional annual payments. Annual inflation is expected to be 5%. He currently has $100,000 saved, and he expects to earn 8% annually on his savings. How much must he save during each of the next 10 years (end-of-year deposits) to meet his retirement goal? Hello I want to do this task by excel tools.Thanks.

Answer 1

First, we calculate the inflation-adjusted withdrawals required during retirement to have the same purchasing power at the time he retires as $40,000 has today. The retirement is in 10 years and annual inflation is 5%.

Future value = present value * (1 + growth rate)^{number of years}

Future value = $40,000 * (1 + 5%)¹⁰ = $65,155.79

The withdrawals required during retirement to have the same purchasing power at the time he retires as $40,000 has today is  $65,155.79.

Next, we calculate the amount required at retirement to enable the yearly withdrawals during retirement. The amount required at retirement is calculated using PV function in Excel :

rate = 8% (rate of return earned)

nper = 25 (number of annual withdrawals)

pmt = -65155.79 (yearly withdrawal. This is entered with a negative sign because it is a withdrawal)

fv = 0 (amount remaining in savings after 25 withdrawals is zero)

type = 1 (first withdrawal is made at the beginning of the year, hence it is an annuity due)

PV is calculated to be $751,165.30

A2 =PV(8%,25,-A1,0,1) B C D E 1 2 A 65,155.79 $751,165.30

Next, we calculate the yearly saving required to accumulate the required amount at retirement. The yearly saving required is calculated using PMT function in Excel :

rate = 8% (rate of return earned)

nper = 10 (number of years until retirement)

pv = -100000 (Amount currently saved. This is entered with a negative sign because it is like a payment into the retirement savings account today)

fv = 751165.30 (required amount at retirement)

PMT is calculated to be $36,949.61

The yearly saving required is $36,949.61

A3 =PMT(8%,10,-100000, A2) B C D E F 1. 2 3 65,155.79 $751,165.30 ($36,949.61).