Question

Your brother has offered to give you $ 140​, 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 6.1 % 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.

Calculation of money needed to deposit into the account today

As the Bank guarantee to pay a 6.1% annual rate, Therefore, we discount annual cash inflows what brother offered to compute Present value of cash inflows over 20 years.

The Present value of cash inflows over 20 years at discount rate 6.1% is the money required to deposit into account today to get same payout over 20 years.

Please refer below excel spreadsheet for calculations -

Formula reference -

Thus, $ 1,939.07 need to be deposit today.

b.

Calculation of remaining balance at the end years 1,2,10 and 19.

Please refer below spreadsheet for calculation of account balance at end of years.

Formula reference-

In above spreadsheets, we can find remaining balances at the end of -

End of year	Balance of Account
1	$ 1,917.35
2	$ 1,890.81
10	$ 1,453.19
19	$ 210.94