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 $60,000 has today. (The real value of his retirement income will decline annually after he retires.) Hisretirement 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 $200,000 saved, and he expects to earn 7% annually on his savings. The data has been collected in the Microsoft Excel Online file below. Open the spreadsheet and perform the required analysis to answer the question below.

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

Part A

First, we calculate the inflation-adjusted withdrawals required during retirement to have the same purchasing power at the time he retires as $ 60000 has today. The retirement is in 10 years and annual inflation is 4%.

Particulars	Amount
Present Value	$    60,000.00
Int Rate	4.0000%
Periods	10

Future Value = Present Value * ( 1 + r )^n
= $ 60000 ( 1 + 0.04) ^ 10
= $ 60000 ( 1.04 ^ 10)
= $ 60000 * 1.4802
= $ 88814.66

The withdrawals required during retirement to have the same purchasing power at the time he retires as $ 60000 has today is $ 88814.66

Part B

Amount required at the time of retirement

First withdrawl made at the beginning of the year

PV of Annuity Due:
Annuity Due is series of cash flows that are deposited / withdrawn at regular intervals for specific period of time at begining of the period.

PV of Annuity Due = Cash Flow + [ Cash Flow * [ 1 - [(1+r)^-(n-1)]] /r ]
r - Int rate per period
n - No. of periods

Particulars	Amount
Cash Flow	$          88,814.66
Int Rate	7.000%
Periods	25

PV of Annuity Due = [ Cash Flow + Cash Flow * [ 1 - [(1+r)^-(n-1)]] / r ]
= [ $ 88814.66 + $ 88814.66 * [ 1 - [(1+0.07)^-24] ] / 0.07 ]
= [ $ 88814.66 + $ 88814.66 * [ 1 - [(1.07)^-24] ] / 0.07 ]
= [ $ 88814.66 + $ 88814.66 * [ 1 - [0.1971] ] / 0.07 ]
= [ $ 88814.66 + $ 88814.66 * [0.8029] ] / 0.07 ]
= [ $ 88814.66 + $ 1018645 ]
= $ 1107459.66

Amount required on the date of retirement $ 1107459.66

Part C

annual savings to meet the required amount at the time of retirement goal

Currently savings = $ 200000

Future value of retirement goal = $ 1107459.66

Present value of retirement goal

Particulars	Amount
Future Value	$      1,107,459.66
Int Rate	7.000%
Periods	10

Present Value = Future Value / ( 1 + r )^n
= $ 1107459.66 / ( 1 + 0.07 ) ^ 10
= $ 1107459.66 / ( 1.07 ) ^ 10
= $ 1107459.66 / 1.9672
= $ 562976.33

if he has $ 562976.33 he can meet the retirement goal , in that he has his savings of $ 200000

Balance amount to be saved = $ 562976.33 - $ 200000 = $ 362976.33

Present Value of Annuity:

Annuity is series of cash flows that are deposited at regular intervals for specific period of time.

PV of Annuity = Cash Flow * [ 1 - [(1+r)^-n]] /r
r - Int rate per period
n - No. of periods

Particulars	Amount
PV Annuity	$        362,976.33
Int Rate	7.000%
Periods	10

Cash Flow = PV of Annuity / [ 1 - [(1+r)^-n]] /r
= $ 362976.33 / [ 1 - [(1+0.07)^-10]] /0.07
= $ 362976.33 / [ 1 - [(1.07)^-10]] /0.07
= $ 362976.33 / [ 1 - 0.5083 ] /0.07
= $ 362976.33 / [0.4917 / 0.07 ]
= $ 362976.33 / 7.0236
= $ 51679.66

If his annual savings is  $ 51679.66 over the next 10 years with an initial savings $ 200000 , he can reach his retirement goal of $ 1107459.66 then he can receive $ 88814.66 during his retirement period