Question

A series of deposits of P20,000 will start at the ninth month and is expected to increase by 5% for the following seven months. Three months later, another series started with an initial deposit of P15,000 and these deposits will increase by P4,000 each month until the sixth month of the series. Five months later, a third series will start with deposits of P15,000 for 7 months. How much is the total available balance if interest is 2% per month?

Answer 1

deposit starts on 9th month with 20000 and increase by 5% for 7 months.

in cash flow coloum type 20000 against month 9. Then increase by 5% for next 7 month.

month 10 cash flow =E6*(1+$B$5) Drag this formula till month 16 cash flow

= 21000

next cash flow is 3 months later i.e 20 th month. with cash flow of 15000. This will increase by 4000 each month for 6 months,

so next to 20th month type 15000 in cash flow column. for 21 month type

=E17+4000 Drag this formula till month 25

= 19000

Next series of cash flow starts five months later. i.e on month 31 with 15000 initial deposit. No information regarding further increase is given. This deposit is for 7 months, i.e till month 37. Now find FV of each cash flow with maturity as on 37 th month and interest rate of 2%.

FV of 9th month cash flow on 37th month

=FV($B$7,37-D6,0,-E6,0) DRAG THIS FORMULA TILL 37TH MONTH.

=34820.48

Once grag the formula till 37th month, we will get the FV of each cash flows as on 37 th month (as seen in the image)

To find total balance add all these FV together.

total balance on 37th month =SUM(F6:F36)

=524040.02

This is the available balance.