Microsoft Office Tutorials and References

In Depth Information

**The Amortization Table**

FIGURE 32.1
The Monthly Payment of the Loan

—

Using the Payment Function to Calculate

the Monthly Mortgage Payment

pv
is the present value of the loan; in this case, $600,000.

fv
is defined as

. . . the future value, or a cash balance due after the last pay-

ment is made.

“

This is what we call a balloon payment, it is a lump sum

payment made at the end of a long-term balloon loan. We do not use it here.

type
indicates when payments are due and is either the number 0 or 1. When type

is omitted, it is assumed to be 0, which means that the payments are made at

the end of the period. When the type is 1, it is assumed that the payments are

due at the beginning of the period, as is the case in some mortgage situations.

”

The Amortization Table

The amortization table will have a number of components. The table headers will be

Month, Payment Date, Principal, Interest, and Balance. You need to set up the table

for the various calculations as shown in Figure 32.2.

Since the table will be set up to calculate the complete duration of the loan, it will

need to show 300 months. You have to enter the values 1 to 300 on the left side of the

table (the first column). To automatically enter the values 1 to 300 without effort,

follow this procedure:

Type the number 1 in the first cell E2 and hit Enter.

n

Reselect the E2 cell and under the Home ribbon, use Fill Series.

n

In the Fill Series menu, select Columns, Linear, and then type 300 for the stop

value. See Figure 32.3.

n

This just created the desired outcome. The result is shown in Figure 32.4. The

series of numbers from 1 to 300 was filled in in column E.