Microsoft Office Tutorials and References

In Depth Information

See Chapter 19 for more information about conditional formatting.

Table 11-2: Formulas Used to Calculate an Amortization Schedule

Cell

Formula

Description

A9

=A8+1

Returns the payment number

=PMT($C$2*($C$3/12),$C$4,-

$C$1)

B9

Calculates the periodic payment amount

C9

=C8+B9

Calculates the cumulative payment amounts

=IPMT($C$2*($C$3/12),A9,$C$4,-

$C$1)

Calculates the interest portion of the periodic

payment

D9

E9

=E8+D9

Calculates the cumulative interest paid

=PPMT($C$2*($C$3/

12),A9,$C$4,-$C$1)

Calculates the principal portion of the periodic

payment

F9

Calculates the cumulative amount applied toward

principal

G9

=G8+F9

Returns the principal balance at the end of the

period

H9

=H8-F9

Calculating a loan with irregular payments

So far, the loan calculation examples in this chapter have involved loans with regular periodic payments. In

some cases, loan payback is irregular. For example, you may loan some money to a friend without a formal

agreement as to how he'll pay the money back. You still collect interest on the loan, so you need a way to per-

form the calculations based on the actual payment dates.

Figure 11-6 shows a worksheet set up to keep track of such a loan. The annual interest rate for the loan is stored

in cell B1 (named
APR
). The original loan amount and loan date are stored in row 5. Notice that the loan

amount is entered as a negative value in cell B5. Formulas, beginning in row 6, track the irregular loan pay-

ments and perform calculations.

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.