Question

Assume you are now 30 years old. You plan to retire when you are 60 years old. You think you will live until you are 85 years old. a. If the rate of return during your working years (a.k.a. the "savings period") is 10% and you plan to save $1,000 per year, how much will you have saved up by retirement age? b. If the rate of return during your retirement is 7%, how much will you receive as an annual income during the 25 years you are retired?

Please show me how I would set this up on excel...What equations would be used to solve each. Thank you!

Answer 1

Notations:

Nper = N = Years

FV = Future value

PV = Present value

PMT = Payment

Rate = R = Interest rate

Excel function:

a.

Total 30 years of payment as retirement age is 60 and present age is 30.

=FV(Rate,Nper,-PMT)

=FV(10%,30,-1000)

= $164,494.02 (Savings after 30 years)

Formula:

FV = PMT x ((1+R)^N-1)/R

=1000*((1+10%)^30-1)/10%

= $164,494.02

b.

=PMT(7%,25,-164494.02)

= $14,115.32 (Expected payment after retirement)

Formula:

PMT = PV x R /((1-(1+R)^-N)

= 164494.02*7%/((1-(1+7%)^-25))

= $14,115.32