Question

Your brother has offered to give you $165?, starting next? year, and after that growing at 2.5% per year for the next 2020 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.8% 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

Formula sheet

A	B	C	D	E	F	G	H	I
2
3		a)
4
5		The amount of money deposited should be equal to the present value of all the withdrawl at the banks interest rate.
6
7		Amount Withdrawn Next Year	165
8		Growth rate	0.025
9		Period	20	years
10		Interest rate	0.058
11
12		The present value can be found using the formula of growing annuity.
13
14		Present value of growing annuity is given as follows:
15
16
17
18
19		Where P is first payment, i rate for the period, g is growth rate and n is the period.
20
21		Thus present value of growing annuity	=(D7/(D10-D8))*(1-(((1+D8)/(1+D10))^D9))	=(D7/(D10-D8))*(1-(((1+D8)/(1+D10))^D9))
22
23		Hence amount to be deposited is	=D21
24
25		b)
26
27		Year	Account Beginning Balance	Amount Withdrawn	Account Ending Balance
28		0	=D23	0	=D28
29		=C28+1	=F28*(1+$D$10)	=D7	=D29-E29
30		=C29+1	=F29*(1+$D$10)	=E29*(1+$D$8)	=D30-E30
31		=C30+1	=F30*(1+$D$10)	=E30*(1+$D$8)	=D31-E31
32		=C31+1	=F31*(1+$D$10)	=E31*(1+$D$8)	=D32-E32
33		=C32+1	=F32*(1+$D$10)	=E32*(1+$D$8)	=D33-E33
34		=C33+1	=F33*(1+$D$10)	=E33*(1+$D$8)	=D34-E34
35		=C34+1	=F34*(1+$D$10)	=E34*(1+$D$8)	=D35-E35
36		=C35+1	=F35*(1+$D$10)	=E35*(1+$D$8)	=D36-E36
37		=C36+1	=F36*(1+$D$10)	=E36*(1+$D$8)	=D37-E37
38		=C37+1	=F37*(1+$D$10)	=E37*(1+$D$8)	=D38-E38
39		=C38+1	=F38*(1+$D$10)	=E38*(1+$D$8)	=D39-E39
40		=C39+1	=F39*(1+$D$10)	=E39*(1+$D$8)	=D40-E40
41		=C40+1	=F40*(1+$D$10)	=E40*(1+$D$8)	=D41-E41
42		=C41+1	=F41*(1+$D$10)	=E41*(1+$D$8)	=D42-E42
43		=C42+1	=F42*(1+$D$10)	=E42*(1+$D$8)	=D43-E43
44		=C43+1	=F43*(1+$D$10)	=E43*(1+$D$8)	=D44-E44
45		=C44+1	=F44*(1+$D$10)	=E44*(1+$D$8)	=D45-E45
46		=C45+1	=F45*(1+$D$10)	=E45*(1+$D$8)	=D46-E46
47		=C46+1	=F46*(1+$D$10)	=E46*(1+$D$8)	=D47-E47
48		=C47+1	=F47*(1+$D$10)	=E47*(1+$D$8)	=D48-E48
49
50		Hence
51		Year	Remaing Balance
52		1	=F29
53		2	=F30
54		10	=F38
55		19	=F47
56