Question

A series of 10 end-of-year deposits is made that begins with $6,500 at the end of year 1 and decreases at the rate of $300 per year with 8% interest.

a. What amount could be withdrawn at t = 10?

b. What uniform annual series of deposits (n = 10) would result in the same accumulated balance at the end of year 10?

I would like to be able to use excel functions to solve this rather than by hand. Thanks!

Answer 1

A. The cashflow with 10th year end total balance is highlighted. Formula view is given after so for understanding of the formula used. Explanation follows.

Formula View-

Explanation-

At the end of year 1, a deposit of 6500 is made. At the end of year 2, that deposit will grow by 8%, that is- it will become 6500*1.08. Also, another deposit of 6500-300 is made at the end of year 2. So total balance at the end of year 2 becomes 6200+6500*1.08=13220. This balance of 13220 will again become 13220*1.08 at the end of year 3, and another deposit of 6200-300 will be made at the end of year 3, making rhe balance 20177.60. This way we can calculate for all years.

We can see that total balance at the end of year 10 is 77338.05

B. Since we need to solve this in excel, we will need to use solver function (available in Data tab. If not seen there, go to File->Options->Add-ins, and enable solver).

First, lets setup the problem. We need to get cell I11, which is the final year balance, to 77338.05. This we need to do by setting value of equal deposit per year, which is cell F2 so that we reach the required year end balance. The rest of the formula setup is same as part A. This all is shown below.

Now that we have this setup, lets go to solver.

This is the solver screen. We are telling it to equate I11 to 77338.05 by varying the value in F2, which is equal deposit each year and on which all year balances depend. Clicking on solve gives us screen that says solver found a solution. Clickin on OK and we get this result

Solver has found a solution. Bty making annual deposits of 5338.606048 we will get balance of 77338.05 at tne of year 10.