Question

You are trying to decide how much to save for retirement. Assume you plan to save $4,500 per year with the first investment made one year from now. You think you can earn 5.5​% per year on your investments and you plan to retire in 26 ​years, immediately after making your last $4,500 investment.

a. How much will you have in your retirement account on the day you​ retire?

b.​ If, instead of investingv$4,500 per​ year, you wanted to make one​ lump-sum investment today for your retirement that will result in the same retirement​ saving, how much would that lump sum need to​ be?

c. If you hope to live for 24 years in​ retirement, how much can you withdraw every year in retirement​ (starting one year after​ retirement) so that you will just exhaust your savings with the 24th withdrawal​ (assume your savings will continue to earn 5.5​% in​ retirement)?

d.​ If, instead, you decide to withdraw $49,000per year in retirement​ (again with the first withdrawal one year after​ retiring), how many years will it take until you exhaust your​ savings? (Use​ trial-and-error, a financial​ calculator: solve for​ "N", or​ Excel: function​ NPER)

e. Assuming the most you can afford to save is $ 900 per​ year, but you want to retire with $1000 in your investment​ account, how high of a return do you need to earn on your​ investments? (Use​ trial-and-error, a financial​ calculator: solve for the interest​ rate, or​ Excel: function​ RATE)

Answer 1

a]

Future value of annuity = P * [(1 + r)ⁿ - 1] / r,

where P = periodic payment. This is $4,500

r = periodic rate of interest. This is 5.5%

n = number of periods. This is 26

Future value of annuity = $4,500 * [(1 + 5.5%)²⁶ - 1] / 5.5%

Future value of annuity = $247,346.91

b]

present value = future value / (1 + interest rate)^{number of years}

present value = $247,346.91 / (1 + 5.5%)²⁶

present value = $61,481.23

c]

PV of annuity = P * [1 - (1 + r)^-n] / r,

where P = periodic payment. We need to calculate this.

r = interest rate per period. This is 5.5%

n = number of periods. This is 24

$247,346.91 = P * [1 - (1 + 5.5%)^-24] / 5.5%

P =  $247,346.91 * 5.5% / [1 - (1 + 5.5%)^-24]

P =  $18,807.22

d]

Number of years is calculated using NPER function in Excel :

rate = 5.5%

pmt = -49000

pv = 247346.91

NPER is calculated to be 6.07 years

e]

Return required to be earned is calculated using RATE function in Excel :

nper = 26

pmt = -900

pv = 0

fv = 1000000

RATE is calculated to be 23.98%

RATE is calculated to be 23.98%

RATE is calculated to be 23.98%