Question

You turned 35 today and have begun to think about saving for retirement. You anticipate that you will retire at age 67 (on your birthday), and will need $120,000 a year for 20 years, with the first withdrawal occurring from the retirement account occurring on your 67th birthday, and the last withdrawal occurring on your 86th birthday.

a) If you begin to make monthly payments into your retirement account today (on your 35th birthday), with the last payment into the account the month before your 67th birthday. The expected rate of return on your account, both before and after retirement, is 6% per year, compounded monthly. What is the payment that you must make into the account in order to hit your retirement target?

b) You decide to make annual payments into the retirement account, with the first payment still occurring today and the last payment on your 66th birthday. What is the even annual payment that is required to meet your target?

c) You add one wrinkle to this process. Specifically, you wish to save enough so that, in addition to your withdrawals of $120,000 per year for 20 years, you can leave a bequest to your children in the amount of $500,000. Assuming, as above, that you forecast living only until your 87th birthday, what is the even annual payment that you must make into the account to hit your (new) target?

Answer 1

a]

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)

nper = 20 (number of years in retirement)

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

fv = 0 (amount remaining after 20 years of withdrawals is zero)

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

PV is calculated to be $1,458,973.98

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

rate = 6%/12 (Converting annual rate of return into monthly rate of return)

nper = 32*12 (number of months until retirement = number of years until retirement * 12)

pv = 0 (amount currently saved is zero)

fv = 1458973.98 (required amount at retirement)

type = 1 (Each withdrawal is made at the start of the month, hence it is an annuity due)

PMT is calculated to be $1,253.99

Monthly payment to hit retirement target is $1,253.99

b]

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 = 6% (annual rate of return)

nper = 32 (number of years until retirement)

pv = 0 (amount currently saved is zero)

fv = 1458973.98 (required amount at retirement)

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

PMT is calculated to be $15,143.51

Yearly payment to hit retirement target is $15,143.51

c]

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)

nper = 20 (number of years in retirement)

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

fv = 500000 (amount remaining after 20 years of withdrawals)

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

PV is calculated to be $1,614,876.34

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 = 6% (annual rate of return)

nper = 32 (number of years until retirement)

pv = 0 (amount currently saved is zero)

fv = 1614876.34 (required amount at retirement)

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

PMT is calculated to be $16,761.71

Yearly payment to hit new retirement target is $16,761.71