Microsoft Office Tutorials and References
In Depth Information
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.
Search JabSto ::

Custom Search