Question

Sara is 30 years old today and wants to set aside an equal amount at the end of each of the next 30 years into a retirement fund earning 10% p.a. so that he can retire at age 60. At retirement , the funds will be transferred to an investment account earning 6% p.a. He expects to live to age 80 and wants to be able to withdraw $375,000 per year from the account on his 61st through 80th birthdays. He also wants to leave $550,000 as an inheritance to his children at age 80. How much money will he need to save in his retirement account per year to achieve these objectives?

Answer 1

First, 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 = 6% (rate of return earned during retirement)

nper = 20 (number of years in retirement)

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

fv = -550000 (Inheritance amount required to be in account at end of retirement. This is entered with a negative sign because it is a withdrawal)

PV is calculated to be $4,472,713.06

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 = 10% (rate of return earned until retirement)

nper = 30 (number of years until retirement)

pv = 0 (amount currently saved is zero)

fv = 4472713.06 (required amount at retirement)

PMT is calculated to be $27,190.73

He will need to save $27,190.73 per year