Question

Your father is 50 years old and will retire in 10 years. He expects to live for 25 years after he retires, until he is 85. He wants a fixed retirement income that has the same purchasing power at the time he retires as $55,000 has today. (The real value of his retirement income will decline annually after he retires.) His retirement income will begin the day he retires, 10 years from today, at which time he will receive 24 additional annual payments. Annual inflation is expected to be 4%. He currently has $190,000 saved, and he expects to earn 8% annually on his savings.

How much must he save during each of the next 10 years (end-of-year deposits) to meet his retirement goal? Do not round your intermediate calculations. Round your answer to the nearest cent.

Answer 1

Fixed Retirement income in current terms = Present value = $55000, No of years to retirement = n = 10, Inflation = 4%

Fixed retirement income after retirement = Present value (1+inflation)ⁿ = 55000(1+4%)¹⁰ = 55000 x 1.48024428 = 81413.4354 = 81413.4354

Now will will calculate the value of savings required at retirement to fund fixed retirement income after retirement..

Value of savings will be equal to present value of fixed retirement income after retirement. Since the fixed retirement income withdrawls begin from the day of the retirement, therefore they form an annuity due. Annuity due is an annuity in which payments are made at beginning of the year.

We can find the present value of annuity due using PV function in excel

No of fixed withdrawls of retirement income or years = 25, Rate of return = 8%

Formula to used in excel: =PV(rate,nper,-pmt,fv,type)

In the formula fv=0 because as the fixed retirement income withdrawls are made, value of accumulated savings decreases and it becomes zero at end of 25 years after retirement. Also type = 1 because payment or withdrawls of retirement income are made at beginning of the year.

Using PV function in excel, we get value of savings required at retirement = $938595.8177

Now amount currently saved or current value of savings = $190000

We can find the amount to save during next 10 years using PMT function in excel

Formula to be used in excel: =PMT(rate,nper,pv,-fv)

Using PMT function in excel, we get amount of save each year = $36475.1864 = $36475.19 (rounded to nearest cent)

Hence Amount to save each year for next 10 years = $36475.19