Question

An individual is currently 30 years old and she is planning her financial needs upon retirement. She will retire at age 65 (exactly 35 years from now) and she plans on funding 20 years of retirement with her investments. Ignore any social security payments and ignore any taxes. She made $131,000 last year and she estimates she will need 75% of her current income in today's dollars to live on when she retires. She believes that inflation will average 3 percent per year during her working years. (For simplicity we will ignore inflation during her retirement years). She will retire at age 65 and will begin drawing down her retirement annuity at age 65. She plans on making a total of 20 annual withdrawals after she retires. After she retires she believes she will be able to earn 6 percent per year. If she puts her money in a blended stock and bond portfolio now, she figures she can earn 10 percent per year until she retires. 1) How much money will she need to withdraw each year starting at age 65 to have the same purchasing power as today? Round your answer to the nearest penny, do not enter the dollar sign in your answer. 2) How much money must she have at age 65 in order to make her planned withdrawals? Round your answer to the nearest penny and do not enter the dollar sign in your answer. 3) How much should she save per year starting right now in order to have the retirement annuity she desires? Round your answer to the nearest dollar and do not enter the dollar sign in your answer.

Answer 1

1]

Amount required per year during retirement = current salary * 75% * (1 + inflation rate)^{years until retirement}

Amount required per year during retirement = $131,000 * 75% * (1 + 3%)⁷⁵

Amount required per year during retirement = $276,461.99.

2]

Money required at age 65 to fund retirement is calculated using PV function in Excel :

rate = 6% (return earned during retirement)

nper = 20 (total number of annual withdrawals during retirement)

pmt = -276461.99 (Amount required per year during retirement. This is entered with a negative sign because it is a withdrawal).

PV is calculated to be $3,170,997.25.

Money required at age 65 to fund retirement is $3,170,997.25.

A5 fi C =PV(6%,20,276461.99) D E 5 ($3,170,997.25)

3]

Amount to save per year to have the required money at retirement is calculated using PMT function in Excel :

rate = 10% (return earned until retirement)

nper = 35 (number of years until retirement)

pv = 0 (savings in beginning is zero)

fv = 3170997.25 (Money required at age 65 to fund retirement)

PMT is calculated to be $11,700.04.

Аб f =PMT(10%,35,0,3170997.25) F б ($11,700.04)