Formulas and Functions

Chapter 13 Additional Examples and Topics 353
Example of a Loan Amortization Table
This example uses IPMT, PPMT, and PMT to construct a loan amortization table. The
information returned by IPMT, PPMT, and PMT is related. This is illustrated in the
example.
Constructing the Amortization Table
Assume you wish to construct an amortization table for all periods of a loan with an initial principal
amount of $50,000, a term of 2 years, an annual interest rate of 7%, and a balance due at the end
of the term of $30,000. The rst part of your amortization table (with formulas shown) could be
constructed like this:
Explanations of Cell Content
Cell B6 uses the PMT function to calculate the amount of each monthly payment. Note that this will
be the total of interest and principal for each month (for example, C9 + D9) as shown in F9.
Cells C9 and D9 use IPMT and PPMT, respectively, to calculate the portion of each monthly payment
that is interest and principal. Note that IPMT is the same as PMT – PPMT and, conversely, PPMT is the
same as PMT – IPMT.