Question

In: Accounting

Sam has just celebrated her 25th birthday. On her way home from the Easter Holiday celebrations...

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.

Solutions

Expert Solution

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


Related Solutions

You just celebrated your 25th birthday today. You plan to invest $2000 annually, with the first...
You just celebrated your 25th birthday today. You plan to invest $2000 annually, with the first $2000 invested on your 26th birthday and the last invested on your 60th birthday. What is the value of this investment on your 61st birthday if all invested funds earn 6 percent annually?
Shelli graduates from the University of South next month on her 25th birthday, and she is...
Shelli graduates from the University of South next month on her 25th birthday, and she is excited to begin her new career. Because she wants to have a comfortable living when she retires, Shelli has decided to begin planning for her retirement now. As a result, she is currently evaluating the amount she needs to contribute to a retirement fund satisfy her financial requirements at retirement. After speaking to retired friends and relatives, Shelli estimates she will need $60,000 each...
Your friend is celebrating her 25th birthday today and wants to start saving for her anticipated...
Your friend is celebrating her 25th birthday today and wants to start saving for her anticipated retirement at age 65. She wants to be able to withdraw $200,000 from her saving account on each birthday for 15 years following her retirement; the first withdrawal will be on her 66th birthday. Your friend intends to invest her money in a retirement account, which earns 8 percent return per year. She wants to make an equal annual deposit on each birthday into...
Your friend is celebrating her 25th birthday today and wants to start saving for her anticipated...
Your friend is celebrating her 25th birthday today and wants to start saving for her anticipated early retirement at age 55. She wants to be able to withdraw $10,000 from her savings account on each birthday for 10 years following her retirement, the first withdrawal will be in her 56th birthday. She wants to make equal, annual payments on each birthday in a new savings account she will establish for her retirement fund. The account pays 8 per cent interest...
Based on the arrangement of Linda’s father, Linda is to get RM115,000 on her 25th birthday...
Based on the arrangement of Linda’s father, Linda is to get RM115,000 on her 25th birthday which is 5 years away. The funds have already been deposited in a bank and Linda wants to find out the present value of the fund. Linda has also found out that banks are, in general, offering 5% interest rate, Calculate the present value of the fund.
Mr. Smith is contemplating retirement. He has just celebrated his 50 birthday and his net worth...
Mr. Smith is contemplating retirement. He has just celebrated his 50 birthday and his net worth is $2.5 million. He hopes that after retirement he can maintain a lifestyle that costs him $110000 per year in today's dollars (i.e., real dollars, inflation adjusted). For simplicity, assume that all expenses occur at the end of each year; the first expense of $110000 will happen one year from now. If he retires, he will invest all his net worth in government bonds...
Allison and Leslie, who are twins, just received $10,000 each for the 25th birthday. The both...
Allison and Leslie, who are twins, just received $10,000 each for the 25th birthday. The both aspire to become millionaires. Each plan to make a $5,000 annual contribution to her “early retirement fund” on her birthday, beginning a year from today. Allison opened an account with the Safety-First Bond Fund, a mutual fund that invests in investment-grade (AAA rated) bonds whose investors have earned 6% per year in the past. Leslie invested in the New Issue Bio-Tech Fund, which invests...
Erika and Kitty, who are twins, just received $30,000 each for their 25th birthday. They both...
Erika and Kitty, who are twins, just received $30,000 each for their 25th birthday. They both have aspirations to become millionaires. Each plans to make a $5,000 annual contribution to her “early retirement fund” on her birthday, beginning a year from today. Erika opened an account with the Safety First Bond Fund, a mutual fund that invests in high-quality bonds whose investors have earned 6% per year in the past. Kitty invested in the New Issue Bio-Tech Fund, which invests...
Sam has just got engaged and wants to pay for her celebration in18 months from...
Sam has just got engaged and wants to pay for her celebration in 18 months from now. She has decided that she wants to start saving for the wedding today. She transferred $1,000 to her TFSA for the wedding and also established a directed debit of $300 to her TFSA at the end of each month. The TFSA pays interest monthly at 2.5%. a) How much money will she have saved for the wedding in 18 months from now? b)...
Your friend Ellen is celebrating her 25th birthday (i.e., she is 25 today) and wants to...
Your friend Ellen is celebrating her 25th birthday (i.e., she is 25 today) and wants to start saving for her anticipated retirement at age 55. She wants to be able to withdraw $10,000 from her savings account on each birthday for 10 years following her retirement (the first withdrawal will be on her 56th birthday). Ellen intends to invest her money in the local saving bank, which offers 8% (EAR) interest per year. Suppose Ellen wants to make 24 deposits...
ADVERTISEMENT
ADVERTISEMENT
ADVERTISEMENT