Excel 2010 Bible

Part II: Working with Formulas and Functions

338

TABLE 15.2

Formulas Used to Calculate an Amortization Schedule

Cell Formula Description

A9 =A8+1 Returns the payment number

B9 =PMT($B$2*($B$3/12),$B$4,-$B$1) Calculates the periodic payment amount

C9 =C8+B9 Calculates the cumulative payment

amounts

D9 =IPMT($B$2*($B$3/12),A9,$B$4,-$B$1) Calculates the interest portion of the periodic

payment

E9 =E8+D9 Calculates the cumulative interest paid

F9 =PPMT($B$2*($B$3/12),A9,$B$4,-$B$1) Calculates the principal portion of the

periodic payment

G9 =G8+F9 Calculates the cumulative amount applied

toward principal

H9 =H8-F9 Returns the principal balance at the end of

the period

Summarizing loan options by using a data table ......................................................

The Excel Data Table feature is probably one of the most underutilized tools in Excel. Keep in

mind that a data table is not the same as a table (created with Insert ➪ Tables ➪ Table). A data

table is a handy way to summarize calculations that depend on one or two “changing” cells. In this

example, I use a data table to summarize various loan options. This section describes how to create

one-way and two-way data tables.

Cross-Reference
See Chapter 36 for more information about setting up data tables. n

On the CD
A workbook that demonstrates one- and two-way data tables is available on the companion CD-ROM. The file
is named loan data tables.xlsx.

Creating a one-way data table ........................................................................

A one-way data table shows the results of any number of calculations for different values of a single

input cell.

Figure 15.6 shows a one-way data table (in B10:I13) that displays three calculations (payment

amount, total payments, and total interest) for a loan, using seven interest rates ranging from 7.00

percent to 8.50 percent. In this example, the input cell is cell B2.