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 (i.e. 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 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 SHOW ALL STEPS IN EXCEL

Answer 1

Present Value = $275,000

Inflation rate, i = 3%

To have the same purchasing power in 10 years the $50,000 will be worth = $50000x(1.03)^10 = $67,195.82

Brother will get $67,195.82 after 10 years as the first payment of a series of 25 payments. This payment will rise by 3% every year at the inflation rate. Below is the chart that show how much the brother will require starting from the beginning of year 10 to equal $50,000 of today. The first payment will be $67,195.82 which will then rise by 3% annually. We first need to find the present value of those required payments at 8% rate of interest.

Yearly payments required	Present Value of yearly payments @ 8%
CFx(1+i)^t	Yearly Payment/(1+r)^t
67195.82	67195.82
69211.69	64084.90
71288.04	61118.01
73426.69	58288.47
75629.49	55589.93
77898.37	53016.32
80235.32	50561.86
82642.38	48221.04
85121.65	45988.58
87675.30	43859.48
90305.56	41828.95
93014.73	39892.42
95805.17	38045.55
98679.33	36284.18
101639.71	34604.36
104688.90	33002.31
107829.56	31474.42
111064.45	30017.27
114396.38	28627.58
117828.28	27302.23
121363.12	26038.24
125004.02	24832.77
128754.14	23683.10
132616.76	22586.66
136595.26	21540.98
Total	1007685.45

so the new future value is, FV = $1,007,685.445 PV, Present Value = $275,000 (current savings)

interest rate, r = 8% N = 10

the payment that is required for 10 years to get to the future value of $1,007,685.445 is

PV = CF1/(1.08)+CF2/(1.08)^2+.........+(CF10+FV)/(1.08)^10

$275,000 = CF1/(1.08)+CF2/(1.08)^2+.........+(CF10+$1,007,685.445)/(1.08)^10

CF,Payment = $110,543.12

The brother needs to save $110,543.12 every year.