In: Accounting
Sam has just celebrated her 25th birthday. On her way home from the Easter Holiday celebrations in Portugal, she was nearly involved in an accident. It was at this point that she realised that she needs to save up for her retirement at the age of 55. She currently has no savings available. When Sam retires, she wants to buy a cattle farm in Portugal that will be valued at R3 500 000 in 30 years. She also wants to receive R42 000 per month for the rest of her life. She wants to make monthly payments (starting a month after her birthday) for the next 30 years into an investment account that earns 14,15% p.a., compounded annually.
REQUIRED:
Calculate the monthly payments that Sam must invest for the next 30 years by completing the tables below. Assume the interest rate is the same for both retirement goals.
PV of annuity of $42,000 p.m required from retirement till perpetuity= Cash outflow/ (rate of return- growth rate) = 42,000/ (14.14/12)= $ 3,559,322.
To support her monthly requirement, SAM needs $ 3,559,322 at age of retirement for supporting her monthly needs and $3,500,000 for cattle farm purchase. Total money needed by SAM is 3,559,322+3,500,000= 7,059,322
Below is complete table for cash flow of monthly investment using goal seek-
Month Opening
Investment
Interest Closing
1
-
1,241.57
1,241.57
2
1,241.57
1,241.57
14.64
2,497.79
3
2,497.79
1,241.57
29.45
3,768.82
4
3,768.82
1,241.57
44.44
5,054.83
5
5,054.83
1,241.57
59.60
6,356.01
6
6,356.01
1,241.57
74.95
7,672.53
7
7,672.53
1,241.57
90.47
9,004.58
8
9,004.58
1,241.57
106.18
10,352.33
9
10,352.33
1,241.57
122.07
11,715.97
10
11,715.97
1,241.57
138.15
13,095.70
11
13,095.70
1,241.57
154.42
14,491.69
12
14,491.69
1,241.57
170.88
15,904.15
13
15,904.15
1,241.57
187.54
17,333.26
14
17,333.26
1,241.57
204.39
18,779.22
15
18,779.22
1,241.57
221.44
20,242.23
16
20,242.23
1,241.57
238.69
21,722.50
17
21,722.50
1,241.57
256.14
23,220.22
18
23,220.22
1,241.57
273.81
24,735.59
19
24,735.59
1,241.57
291.67
26,268.84
20
26,268.84
1,241.57
309.75
27,820.17
21
27,820.17
1,241.57
328.05
29,389.79
22
29,389.79
1,241.57
346.55
30,977.92
23
30,977.92
1,241.57
365.28
32,584.77
24
32,584.77
1,241.57
384.23
34,210.58
25
34,210.58
1,241.57
403.40
35,855.55
26
35,855.55
1,241.57
422.80
37,519.92
27
37,519.92
1,241.57
442.42
39,203.92
28
39,203.92
1,241.57
462.28
40,907.77
29
40,907.77
1,241.57
482.37
42,631.72
30
42,631.72
1,241.57
502.70
44,375.99
31
44,375.99
1,241.57
523.27
46,140.83
32
46,140.83
1,241.57
544.08
47,926.48
33
47,926.48
1,241.57
565.13
49,733.19
34
49,733.19
1,241.57
586.44
51,561.20
35
51,561.20
1,241.57
607.99
53,410.77
36
53,410.77
1,241.57
629.80
55,282.14
37
55,282.14
1,241.57
651.87
57,175.58
38
57,175.58
1,241.57
674.20
59,091.35
39
59,091.35
1,241.57
696.79
61,029.71
40
61,029.71
1,241.57
719.64
62,990.93
41
62,990.93
1,241.57
742.77
64,975.27
42
64,975.27
1,241.57
766.17
66,983.01
43
66,983.01
1,241.57
789.84
69,014.43
44
69,014.43
1,241.57
813.80
71,069.80
45
71,069.80
1,241.57
838.03
73,149.40
46
73,149.40
1,241.57
862.55
75,253.53
47
75,253.53
1,241.57
887.36
77,382.47
48
77,382.47
1,241.57
912.47
79,536.51
49
79,536.51
1,241.57
937.87
81,715.95
50
81,715.95
1,241.57
963.57
83,921.09
51
83,921.09
1,241.57
989.57
86,152.24
52
86,152.24
1,241.57
1,015.88
88,409.69
53
88,409.69
1,241.57
1,042.50
90,693.76
54
90,693.76
1,241.57
1,069.43
93,004.77
55
93,004.77
1,241.57
1,096.68
95,343.02
56
95,343.02
1,241.57
1,124.25
97,708.85
57
97,708.85
1,241.57
1,152.15
100,102.57
58
100,102.57
1,241.57
1,180.38
102,524.52
59
102,524.52
1,241.57
1,208.93
104,975.03
60
104,975.03
1,241.57
1,237.83
107,454.44
61
107,454.44
1,241.57
1,267.07
109,963.08
62
109,963.08
1,241.57
1,296.65
112,501.30
63
112,501.30
1,241.57
1,326.58
115,069.45
64
115,069.45
1,241.57
1,356.86
117,667.89
65
117,667.89
1,241.57
1,387.50
120,296.96
66
120,296.96
1,241.57
1,418.50
122,957.04
67
122,957.04
1,241.57
1,449.87
125,648.48
68
125,648.48
1,241.57
1,481.60
128,371.66
69
128,371.66
1,241.57
1,513.72
131,126.95
70
131,126.95
1,241.57
1,546.21
133,914.73
71
133,914.73
1,241.57
1,579.08
136,735.38
72
136,735.38
1,241.57
1,612.34
139,589.29
73
139,589.29
1,241.57
1,645.99
142,476.85
74
142,476.85
1,241.57
1,680.04
145,398.47
75
145,398.47
1,241.57
1,714.49
148,354.53
76
148,354.53
1,241.57
1,749.35
151,345.45
77
151,345.45
1,241.57
1,784.62
154,371.64
78
154,371.64
1,241.57
1,820.30
157,433.52
79
157,433.52
1,241.57
1,856.40
160,531.49
80
160,531.49
1,241.57
1,892.93
163,666.00
81
163,666.00
1,241.57
1,929.89
166,837.47
82
166,837.47
1,241.57
1,967.29
170,046.34
83
170,046.34
1,241.57
2,005.13
173,293.04
84
173,293.04
1,241.57
2,043.41
176,578.03
85
176,578.03
1,241.57
2,082.15
179,901.75
86
179,901.75
1,241.57
2,121.34
183,264.67
87
183,264.67
1,241.57
2,161.00
186,667.24
88
186,667.24
1,241.57
2,201.12
190,109.93
89
190,109.93
1,241.57
2,241.71
193,593.21
90
193,593.21
1,241.57
2,282.79
197,117.58
91
197,117.58
1,241.57
2,324.34
200,683.49
92
200,683.49
1,241.57
2,366.39
204,291.46
93
204,291.46
1,241.57
2,408.94
207,941.97
94
207,941.97
1,241.57
2,451.98
211,635.53
95
211,635.53
1,241.57
2,495.54
215,372.64
96
215,372.64
1,241.57
2,539.60
219,153.81
97
219,153.81
1,241.57
2,584.19
222,979.58
98
222,979.58
1,241.57
2,629.30
226,850.45
99
226,850.45
1,241.57
2,674.94
230,766.97
100
230,766.97
1,241.57
2,721.13
234,729.67
101
234,729.67
1,241.57
2,767.85
238,739.10
102
238,739.10
1,241.57
2,815.13
242,795.81
103
242,795.81
1,241.57
2,862.97
246,900.35
104
246,900.35
1,241.57
2,911.37
251,053.29
105
251,053.29
1,241.57
2,960.34
255,255.20
106
255,255.20
1,241.57
3,009.88
259,506.66
107
259,506.66
1,241.57
3,060.02
263,808.25
108
263,808.25
1,241.57
3,110.74
268,160.56
109
268,160.56
1,241.57
3,162.06
272,564.19
110
272,564.19
1,241.57
3,213.99
277,019.75
111
277,019.75
1,241.57
3,266.52
281,527.85
112
281,527.85
1,241.57
3,319.68
286,089.11
113
286,089.11
1,241.57
3,373.47
290,704.15
114
290,704.15
1,241.57
3,427.89
295,373.61
115
295,373.61
1,241.57
3,482.95
300,098.13
116
300,098.13
1,241.57
3,538.66
304,878.36
117
304,878.36
1,241.57
3,595.02
309,714.96
118
309,714.96
1,241.57
3,652.06
314,608.59
119
314,608.59
1,241.57
3,709.76
319,559.92
120
319,559.92
1,241.57
3,768.14
324,569.64
121
324,569.64
1,241.57
3,827.22
329,638.43
122
329,638.43
1,241.57
3,886.99
334,766.99
123
334,766.99
1,241.57
3,947.46
339,956.03
124
339,956.03
1,241.57
4,008.65
345,206.25
125
345,206.25
1,241.57
4,070.56
350,518.38
126
350,518.38
1,241.57
4,133.20
355,893.15
127
355,893.15
1,241.57
4,196.57
361,331.30
128
361,331.30
1,241.57
4,260.70
366,833.57
129
366,833.57
1,241.57
4,325.58
372,400.72
130
372,400.72
1,241.57
4,391.23
378,033.52
131
378,033.52
1,241.57
4,457.65
383,732.74
132
383,732.74
1,241.57
4,524.85
389,499.17
133
389,499.17
1,241.57
4,592.84
395,333.58
134
395,333.58
1,241.57
4,661.64
401,236.80
135
401,236.80
1,241.57
4,731.25
407,209.62
136
407,209.62
1,241.57
4,801.68
413,252.88
137
413,252.88
1,241.57
4,872.94
419,367.39
138
419,367.39
1,241.57
4,945.04
425,554.01
139
425,554.01
1,241.57
5,017.99
431,813.57
140
431,813.57
1,241.57
5,091.80
438,146.95
141
438,146.95
1,241.57
5,166.48
444,555.00
142
444,555.00
1,241.57
5,242.04
451,038.62
143
451,038.62
1,241.57
5,318.50
457,598.69
144
457,598.69
1,241.57
5,395.85
464,236.12
145
464,236.12
1,241.57
5,474.12
470,951.81
146
470,951.81
1,241.57
5,553.31
477,746.69
147
477,746.69
1,241.57
5,633.43
484,621.70
148
484,621.70
1,241.57
5,714.50
491,577.77
149
491,577.77
1,241.57
5,796.52
498,615.86
150
498,615.86
1,241.57
5,879.51
505,736.95
151
505,736.95
1,241.57
5,963.48
512,942.00
152
512,942.00
1,241.57
6,048.44
520,232.02
153
520,232.02
1,241.57
6,134.40
527,608.00
154
527,608.00
1,241.57
6,221.38
535,070.95
155
535,070.95
1,241.57
6,309.38
542,621.90
156
542,621.90
1,241.57
6,398.42
550,261.89
157
550,261.89
1,241.57
6,488.50
557,991.97
158
557,991.97
1,241.57
6,579.66
565,813.20
159
565,813.20
1,241.57
6,671.88
573,726.65
160
573,726.65
1,241.57
6,765.19
581,733.42
161
581,733.42
1,241.57
6,859.61
589,834.60
162
589,834.60
1,241.57
6,955.13
598,031.31
163
598,031.31
1,241.57
7,051.79
606,324.67
164
606,324.67
1,241.57
7,149.58
614,715.82
165
614,715.82
1,241.57
7,248.52
623,205.92
166
623,205.92
1,241.57
7,348.64
631,796.13
167
631,796.13
1,241.57
7,449.93
640,487.63
168
640,487.63
1,241.57
7,552.42
649,281.62
169
649,281.62
1,241.57
7,656.11
658,179.31
170
658,179.31
1,241.57
7,761.03
667,181.91
171
667,181.91
1,241.57
7,867.19
676,290.67
172
676,290.67
1,241.57
7,974.59
685,506.84
173
685,506.84
1,241.57
8,083.27
694,831.68
174
694,831.68
1,241.57
8,193.22
704,266.48
175
704,266.48
1,241.57
8,304.48
713,812.53
176
713,812.53
1,241.57
8,417.04
723,471.14
177
723,471.14
1,241.57
8,530.93
733,243.65
178
733,243.65
1,241.57
8,646.16
743,131.39
179
743,131.39
1,241.57
8,762.76
753,135.72
180
753,135.72
1,241.57
8,880.73
763,258.02
181
763,258.02
1,241.57
9,000.08
773,499.68
182
773,499.68
1,241.57
9,120.85
783,862.10
183
783,862.10
1,241.57
9,243.04
794,346.72
184
794,346.72
1,241.57
9,366.67
804,954.96
185
804,954.96
1,241.57
9,491.76
815,688.30
186
815,688.30
1,241.57
9,618.32
826,548.20
187
826,548.20
1,241.57
9,746.38
837,536.15
188
837,536.15
1,241.57
9,875.95
848,653.67
189
848,653.67
1,241.57
10,007.04
859,902.29
190
859,902.29
1,241.57
10,139.68
871,283.54
191
871,283.54
1,241.57
10,273.89
882,799.00
192
882,799.00
1,241.57
10,409.67
894,450.25
193
894,450.25
1,241.57
10,547.06
906,238.88
194
906,238.88
1,241.57
10,686.07
918,166.52
195
918,166.52
1,241.57
10,826.71
930,234.81
196
930,234.81
1,241.57
10,969.02
942,445.40
197
942,445.40
1,241.57
11,113.00
954,799.98
198
954,799.98
1,241.57
11,258.68
967,300.23
199
967,300.23
1,241.57
11,406.08
979,947.89
200
979,947.89
1,241.57
11,555.22
992,744.68
201
992,744.68
1,241.57
11,706.11
1,005,692.37
202 1,005,692.37
1,241.57
11,858.79
1,018,792.73
203 1,018,792.73
1,241.57
12,013.26
1,032,047.57
204 1,032,047.57
1,241.57
12,169.56
1,045,458.71
205 1,045,458.71
1,241.57
12,327.70
1,059,027.98
206 1,059,027.98
1,241.57
12,487.70
1,072,757.26
207 1,072,757.26
1,241.57
12,649.60
1,086,648.43
208 1,086,648.43
1,241.57
12,813.40
1,100,703.40
209 1,100,703.40
1,241.57
12,979.13
1,114,924.10
210 1,114,924.10
1,241.57
13,146.81
1,129,312.49
211 1,129,312.49
1,241.57
13,316.48
1,143,870.54
212 1,143,870.54
1,241.57
13,488.14
1,158,600.25
213 1,158,600.25
1,241.57
13,661.83
1,173,503.66
214 1,173,503.66
1,241.57
13,837.56
1,188,582.79
215 1,188,582.79
1,241.57
14,015.37
1,203,839.74
216 1,203,839.74
&nbs