Question

Problem 1

Bill, age 45, wants to retire at age 60. He currently earns $60,000 per year. His goal is to replace 80% of his preretirement income. He wants the retirement income to be adjusted for inflation. Bill has an investment portfolio valued at $150,000, which is cur­rently earning 10% average annual returns. Bill expects inflation to average 3% and, based on his family health, predicts he will live to age 90. Bill is currently saving 7% of his gross income at each year-end and expects to continue this level of savings. Bill wants to ignore any Social Security benefits for purposes of retirement planning.

a. What will Bill’s annual income needs be at age 60?

b. Will the need be for an ordinary annuity or an annuity due?

c. How much total capital will Bill need at age 60?

d. How much capital will Bill have at age 60?

e. Will Bill have enough income at retirement?

f. What is the earliest age that Bill could retire utilizing the current savings and investment plan?

g. How much would Bill need to increase his savings on an annual basis to meet his goal of retiring at age 60?

h. Even assuming that Bill increases his savings to an appropriate amount, what are the risks that may affect the success of the plan?

i. How could the capital needs analysis be modified to reduce the risks identified above?

Answer 1

a) Bill's retirement income needs at age 60 = Current Salary x 80% x (1 + inflation)^15

= 60,000 x 80% x (1 + 3%)^15

= $74,782.44

b) He would need an annuity due that pays at the beginning of the year to meet his retirement needs.

c) Using present value for growing annuity formula,

PV = P / (r - g) x [1 - ((1 + g) / (1 + r))^n]

Here, P = 74,782.44, r = 10%, g = 3%, n = 30

=> PV = $919,714..08 is the amount he needs.

d) Bill currently invest 7% x 60,000 = 4,200 each year, which will grow at the rate of inflation. Using future value of growing annuity formula and future value of his investment portfolio,

FV = P / (r - g) x [(1 + r)^n - (1 + g)^n] + PV x (1 + r)^n

Here, P = 4,200, r = 10%, g = 3%, n = 15, PV = 150,000

=> FV = $783,744.08 is the amount he will have at retirement.

f. Investment deficit is only 79,479. I tried changing it to 61 years working and then returement, and it showed extra surplus, hence ans is bw 60 to 61. He will have to work till aprox 60 years and 6 months

g. Ans is 10.69% from the excel model

								Retirement income required			$74,782.44	FV(3%, 15, 0, -C5*80%)
	age	Salary	savings	Investment value
			C*7%	increase by 10% each year and add savings each year
			10.69%	150,000
	45	60,000	6,411	171,411.0			61	$74,782.44			Present value of investment needed
	46	60,000	6,411	194,963.1			62	$77,025.91			$919,714.03	NPV(10%, I5:I34)
	47	60,000	6,411	220,870.4			63	$79,336.69
	48	60,000	6,411	249,368.5			64	$81,716.79			Retirement corpus from investments
	49	60,000	6,411	280,716.3			65	$84,168.29			919,719.7	E20
	50	60,000	6,411	315,198.9			66	$86,693.34
	51	60,000	6,411	353,129.8			67	$89,294.14			Investment deficit
	52	60,000	6,411	394,853.8			68	$91,972.96			($6)	L6-L9
	53	60,000	6,411	440,750.2			69	$94,732.15
	54	60,000	6,411	491,236.2			70	$97,574.12
	55	60,000	6,411	546,770.8			71	$100,501.34
	56	60,000	6,411	607,858.9			72	$103,516.38
	57	60,000	6,411	675,055.8			73	$106,621.87
	58	60,000	6,411	748,972.4			74	$109,820.53
	59	60,000	6,411	830,280.6			75	$113,115.14
	60	60,000	6,411	919,719.7			76	$116,508.60
							77	$120,003.86
							78	$123,603.97
							79	$127,312.09
							80	$131,131.45
							81	$135,065.40
							82	$139,117.36
							83	$143,290.88
							84	$147,589.61
							85	$152,017.30
							86	$156,577.81
							87	$161,275.15
							88	$166,113.40
							89	$171,096.80
sksnvolved:							90	$176,229.71

h. Risk involved:  inflation could be more, the market could be down at the time of retirement, hence the return on investment could be less than 10% yoy.

I. More investment in debt instruments, if done in equity. Government bonds. Taking insurance.