Question

insert a formula in cell I5 to calculate the balance due on the membership.

Answer 1

Steps are given below for using the formula :

1. Insert a function in cell C5 to display the membershio cost of first client.

2. Insert the function in B2 to display the current date.

3. use if function in cell E5 to calculate the total ammount or cost shown in column C.

4. in cell G5 calculate the total ammount due to the first client based on the amount totals and number of years in contact copy the formula of C,E,G down their respective columns.

5. Insert a function in cell H5, to display the ammount of down payment of first client based on membership.

6. Insert the formula in cell I5 to calculate the balance due on membership, copy the formula in H and I for rest of clients.

7. Insert PMT function in cell J5 to calculate first clients monthly payment copy the formula down the column.

8. calculate totals for annual totals, due date , down payments, balance dates and monthly payment on row 14.

9. In cells H18 and H22 format the payment for accounting number format.

10. Save the sheet.

PMT formula for calculating balance due:

=PMT(B$23/B$24, F5*B$24, -I5, 0, 0)