Question

Hermes Conrad is celebrating his birthday and wants to start saving for his anticipated retirement. He has the following years to retirement and retirement spending goals: Years until retirement = 30; Amount to withdraw each year = $90,000; Years to withdraw in retirement = 20; Investment rate = 8%. Because Hermes is planning ahead, the first withdrawal will not take place until one year after he retires. He wants to make equal annual deposits into his account for his retirement fund. a. If he starts making these deposits in one year and makes his last deposit on the day he retires, what amount must he deposit annually to be able to make the desired withdrawals at retirement? b. Suppose Mr. Conrad has just inherited a large sum of money. Rather than making equal annual payments, he has decided to make one lump sum deposit today to cover his retirement needs. What amount does he have to deposit today? c. Suppose Hermes's employer will contribute to the account each year as part of the company's profit sharing plan. In addition, he expects a distribution from a family trust several years from now. What amount must he deposit annually now to be able to make the desired withdrawals at retirement if Employer's annual contribution = $1,500; Years until trust fund distribution = 20; and Amount of trust fund distribution = $25,000? PLEASE PROVIDE YOUR ANSWER USING EXCEL, WITH FORMULAS.

Answer 1

Present value of amount to be withdrawn
Year	Amount	Discount factor	Present value	Discount factor formula	Present value formula
31	90000	0.0920	8281.444379	=1/((1+8%)^A3)	=B3*C3
32	90000	0.0852	7668.004055	=1/((1+8%)^A4)	=B4*C4
33	90000	0.0789	7100.003754	=1/((1+8%)^A5)	=B5*C5
34	90000	0.0730	6574.07755	=1/((1+8%)^A6)	=B6*C6
35	90000	0.0676	6087.108843	=1/((1+8%)^A7)	=B7*C7
36	90000	0.0626	5636.211892	=1/((1+8%)^A8)	=B8*C8
37	90000	0.0580	5218.714714	=1/((1+8%)^A9)	=B9*C9
38	90000	0.0537	4832.143254	=1/((1+8%)^A10)	=B10*C10
39	90000	0.0497	4474.206717	=1/((1+8%)^A11)	=B11*C11
40	90000	0.0460	4142.783997	=1/((1+8%)^A12)	=B12*C12
41	90000	0.0426	3835.911108	=1/((1+8%)^A13)	=B13*C13
42	90000	0.0395	3551.769545	=1/((1+8%)^A14)	=B14*C14
43	90000	0.0365	3288.675504	=1/((1+8%)^A15)	=B15*C15
44	90000	0.0338	3045.069912	=1/((1+8%)^A16)	=B16*C16
45	90000	0.0313	2819.509177	=1/((1+8%)^A17)	=B17*C17
46	90000	0.0290	2610.656646	=1/((1+8%)^A18)	=B18*C18
47	90000	0.0269	2417.274672	=1/((1+8%)^A19)	=B19*C19
48	90000	0.0249	2238.217289	=1/((1+8%)^A20)	=B20*C20
49	90000	0.0230	2072.423416	=1/((1+8%)^A21)	=B21*C21
50	90000	0.0213	1918.91057	=1/((1+8%)^A22)	=B22*C22
Sum present value	87813.12	=SUM(D3:D22)
This present value is at time = 0
Present value of amount to be invested
Year	Amount	Discount factor	Present value	Discount factor formula	Present value formula
1		0.925925926	0.925925926	=1/((1+8%)^A28)	=FORMULATEXT(C28)
2		0.85733882	0.85733882	=1/((1+8%)^A29)	=FORMULATEXT(C29)
3		0.793832241	0.793832241	=1/((1+8%)^A30)	=FORMULATEXT(C30)
4		0.735029853	0.735029853	=1/((1+8%)^A31)	=FORMULATEXT(C31)
5		0.680583197	0.680583197	=1/((1+8%)^A32)	=FORMULATEXT(C32)
6		0.630169627	0.630169627	=1/((1+8%)^A33)	=FORMULATEXT(C33)
7		0.583490395	0.583490395	=1/((1+8%)^A34)	=FORMULATEXT(C34)
8		0.540268885	0.540268885	=1/((1+8%)^A35)	=FORMULATEXT(C35)
9		0.500248967	0.500248967	=1/((1+8%)^A36)	=FORMULATEXT(C36)
10		0.463193488	0.463193488	=1/((1+8%)^A37)	=FORMULATEXT(C37)
11		0.428882859	0.428882859	=1/((1+8%)^A38)	=FORMULATEXT(C38)
12		0.397113759	0.397113759	=1/((1+8%)^A39)	=FORMULATEXT(C39)
13		0.367697925	0.367697925	=1/((1+8%)^A40)	=FORMULATEXT(C40)
14		0.340461041	0.340461041	=1/((1+8%)^A41)	=FORMULATEXT(C41)
15		0.315241705	0.315241705	=1/((1+8%)^A42)	=FORMULATEXT(C42)
16		0.291890468	0.291890468	=1/((1+8%)^A43)	=FORMULATEXT(C43)
17		0.270268951	0.270268951	=1/((1+8%)^A44)	=FORMULATEXT(C44)
18		0.250249029	0.250249029	=1/((1+8%)^A45)	=FORMULATEXT(C45)
19		0.231712064	0.231712064	=1/((1+8%)^A46)	=FORMULATEXT(C46)
20		0.214548207	0.214548207	=1/((1+8%)^A47)	=FORMULATEXT(C47)
21		0.198655748	0.198655748	=1/((1+8%)^A48)	=FORMULATEXT(C48)
22		0.183940507	0.183940507	=1/((1+8%)^A49)	=FORMULATEXT(C49)
23		0.170315284	0.170315284	=1/((1+8%)^A50)	=FORMULATEXT(C50)
24		0.157699337	0.157699337	=1/((1+8%)^A51)	=FORMULATEXT(C51)
25		0.146017905	0.146017905	=1/((1+8%)^A52)	=FORMULATEXT(C52)
26		0.135201764	0.135201764	=1/((1+8%)^A53)	=FORMULATEXT(C53)
27		0.125186818	0.125186818	=1/((1+8%)^A54)	=FORMULATEXT(C54)
28		0.115913721	0.115913721	=1/((1+8%)^A55)	=FORMULATEXT(C55)
29		0.107327519	0.107327519	=1/((1+8%)^A56)	=FORMULATEXT(C56)
30		0.099377333	0.099377333	=1/((1+8%)^A57)	=FORMULATEXT(C57)
Sum	11.25778
Since both the sum should match, therefore amount to be invested per year	7800.2138
					=B24/B59

If lumpsum amount is to be invested at beginning
Value of lumpsum amount at beginning should be equal to present value of withdrawl
Lumpsum amount	87813.12	=B24

Sum of present value factors		11.25778334	=SUM(D28:D57)
Contribution employer		1500
Present value of employer contribution	16886.67501	=D70*D71
Present value trust fund		5363.705185	=25000/((1+8%)^20)
Present value of amount to be invested	65562.73679	=B24-D72-D73
Therefore annual investment required	5823.769635	=D74/D70