Part II: Working with Formulas and Functions
342
FIGURE 15.9This worksheet tracks loan payments that are made on an irregular basis.
Column B stores the payment amount made on the date in column C. Notice that the payments
are not made on a regular basis. Also, notice that in two cases (row 11 and row 24), the payment
amount is negative. These entries represent additional borrowed money added to the loan balance.
Formulas in columns D and E calculate the amount of the payment credited toward interest and
principal. Columns F and G keep a running tally of the cumulative payments and interest
amounts. Formulas in column H compute the new loan balance after each payment. Table 15.3
lists and describes the formulas in row 6. Note that each formula uses an IF function to determine
whether the payment date in column C is missing. If so, the formula returns an empty string, so no
data appears in the cell.TABLE 15.3Formulas to Calculate a Loan with Irregular Payments
Cell Formula DescriptionD6 =IF(C6<>””,(C6-
C5)/365*H5*APR,””)The formula calculates the interest, based on the payment date.E6 =IF(C6<>””,B6-D6,””) The formula subtracts the interest amount from the payment to
calculate the amount credited to principal.