Question

Page 2 of 6 66th birthdav after your retirement in 34 years s). The remainder (i.e., after you withdraw the $70,000 each year) of the amount abltu are plannigyour retirement and you open a retirement account. You want to be able to have a yearly income of $70,000 from your (i.e. 35 payments will stay in the account earning some ac retirement account. Assumntcrest To achieve that you need to contribute to your ly. You are asked to explore the following scenarios earn 8% annual interest compounded retirement account. Assume that your retirement account will payment (contribution) into the account be if you: a) contribute from your 20th until the 65h birthday. Draw the 0h until the 30h and from your 35 Wat would the annual cash flow. until 65th birthday. Draw the b) contribute from your 2 cash flow. retirement account will hane zero (50.00 value after you turn 100 (Hint: Assume that your old) account

Answer 1

Ans:

Excel formula for value of retirement corpus at 65th year = =PV(8%,35,-70000,0) = 815,820

Tenure	35
Annual amount you want received from 66th until 100th year	70,000
Interest	8%
FV	0
Value of your retirement account at 65th Age	815,820

a) Now we want to know the annual contribution from 20th birthday to 65th year to achieve this much corpus:

Future value at 65th age	815,820
Present value	0
Interest rate	8%
Tenure	46
Annual contribution required	1,950

Excel formula: =PMT(8%,46,0,-815,820) = $1,950

Cash flow as per excel:

Year	Annual contribution at year end	Interest rate	Total corpus
20	1,950		1,950
21	1,950	8%	4,055
22	1,950	8%	6,330
23	1,950	8%	8,786
24	1,950	8%	11,438
25	1,950	8%	14,303
26	1,950	8%	17,397
27	1,950	8%	20,739
28	1,950	8%	24,347
29	1,950	8%	28,245
30	1,950	8%	32,454
31	1,950	8%	37,000
32	1,950	8%	41,910
33	1,950	8%	47,213
34	1,950	8%	52,939
35	1,950	8%	59,124
36	1,950	8%	65,804
37	1,950	8%	73,018
38	1,950	8%	80,809
39	1,950	8%	89,224
40	1,950	8%	98,311
41	1,950	8%	108,126
42	1,950	8%	118,726
43	1,950	8%	130,174
44	1,950	8%	142,537
45	1,950	8%	155,890
46	1,950	8%	170,311
47	1,950	8%	185,885
48	1,950	8%	202,706
49	1,950	8%	220,872
50	1,950	8%	240,492
51	1,950	8%	261,681
52	1,950	8%	284,565
53	1,950	8%	309,280
54	1,950	8%	335,972
55	1,950	8%	364,800
56	1,950	8%	395,933
57	1,950	8%	429,558
58	1,950	8%	465,872
59	1,950	8%	505,091
60	1,950	8%	547,449
61	1,950	8%	593,194
62	1,950	8%	642,599
63	1,950	8%	695,957
64	1,950	8%	753,583
65	1,950	8%	815,820

Excel formula:

Annual at year end Interest rate Total corpus ear 17 20 1949.73458856823 -C17 -C14 18 21 0.08 -E17 (1+D18)+C18 -E18 (1+D19)+C19 19 B18+1 -D18 20 B19+1 -D19 -E19"(1+D20)+C20 21 B20+1 -D20 E20 (1+D21)+C21 -E21 (1+D22)+C22 -E22 (1+D23)+C23 -E23 (1+D24)+C24 -E24 (1+D25)+C25 -E25 (1+D26)+C26 E26 (1+D27)+C27 -E27 (1+D28)+C28 E28 (1+D29)+C29 -C21 D21 22B21+1 D22 23 B22+1 24B23+1 -D23 -C24 25B24+1 -D24 26 -B25+1 -D25 -C26 27 B26+1 28 B27+1 -D26 -C27 -D27 29B28+1 -D28 30B29+1 -D29 -E29"(1+D30)+C30 31B30+1 -D30 E30 (1+D31)+C31 -E31 (1+D32)+C32 -E32 (1+D33)+C33 E33 (1+D34)+C34 -E34 (1+D35)+C35 E35 (1+D36)+C36 E36 (1+D37)+C37 -E37 (1+D38)+C38 -E38 (1+D39)+C39 -D31 -C31 32 B31+1 33 B32+1 -D32 -C33 -D33 34 B33+1 35 B34+1 -D34 36 -B35+1 -C35 -D35 37 B36+1 -D36 38 B37+1 -C37 -D37 39 B38+1 -D38 40B39+1 -C39 -D39 -E39*( 1+D40)+C40 1B40+1 -E40"( 1+D41)-C41 -C41 D41 2B41+1 -E41 (1+D42)+C42 -E42 (1+D43)+C43 -E43 (1+D44)+C44 -E44 (1+D45)+C45 -E45 (1+D46)+C46 43B42+1 44 B43+1 -C42 -D42 -C43 D43 5B44+1 46B45+1 -C45 -D45

E46 (1+D47)+C47 -E47 (1+D48)+C48 -E48 (1+D49)+C49 -E49 (1+D50)+C50 E50 (1+D51)+C51 7B46+1 -D46 8B47+1 49 B48+1 -C47 -D47 -D48 -C49 -D49 50B49+1 51B50+1 -D50 D51 -C51 52B51+1 -E51*(1+D52)-С52 -E5211+D53)-С53 53B52+1 -D52 -C53 -D53 54B53+1 -E53"( 1+D54)-С54 55 B54+1 -D54 E54 (1+D55)+C55 56 -B55+1 -D55 -E55"( 1+D56)-С56 57B56+1 -D56 -E56" (1+D57)-С57 58 B57+1 -C57 D57 E57 (1+D58)+C58 -E58" (1+D59)-С59 59B58+1 -D58 -C59 60 B59+1 -D59 -E59*( 1+D60)-С60 61 B60+1 -D60 -E60 (1+D61)+C61 -C61 -D61 62 B61+1 -E61*( 1+D62)-С62

b) Assuming contribution from 20th to 30th year and from 35th until 65th year:

Annual contribution to achieve the corpus of $815,820 would be $2,208.2 (calculated using trial and error method in the cash flow below.

Year	Annual contribution at year end	Interest rate	Total corpus
20	2,208		2,208
21	2,208	8%	4,593
22	2,208	8%	7,169
23	2,208	8%	9,950
24	2,208	8%	12,954
25	2,208	8%	16,199
26	2,208	8%	19,703
27	2,208	8%	23,487
28	2,208	8%	27,575
29	2,208	8%	31,989
30	2,208	8%	36,756
31	0	8%	39,697
32	0	8%	42,872
33	0	8%	46,302
34	0	8%	50,006
35	2,208	8%	56,215
36	2,208	8%	62,920
37	2,208	8%	70,162
38	2,208	8%	77,983
39	2,208	8%	86,430
40	2,208	8%	95,553
41	2,208	8%	105,405
42	2,208	8%	116,046
43	2,208	8%	127,537
44	2,208	8%	139,948
45	2,208	8%	153,353
46	2,208	8%	167,829
47	2,208	8%	183,463
48	2,208	8%	200,349
49	2,208	8%	218,585
50	2,208	8%	238,280
51	2,208	8%	259,550
52	2,208	8%	282,522
53	2,208	8%	307,332
54	2,208	8%	334,127
55	2,208	8%	363,065
56	2,208	8%	394,319
57	2,208	8%	428,072
58	2,208	8%	464,526
59	2,208	8%	503,897
60	2,208	8%	546,417
61	2,208	8%	592,338
62	2,208	8%	641,933
63	2,208	8%	695,496
64	2,208	8%	753,344
65	2,208	8%	815,820

Excel formula:

Annual contribution at year end Total corpus nterest rate ear -H17 2208.17 -117"( 1+1 18)-H18 -H17 -118 s] 18" (1+119)-H19 19G18+1 Н 18 20 G19+1 J19 (1+120)+H20 J20 (1+121)+H21 J21 (1+122)+H22 J22 (1+123)+H23 23 (1+124)+H24 24 (1+125)+H25 J25 (1+126)+H26 -H19 21G20+1 22G21+1 -H20 -120 -H21 #121 23 G22+1 -H22 24G23+1 H23 -123 25 G24+1 -H24 -124 26 G25+1 -H25 -125 27 G26+1 -H26 -126 J26 (1+127)+H27 J27 (1+128)+H28 28 (1+129)+H29 J29 (1+130)+H30 J30 (1+131)+H31 J31 (1+132)+H32 28 G27+1 -127 29 G28+1 -H28 -128 30 G29+1 -H29 -129 31G30+1 -H30 #131 32 G31+1 -H27 -H32 33 G32+1 J32 (1+133)+H33 J33 (1+134)+H34 -H33 -133 34G33+1 35 G34+1 -H34 J34 (1+135)+H35 J35 (1+136)+H36 36 -G35+1 -H35 -135 37 G36+1 -H36 J36 (1+137)+H37 38 G37+1 J37 (1+138)+H38 J38 (1+139)+H39 -H37 -137 -H38 39 G38+1 40G39+1 J39 (1+140)+H40 J40 (1+141)+H41 J41 (1+142)+H42 J42 (1+143)+H43 J43 (1+144)+H44 J44 (1+145)+H45 J45 (1+146)+H46 -H39 -139 41G40+1 -140 -H41 42G41+1 43G42+1 -H42 -H43 44G43+1 -143 45 G44+1 -144 -H45 46G45+1 -145

47G46+1 -H46 J46 (1+147)+H47 J47 (1+148)+H48 J48 (1+149)+H49 -146 48 -G47+1 -H47 -147 49 :G4841 H48 -148 -H49 #149 50 =G49+1 J49 1+150)+H50 J50 (1+151)+H51 J51 (1+152)+H52 J52 (1+153)+H53 51 -G50+1 -H50 -150 -H51 #151 52 G51+1 53G52+1 -H52 -152 54G53+1 -153 J53 (1+154)+H54 -H53 55 G54+1 -H54 -154 J54 (1+155)+H55 56 G55+1 -H55 155 J55 (1+156)+H56 -H56 -156 J56 (1+157)+H57 58 G57+1 59 G58+1 J57 (1+158)+H58 J58 (1+159)+H59 -H57 -157 -H58 -158 60 G59+1 -H59 J59 (1+160)+H60 J60 (1+161)+H61 J61 (1+162)+H62 = 159 61G60+1 -H60 -160 -H61 #161 62 G61+1