Question

Assume that your brother is now 50 years old, that he plans to retire in 10 years and that he expects to live for 25 years after he retires (until he is 85 years old). He wants a fixed retirement income that has the same purchasing power at the time he retires as $50,000 has today (he realizes that the real value of his retirement income will decline year by year after he retires). His retirement income will begin pm the day after he retires, 10 years from today, and he will then get 24 additional annual payments Inflation is expected to be 3% per year from today forward. Your brother currently has a savings of $275,000 and expects to earn a return on his savings of 8% per year, annual compounding. To the nearest dollar, how much must your brother save during each of the next 10 years (with deposits being made at the end of each year) in order to meet his retirement goal?

PLEASE calculate in excel using appropriate excel formulas to showing how you arrived at your answer

Answer 1

We will first calculate the annual retirement income - which should be fixed and has same value in 10 years time as $50000 has today. This is akin to calculating the value of $50000 at 3% inflation rate in 10 years which will be :

50000 * (1+3%)¹⁰ = $ 67195.82

Hence your brother wants to receive $67195.82 for each of his 25 years of retirement. This is like an annuity stream and the amount required to get this stream at the expected interest rate of 8% is the present value of this annuity stream. We will use the following formula : PV = where r is the interest rate (given 8%) and t is the time period (given 25 years).

PV = 67195.82 * [1 - (1+8%)^-25]/8% = $ 717,300.33

Hence the value of borther's savings in 10 years time should $ 717300.33. The current savings are $ 275000 which at 8% will grow to : 275000 * (1+8%)¹⁰ = $ 593704.37 but there will be still a gap of $123595.95.

The brother plans to invest annually a sum of money at 8% for 10 years to bridge the gap of $123595.95 and this is like calculating future value of an annuity stream at 8% . The future value annuity formula is:

FV = ; we are given r = 8%, t = 10 and FV should be 123595.95. Plugging in the values, we get : 123595.95 = Periodic Cash Flow * [(1+8%)¹⁰ - 1]/8% or Periodic Cash flows should be = $ 8531.77 . Hence the brother should invest $ 8531.77 every year at 8% in addition to current savings to meet his retirement requirements.