Question

  

Your brother has offered to give you $ 160​, starting next​ year, and after that growing at 2.5 % per year for the next 20 years. You would like to calculate the value of this offer by calculating how much money you would need to deposit in a local bank so that the amount will generate the same cash flows as he is offering you. Your local bank will guarantee a 5.5 % annual interest rate so long as you have money in the account.

a. How much money will you need to deposit into the account​ today?

b. Assuming you deposited the amount of money in part ​(a​), and then withdrew the required payments each​ year, calculate the remaining balance at the end of years​ 1, 2, 10 and 19.​ (Hint: To solve this problem it is best to use an excel​ spreadsheet.)  

Answer 1

A. To calculate the money to deposit in the account today

We need to find the present value of growing annuity = p/(r-g)*[1-(1+g)^n/(1+r)^n)

P= deposit amount-160

r - annual interest rate-5.5%

g - growth rate - 2.5%

160/(5.5%-2.5%)*[1-(1+2.5%)^20/(1+5.5%)^20)

=2338.13

b.

Rate	5.50%
Growth Rate	2.50%

Year	Amount Invested	Fund Value	SWP Amount	The amount at the end of the month
1	2338	2467	160	2307
2	2307	2433	164	2269
3	2339	2468	168	2300
4	2300	2426	172	2254
5	2340	2469	177	2292
6	2292	2418	181	2237
7	2341	2470	186	2284
8	2284	2410	190	2220
9	2342	2471	195	2276
10	2276	2401	200	2201
11	2343	2472	205	2267
12	2267	2392	210	2182
13	2344	2473	215	2258
14	2258	2382	221	2161
15	2345	2474	226	2248
16	2248	2372	232	2140
17	2346	2475	238	2238
18	2238	2361	243	2117
19	2347	2476	250	2227
20	2227	2349	256	2093

Fund Value = AMount at the beginning*(5.5%*amount at the beginning)

SWP Amount = Amount at the beginning of the year*(2.5%*amount at the beginning)