Microsoft Office Tutorials and References

In Depth Information

Figure 11-6:
This worksheet tracks loan payments that are made on an irregular basis.

Table 11-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 11-3: Formulas to Calculate a Loan with Irregular Payments

Cell

Formula

Description

=IF(C6<>””,(C6-C5)/

365*H5*APR,””)

D6

Calculates the interest, based on the payment date

Subtracts the interest amount from the payment to calculate

the amount credited to principal

E6

=IF(C6<>””,B6-D6,””)